Copy dissimilar data from one database to another? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Copy dissimilar data from one database to another?

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-03-30 : 11:59:58

I have two databases with the same table structure. I need to copy the data from one table to another table. One caveat is that I only want to copy the dissimilar data from the "master" table to the "clone" table.

MASTER DATABASE:
db_master

MASTER TABLE:
tb_slideshows

CLONE DATABASE:
db_clone

CLONE TABLE:
tb_slideshows

There are many fields in the "tb_slideshows" MASTER table that I want to copy to the "tb_slideshows" CLONE table. I only want to copy items that exist in db_master but not in db_clone

For example:


master tb_slideshows                             clone tb_slideshows
======================================================================
ID       Name                                    ID      Name
----------------------------------------------------------------------
1        Alpha                                   2       Bravo
2        Bravo                                   3       Charlie
3        Charlie                                 6       Foxtrot
4        Delta
5        Echo
6        Foxtrot

In the case above, only IDs of 1, 4, and 5 would copy from MASTER to CLONE.

Any advice is appreciated,
Thanks,
Matt

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 12:26:03

[code]INSERT INTO db_clone.dbo.tb_slideshows (ID, [Name])
SELECT m.ID, m.[Name]
FROM db_master.dbo.tb_slideshows m
LEFT JOIN db_clone.dbo.tb_slideshows c
ON m.ID = c.ID
WHERE c.ID IS NULL[/code]

If ID is an IDENTITY column, you'll need to execute this first:
[code]SET IDENTITY_INSERT db_clone.dbo.tb_slideshows ON
GO[/code]
Then execute this when you're done:
[code]SET IDENTITY_INSERT db_clone.dbo.tb_slideshows OFF
GO[/code]

There are 10 types of people in the world, those that understand binary, and those that don't.

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-03-30 : 12:56:24

Thanks, I'll try this after lunch.

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-03-30 : 16:32:10

That works great, thank you very much.

I will save it here in case the server ever gets hit by a beer truck and I lose my script:


INSERT INTO [domainname.com].dbo.tb_slideshowimages ([f_slideshowGUID], [f_slideshowmanagerID], [f_slideshowpropertyassignment], [f_slideshowassignmentID], [f_slideshowfilename], [f_slideshoworder])
SELECT m.[f_slideshowGUID], m.[f_slideshowmanagerID], m.[f_slideshowpropertyassignment], m.[f_slideshowassignmentID], m.[f_slideshowfilename], m.[f_slideshoworder]
FROM [restore.domainname.com].dbo.tb_slideshowimages m
LEFT JOIN [domainname.com].dbo.tb_slideshowimages c
	ON m.[f_slideshowGUID] = c.[f_slideshowGUID]
WHERE c.[f_slideshowGUID] IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources