How do I copy table data from one database to another when there is an Identity column

Question

I am trying to do something like the following, but it fails because I have to explicity name each column in the table (which can be HUGE). Is there a simple way to do this when a table contains an identity column.


--------------------------------------------------------------------
SET IDENTITY_INSERT t_customer ON
GO

INSERT INTO db1.dbo.t_customer SELECT * FROM db2.dbo.t_customer

GO

 

DECLARE @max_id BIGINT

SELECT @max_id = max(customer_uid) FROM t_customer

DBCC CHECKIDENT(t_customer, RESEED, @max_id)

SET IDENTITY_INSERT t_customer OFF

GO
--------------------------------------------------------------------

Answer 1

First, you may want to consider Replication for this, which is probably much better suited to your needs.

Secondly, if you copy data into this table, do you really want an IDENTITY column in the output table?  If it does not have any original records Inserted there itself, you could just drop it (the IDENTITY attribute).

Finally, you could execute this statement and then cut and paster it's output to replace the INSERT statement line in your script (above).:

Declare @sqlcmd NVarchar(MAX)
Select @sqlcmd = 'INSERT INTO db1.dbo.t_customer('
Select @sqlcmd = @sqlcmd + 
	CASE ORDINAL_POSITION When 1 Then COLUMN_NAME
	ELSE ', ' + COLUMN_NAME End
 From db1.INFORMATION_SCHEMA.COLUMNS
 Where TABLE_SCHEMA = 'dbo'
  And TABLE_NAME = 't_customer'

Select @sqlcmd = @sqlcmd + ')
 SELECT * FROM db2.dbo.t_customer'

Select @sqlcmd

---

RBarryYoung

Answer 2

I tried dropping it, but it is SQL Server 2005 and I don't see a way to temporarily drop/restore the IDENTITY property on a column.

Answer 3

Use management studio to do it.  There is no direct T-Sql command, so SSMS has to do the whole copy-Drop-Rename trick.

---

RBarryYoung

Answer 4

Check out this one:

http://shahanayyub.wordpress.com/2012/04/20/copy-one-database-table-rows-to-another-database-table/

For column name the link provides the column name including Identity fields, you can use this instead:

select (name + ',') as TableColumns from sys.columns where object_id = object_id('YourTableName') and is_Identity=0

Answer 5

This may help you, please refer it.

http://www.bigresource.com/MS_SQL--Copy-Records-From-one-table-to-another-table-with-same-structure--m9NFvanI.html