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