Select Into From, Create table without identity property

Question

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.

Answer 1

There is some lifehack

select *
into #tmp_table
from Table

union all

select *
from Table 
where 1<>1

Using union all deprecate SQL Endgine to inherit constraints and identity too.

      When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
            - The SELECT statement contains a join, GROUP BY clause, or aggregate function.
            - Multiple SELECT statements are joined by using UNION.
            - The identity column is listed more than one time in the select list.
            - The identity column is part of an expression.
            - The identity column is from a remote data source.

Answer 2

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.