May 21, 2013 at 1:06 pm
Hello,
I want to know if anyone knows of an easy way to do a SELECT INTO from a table with an identity column, but I do not want the Identity column to be marked as an identity column in the new table. I do want the data from the identity column, but I don't want to have the identity property.
The best I can come up with is using the union operator:
with t1 as
(select * from table union select * from table)
select * into newtable from t1
May 21, 2013 at 1:11 pm
USE ProofOfConcept;
GO
CREATE TABLE dbo.T1
(ID INT IDENTITY
PRIMARY KEY,
Col1 CHAR(1) NOT NULL);
GO
INSERT INTO dbo.T1
(Col1)
VALUES ('A');
GO
SELECT CAST(ID AS INT) AS ID,
Col1
INTO dbo.T2
FROM dbo.T1;
GO
SELECT *
FROM sys.columns AS C
WHERE C.object_id = OBJECT_ID(N'dbo.T2');
GO
DROP TABLE dbo.T1;
DROP TABLE dbo.T2;
Wrap the identity column in a Cast/Convert to its native datatype. NULLability, etc., will also revert to default, but that can be fixed easily (if desired) by Alter Table scripts after the Select Into.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2013 at 1:19 pm
I'm sorry, I should've mentioned I am deliberately trying to avoid listing out columns. Trying to see if there is a way to populate several QA tables from a production database without having to update this code everytime they update the table. I will probably have to end up doing dynamic sql, but I want to make sure I explore all options first.
May 21, 2013 at 2:05 pm
You'll need to do it with dynamic SQL, in that case. Pretty easy to select the column names from sys.columns and go from there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2013 at 3:15 pm
This should "turn off" the identity property:
SELECT *
INTO dbo.newtable
FROM dbo.table
UNION ALL
SELECT TOP 1 (*)
FROM dbo.table
WHERE 1 = 0
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 22, 2013 at 3:14 am
ScottPletcher (5/21/2013)
This should "turn off" the identity property:
SELECT *
INTO dbo.newtable
FROM dbo.table
UNION ALL
SELECT TOP 1 (*)
FROM dbo.table
WHERE 1 = 0
Nice one, Scott! Need to remember this.
May 22, 2013 at 8:07 am
Jan Van der Eecken (5/22/2013)
ScottPletcher (5/21/2013)
This should "turn off" the identity property:
SELECT *
INTO dbo.newtable
FROM dbo.table
UNION ALL
SELECT TOP 1 (*)
FROM dbo.table
WHERE 1 = 0
Nice one, Scott! Need to remember this.
Agreed. Thanks Scott
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply