SQLTeam.com | Weblogs | Forums

A query that seems simple

#1

Hello,
I have a table that looks like this:
table1:

col1 col2
100001 100001
210000 210000
210001 210000
211000 210000
212000 212000
220000 212000
230000 212000
240000 240000
241000 240000
241100 240000
241200 240000

I need to insert column 1 in another tabe
then retrieve the ID on the column 2 and insert in column 2 of the second table, either:
my table 2 must contains :

id col1 col2
1 100001 1
2 210000 2
3 210001 2
4 211000 2
5 212000 5
6 220000 5
7 230000 5
8 240000 8
9 241000 8
10 241100 8
11 241200 8

I tried this, but it only updates columns 1 and 2 identical my table1

after insertion of the col1 (in my table2)

update table2 set
table2.col2 = table2.id
from table1
where table1.col2=table2.col1

#2

Use the OUTPUT clause to store the Key columns (or all columns, if you prefer) along with the IDENTITY that has been assigned by SQL, and then use that in the second statement block

#3

that's what I'm doing

#4

Not seeing it in the code you have posted, pls post the complete code.

#5

insert into table2
select col1 from table 1

select * from table2

id col1 col2
1 100001
2 210000
3 210001
4 211000
5 212000
6 220000
7 230000
8 240000
9 241000
10 241100
11 241200

now I have to update col2 by the id created

#6 
create table #tmp(col1 varchar(50),col2 varchar(50))
create table #tmp2(pk int not null identity primary key,col1 varchar(50),col2 varchar(50))

insert into #tmp(col1,col2)
values( 100001,	100001),(210000,210000)
,(230000,	212000)
,(240000,	240000)
,(241000,	240000)
,(241100,	240000)
,(241200,	240000)

DECLARE @output TABLE (id int,col1 varchar(50),col2 varchar(50))

Insert into #tmp2(col1,col2)
OUTPUT inserted.pk,inserted.col1,inserted.col2 INTO @output
select * from
#tmp

select * from @output a
#7

it is the id that I want to have in col2

#8

honestly, you should have known how to get it from that point. output holds your values, you can query output how you want...

ie, this is just a sample to show you how it works, you should go through and try to understand it.

select col1,id
from @output

...

#9

I think I could not ask my question correctly

thanks

#10

Your query does not have the OUTPUT clause, that's what you need (to add) in your first statement - that will "store" the necessary data which you can then combine in your second statement