CODEWITHSUNDEEP
sql-servercopy
M Z
M Z

Reputation: 39

Copy whole row excluding identifier column

I'm trying to insert a new row into a table which is an exact copy of another row except for the identifier. Previously I hadn't had the issue because there was an ID-column which didn't fill automatically. So I could just copy a row like this

INSERT INTO table1 SELECT * FROM table1 WHERE Id = 5

And then manually change the ID like this

WITH tbl AS (SELECT ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) AS RNr, Id
FROM table1 WHERE Id = 5 
UPDATE tbl SET Id = (SELECT MAX(Id) FROM table1) + 1 
WHERE RNr = 2

Recently the column Id has changed to not be filled manually (which I also like better). But this leads to the error that I obviously can't fill that column while IDENTITY_INSERT is false. Unfortunately, I don't have the right to use SET IDENTITY_INSERT IdentityTable ON/OFF before and after the statement, which I also would like to avoid anyways.

I'm looking for a way to insert the whole row excluding the Id column without naming each other column in the INSERT and SELECT statement (which are quite a lot).

Upvotes: 0

Views: 63

Answers (2)

M Z
M Z

Reputation: 39

Okay, I found a way by using a temporary table

SELECT * INTO #TmpTbl
FROM table1 WHERE Id = 5;
ALTER TABLE #TmpTbl
DROP COLUMN Id;
INSERT INTO table1 SELECT * FROM #TmpTbl;
DROP TABLE #TmpTbl

Upvotes: 0

Max
Max

Reputation: 44

In the code below the maximum value of the ID gets added by one, so your integrity is not violated.

insert into table1 
select a.Top_ID, Column2, Column3, ColumnX 
from table1 t1
outer apply (select max(id_column) + 1as Top_ID from table1) as a 
where t1.Id = 1

Upvotes: 1

Related Questions