CODEWITHSUNDEEP
sqlsql-server
Дмитрий Чистик
Дмитрий Чистик

Reputation: 638

How to copy row in table with key column

How to copy a row while in her auto-increment key column

INSERT [Table] SELECT * FROM [Table] where [Table].[id]=7

not work

Upvotes: 1

Views: 41

Answers (1)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

Imagine that your AUTO_INCREMENT (in SQL-Server It's IDENTITY) column's name is col1 so you can try (not include It in select list): 

INSERT INTO [Table1] (col2, col3, col4) -- specify all columns except col1
SELECT col2, col3, col4 -- specify all columns except col1
FROM [Table2] 
WHERE [Table2].[id]=7

Following example working fine:

CREATE TABLE #Temp1 
(
    ID INT NOT NULL IDENTITY (1,1),
    Name NVARCHAR(40)
)
GO
CREATE TABLE #Temp2 
(
    ID INT NOT NULL IDENTITY (1,1),
    Name NVARCHAR(40)
)
GO
INSERT INTO #Temp1 (Name) VALUES ('Lisa'), ('Jesicca'), ('John')
GO
INSERT INTO #Temp2 (Name)
SELECT Name
FROM #Temp1
GO
SELECT * FROM #Temp1
GO
DROP TABLE #Temp1
DROP TABLE #Temp2

Upvotes: 1

Related Questions