insert into new table but update old table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

insert into new table but update old table

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-24 : 12:08:21

i have a statement

insert into table1(x,y,z) select x,y,z from table2 where moved=0

now this works but I want to at the same time while it inserts the data into table1 it should update table2 and set field called moved=1

what's the best way to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 12:15:27

[code]
declare @inserted_items table
(x <datatype>,
y datatype,
z...
)

insert into table1(x,y,z)
output inserted.x,inserted.y,inserted.z into @inserted_items
select x,y,z from table2 where moved=0

update t2
set moved =1
from table2 t2
join @inserted_items i
on i.x=t2.x
and i.y=t2.y
and i.z = t2.z
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 12:17:39

if any of x,y,z is pk of table2 then you need to include only that in @inserted_items as well as in last join

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-24 : 14:20:19

thanks - this is perfect -- now I have an id but if the id might not match in both tables (as auto increment) then I have to go by matching up all the other fields?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 14:24:28

yup...you need to match all other fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources