Appending a table's records without duplicates

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

SQL Server Administration (2000)

Appending a table's records without duplicates

Author

Topic

kowani1
Starting Member

42 Posts

Posted - 2007-03-20 : 01:09:57

Hi DB Gurus,

I have a scenario where I have 'TableA' on 'ProdServer' which has some missing/deleted records.
After restoring a earlier copy of 'TableA' from tape to 'TestServer', I need to consolidate(merge) the records on 'TableA' in Test to the copy of 'TableA' currently in Production.

Is there a way I can merge these two copies of 'TableA' to get the complete recordsets for table A without:
a) Getting Primary Key Violation error;
b) Affecting any dependency objects of 'TableA' on 'ProdServer'.
c) Importantly, have NO Duplicate records.

Please assist me here, as I need to get this done ASAP for some mission critical reporting.

Thanks.
Jungle DBA

MohammedU
Posting Yak Master

145 Posts

Posted - 2007-03-20 : 01:42:57

Copy TableA from TestServer to ProdServer as different name TableAOld
Then try the following...
Insert into TableA
select * from TableA a
where not exists ( select 1 from TableAOld o where o.pk = a.pk)

MohammedU

kowani1
Starting Member

42 Posts

Posted - 2007-03-21 : 23:44:44

I tried doing the above and got the following error. Please help..

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_CardHolderMealsArchive'. Cannot insert duplicate key in object 'CardHolderMealsArchive'.
The statement has been terminated.

MohammedU
Posting Yak Master

145 Posts

Posted - 2007-03-22 : 01:48:43

Post the script...

MohammedU

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources