Select Into From, Create table without identity property

sql, sql-server

Solution

Your code looks like an attempt to keep a history of data changes. Consider using Change Data Capture instead of rolling your own solution.

One way to allow inserts with the `identity` column specified is identity_insert:

SET IDENTITY_INSERT TableName ON

You could turn this on in your second exec. Since you're copying the entire table, you'd have to clean out the history table before you copy, for example using `truncate`:

Set @Query  = 
    'truncate table ' + @TableName  + 'History; ' +
    'set identity_insert ' + @TableName 'History on; ' +
    'Insert Into ' + @TableName  + 'History Select *   FROM ' + @TableName '; ' +
    'set identity_insert ' + @TableName 'History off; '

A nicer solution would be to modify the first exec to create a table without an `identity` column. I have not found a practical way to do that. SQL Server requires you to drop the column and recreate it, which would make for very cumbersome dynamic SQL.

Problem

I have stored procedure like this that executed after insert command for any table ``` Create Procedure [dbo].[HistoryInsert]( @TableName nVarchar(500), @RecordId bigInt ) As declare @Query nVarChar(max) if Not Exists (Select Top 1 1 From Information_schema.tables Where Table_Name = @TableName + 'History') Set @Query = 'Select * Into ' + @TableName + 'History FROM ' + @TableName Else Set @Query = 'Insert Into ' + @TableName + 'History Select * FROM ' + @TableName Exec(@Query) Exec(@Query) ``` When this procedure executed for first time , History table created. and when this procedure executed for second time, insertion failed because created table has identity column.how to select into from table for all column without increment identity property for column.

Original source