Copying Data Between 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 2005 Forums

Express Edition and Compact Edition (2005)

Copying Data Between Tables

Author

Topic

cbjroms
Starting Member

13 Posts

Posted - 2009-11-12 : 14:03:41

I have 2 databases; one is a test versions and the other live.

I simply want to update certain tables in the test database by copying contents of same table in live.

I have searched the internet but can only find Select Into statement. This fails because the table alreay exists in both databases.

Can anyone point me in right direction to copy data from DatabaseA.dbo.TableA into (and overwrite) DatabaseB.dbo.TableA?

Alternatively, if I delete the table from test database
and use Select Into to create a copy, will all dependencies, keys schemas etc be the same?

Thanks

Chris

cbjroms
Starting Member

13 Posts

Posted - 2009-11-12 : 14:17:30

I should add that I think that the correct script to use is:

Insert Into
DatabaseB.dbo.TableA
Select * From
DatabaseA.dbo.TableA

But when I run this I get the following error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_TableA'. Cannot insert duplicate key in object 'dbo.TableA'.
The statement has been terminated.

Why is this happening?

Thanks

Chris

cbjroms
Starting Member

13 Posts

Posted - 2009-11-12 : 14:19:17

I have sorted this by deleting contents on Table A on test database first.

Thanks

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources