CODEWITHSUNDEEP
sqlsql-server
compcobalt
compcobalt

Reputation: 1362

SQL Server : copy row and Insert into to the same table but with a different ID

I would just like to copy a specific LOAD_NO from a table called LOADS and insert it back into the same LOADS table but without using the old LOAD_NO (aka ID)....

The tables name is LOADS.

  1. I would like to copy a specific row and insert it into the table but without needing to specify all of the fields (because the table has a lot of fields)

  2. I need to exclude the field named: LOAD_NO

  3. Then delete the old row

I tried something like this:

I cloned the LOADS table without cloning the data and I named that table LOADS_TempTable and added the identity of LOAD_NO seed start at: 11600 

SELECT * 
INTO LOADS_TempTable 
FROM LOADS WHERE LOAD_NO = 200;

ALTER TABLE LOADS_TempTable DROP COLUMN LOAD_NO;

INSERT INTO LOADS 
   SELECT * 
   FROM LOADS_TempTable;

DELETE FROM LOADS 
WHERE LOAD_NO = 200;

Here is how the column LOAD_NO in the table LOADS looks:

enter image description here

The error I get says the following:

Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'LOADS' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I tried to add: 

set identity_insert LOADS OFF
GO

and also

set identity_insert LOADS ON
GO

I always get the same error.

THANKS FOR ANY HELP :)

Upvotes: 3

Views: 9270

Answers (3)

AiApaec
AiApaec

Reputation: 660

It:

INSERT INTO LOADS 
SELECT * --now begins from the second column, you drop the first column.
FROM LOADS_TempTable;

is the same:

INSERT INTO LOADS VALUES(Load_No, second_colum, n_column)
SELECT second_colum, n_column
FROM LOADS_TempTable;

you're trying to insert a second_column value into a first_column (Load_No) so you get error.

Solution: 

INSERT INTO LOADS VALUES(second_colum, n_column)
SELECT * -- or: SELECT second_colum, n_column 
FROM LOADS_TempTable;

Upvotes: 1

marc_s
marc_s

Reputation: 754388

Since LOAD_NO is an identity column, you should not be specifying a value for it when inserting. So you need to modify our INSERT statement to not include the LOAD_NO column - something like this:

INSERT INTO LOADS(col1, col2, ..., colN)  -- all columns *EXCEPT* LOAD_NO
   SELECT col1, col2, ...., colN          -- all columns *EXCEPT* LOAD_NO 
   FROM LOADS_TempTable;

SQL Server will automatically assign a new LOAD_NO to the row you're inserting - after all, that's the job of the identity column!

Upvotes: 6

Joel Coehoorn
Joel Coehoorn

Reputation: 415690

Try this:

set identity_insert LOADS ON
Update LOADS set Load_No = (select max(load_no) + 1 from loads) where load_no = 200
set identity_insert LOADS OFF

You probably also want to wrap that in a transaction.

Upvotes: 1

Related Questions