Moving a column of data to different 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)

Moving a column of data to different table.

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2008-05-01 : 19:00:50

Hi,

Here is my scenario simplified.

tbl1
userid, secondaryid

tbl2
userID, secondaryID

tbl1 is already populated. I just want to transfer the secondary ID to tbl2 based on the userID. I don't want to store secondaryID in tbl1 anymore.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 19:09:33

Does tbl2 already have the column? Are there rows in it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

robc
Yak Posting Veteran

60 Posts

Posted - 2008-05-01 : 19:38:31

Sorry. Yes tbl2 has the same column and also has rows. (Some users in tbl1 will not be in tbl2 though, if that matters.)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 19:54:05

UPDATE t2
SET secondaryID = t1.secondaryid
FROM tbl2 t2
INNER JOIN tbl1 t1
ON t2.userID = t1.userid

If you want the missing rows:
INSERT INTO tbl2 (userID, secondaryID)
SELECT userid, secondaryid
FROM tbl1 t1
WHERE NOT EXISTS (SELECT * FROM tbl2 t2 WHERE t1.userid = t2.userID)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

robc
Yak Posting Veteran

60 Posts

Posted - 2008-05-01 : 20:05:47

Thank you very much Tara.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources