SQL To Copy Data Between Tables In Different Databases

Quick daytime entry; so I don't forget it too quickly.

So the problem is that I have duplicated the structure of a table from one database into another. Now, I want to populate it with the original data, including identity columns. And, by the way, both databases are registered in the same client. The way to handle this is with the following bit of sql:

SET IDENTITY_INSERT myTableName ON
insert into myTableName(column1, column2, columnN)
select column1, column2, columnN from theOriginalDataBase.dbo.theOriginalTable
SET IDENTITY_INSERT myTableName OFF

The SET IDENTITY_INSERT myTableName ON and SET IDENTITY_INSERT myTableName OFF ought to be self-explanatory. No, really, I haven't got a thing to add about them. The only thing worth mentioning is that nomenclature of the original database: theOriginalDataBase.dbo.theOriginalTable. Very likely theOriginalTable will have the same name as myTableName.

Other than that, make sure to explicitely reference your column names.

Related Blog Entries

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1.001. Contact Blog Owner