Thursday, August 26, 2010

Insert a backup table back into the original table

1. Get the column list
SELECT COLUMN_NAME + ', ' from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '(table name)'

2. Copy the results of this query, and remove the comma from the last record.

3. Run the query:

SET IDENTITY_INSERT originalTableName ON

INSERT INTO originalTableName
(paste column list here)
SELECT
(paste column list here)
FROM (backup table)

SET IDENTITY_INSERT  originalTableName OFF

Note: If your table doesn't have an identity column you can safely disregard the first and last lines of this query.


Reference: http://blog.sqlauthority.com/2007/03/28/sql-server-fix-error-8101-an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a-column-list-is-used-and-identity_insert-is-on/

No comments:

Post a Comment

There was an error in this gadget