Copy Data from One SQL Server Table to Other in Same Database Without Specifying Columns

In SQL Server there is the ability to INSERT all of the data from one table into another using the following statement:

INSERT INTO TABLE1 SELECT * FROM TABLE2

When running this on a table with an identity column, even thoug开发者_运维百科h we have run the command SET IDENTITY_INSERT TABLE1 ON, we get the error:

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

This implies that we would have to list off all of the columns within the INSERT in order for this to work properly. In the situation that we are in, we do not have access to the column names, only the list of the tables we need to copy over. Is there any way to get around this limitation?

You can dynamically build the SQL statement at runtime by querying the data dictionary, like this:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Your_Table'

You could use this to dynamically build the SQL statement to do this insert, and it would have all the columns in it.

You could do the above in T-SQL procedure, and you could use sp_executesql to run the dynamically built query. For example, if your query was in a variable called @SQL, you could do:

EXECUTE sp_executesql  @SQL

An example for this is given below:

create table three (id int identity, other int)
create table four (id int identity, other int)

insert into three select 1
insert into three select 2

declare @sql nvarchar(max)
set @sql = REPLACE(
'
set identity_insert four on
insert into four (:columns:) select :columns: from three
set identity_insert four off',
':columns:',
stuff((
    select ','+quotename(name)
    from sys.columns
    where object_id=object_id('four')-- and is_identity=0
    for xml path('')),1,1,''))
EXECUTE sp_executesql @SQL