Reputation: 3
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
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