CODEWITHSUNDEEP
sql-server
pmgurus
pmgurus

Reputation: 3

Insert a duplicate record in SQL Server with identity column ON

Here is my requirement: I want to insert some duplicate records into my SQL Server table. A column called QID is the PK - identity column auto increments by 1.

I tried the following ways:

insert into qtable 
    select top 1 * 
    from qtable

which caused an error:

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

Then I tried without the identity column including all columns in the table.

INSERT INTO qtable  
    SELECT TOP 1   
        col1, col2, col3, col4...  
    FROM
        qtable

which gave me values inserted but col1 value was shifted to col2 and col2 to col3 and so on and col1 had the identity value.

How can I insert a duplicate record but identity column must be auto incremented?

Upvotes: 0

Views: 169

Answers (1)

TomStroemer
TomStroemer

Reputation: 1560

You have to specify the columns for your insert statement.

Like this:

INSERT INTO qtable (col1,col2,col3) 
select top 1 col1
   , col2
   , col3 
from qtable

Upvotes: 2

Related Questions