Temp Tables - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Temp Tables

Author

Topic

iloveorangesoda
Starting Member

30 Posts

Posted - 2005-11-17 : 04:33:20

I have added data from one of my database tables to a temp table and made changes to the data. Now I want to write these changes back to the database table but not sure how to?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-17 : 04:50:28

post your table structure so that we may help

[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 04:54:53

I think you want to update the data of temp table to the actaul table
If so, try this

Update A
set A.col1=T.col1,A.col2=T.col2,..A.colN=T.colN
from ActualTable A inner join #tempTable T on A.keycol=T.keycol

Madhivanan

Failing to plan is Planning to fail

iloveorangesoda
Starting Member

30 Posts

Posted - 2005-11-17 : 04:54:54

CREATE TABLE #TempMS
(
ipd int,
MSMToD varchar(10)
)

INSERT INTO #TempMS
(
ipd ,
MSMToD
)
VALUES
(
@ipd ,
@SM
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-17 : 05:02:37

if you need to update back to your table use madhivanan's sample code

quote:
Update A
set A.col1=T.col1,A.col2=T.col2,..A.colN=T.colN
from ActualTable A inner join #tempTable T on A.keycol=T.keycol

if you need to insert to your table MS, use this

insert into MS(ipd, MSMToD)
select ipd, MSMTod
from #TempMS

you might also want to check for existance of record in your table MS, add a where clause

where not exists (select * from MS x where x.ipd = #TempMS.ipd

assuming ipd is your primary key

then for those record already existed in table MS, use the above update sample from madhivanan

[KH]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources