joining the tables

Question

Post reply

joining the tables

blnbmv

SSC Eights!

Points: 900
More actions
August 9, 2008 at 11:51 pm

#190922

hi members,
The following are the tables I use:
declare @totable table (gid int,parent int,id int,protype int)
insert into @totable values(4,29246,278397,2)
insert into @totable values(4,277124,29246,2)
insert into @totable values(4,278937,2788399,1)
Create table fromtable (Tgid int,Tparent int,Tid int,Tprotype int)
insert into fromtable values(4,278397,278399,1)
insert into fromtable values(4,29246,278397,1)
insert into fromtable values(4,277124,29246,1)
insert into fromtable values(4,35930,277124,1)
insert into fromtable values(4,4,35930,1)
insert into fromtable values(4,4,4,1)
I have a Temp table(fromtable) with fresh data coming from different table and Target table(Totable) which has some records existing and I need to match the records based on gid,parent,id with Tgid,Tparent,Tid of Target table and Target table should get inserted with those where there are no matching records and less than protection of the target table records.(ie)
I dont want to disturb the Target table records which have Tprotype level 2 and i just want my target table to look like this
4,278397,2788399,1
4,29246,278397,2
4,277124,29246,2
4,35930,277124,1
4,4,35930,1
4,4,4,1
I want select statement which insert into target table by select the temptable records and matching with target table and check the logic and then insert.
Thanks in advance,
Sainath

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

You can use a recursive CTE.

Books Online has examples of this.

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 2

Not sure a recursive CTE is needed for this simple update problem... it appears that the prototype ID is simply being updated for each occurance in the temp table...

Sainath,

Thank you so much for taking the time to present your sample data correctly for use. Makes life a lot easier on those who want to help.

Here's the solution...

[font="Courier New"]

UPDATE dbo.FromTable

SET TProType = t.ProType

FROM dbo.FromTable f

INNER JOIN @ToTable t

ON t.GID = f.TGID

AND t.Parent = f.TParent

AND t.ID = f.TID[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

blnbmv SSC Eights! Points: 900 More actions · Answer 3

Hi jeff,

Thanks for your interest. I dont want to update my fromtable. I just want to insert into

Initially the target table contains the below records that is three records

4,29246,278397,2

4,277124,29246,2

4,278937,2788399,1

and I want to insert additional records from fromtable which are protype(protype is column name) < 2 and the records which are not there in my totable. (i.e) the below records which are not there in totable there is :

4,35930,277124,1

4,4,35930,1

4,4,4,1

At the end my my total totable should like this:

4,278397,2788399,1