wbh
May 20, 2016, 1:59pm
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
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
wbh:
that's what I'm doing
Not seeing it in the code you have posted, pls post the complete code.
wbh
May 20, 2016, 4:23pm
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
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
wbh
May 20, 2016, 4:37pm
7
it is the id that I want to have in col2
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
...
wbh
May 20, 2016, 5:00pm
9
I think I could not ask my question correctly
thanks
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