SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT * INTO Table without propagating IDENTITY attribute?


SELECT * INTO Table without propagating IDENTITY attribute?

Author
Message
PhilPacha
PhilPacha
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 2529
Using the simplest SELECT INTO syntax, the IDENTITY property of a column is part of the definition of the destination table. Is there a simple way to avoid this? I can't seem to find anything in the documentation, or my google-foo is deficient.

I'm trying to create an mirror image of a table, using a false WHERE clause to create only the table structure.



Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7084 Visits: 4115
Why not drop the column after creation and before putting anything in it?

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
PhilPacha
PhilPacha
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 2529
I want a clone of the table (with 3 addition, preceeding, columns), with the original column sequence, if possible. I'd rather not have to resort to dynamic SQL to define the table, but the presence of the IDENTITY attribute is a "deal killer", since the purpose is short-term "before / after" storage for content changes.



Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64990 Visits: 17979
What you described would create that mirror image. If you just not want to have the identity column in the new table, you can simply exclude it from the select list.


create table #Source
(
SourceID int identity,
SomeValue varchar(10)
)

insert #Source
select 'Source 1' union all
select 'Source 2'

select *
into #Test
from #Source
where 1 = 2

insert #Test
select 'Test'

select * from #Test

drop table #Source
drop table #Test



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222396 Visits: 42003
PhilPacha (12/27/2012)
Using the simplest SELECT INTO syntax, the IDENTITY property of a column is part of the definition of the destination table. Is there a simple way to avoid this? I can't seem to find anything in the documentation, or my google-foo is deficient.

I'm trying to create an mirror image of a table, using a false WHERE clause to create only the table structure.


It's easy. Just recast the column as an INT during the SELECT/INTO. If you want it to be a NOT NULL column, just wrap an ISNULL around that...

 SELECT SomeBasicIntColumn = ISNULL(CAST(SomeIdentityColumn AS INT),0),
other columns
INTO #SomeTempTable
FROM dbo.SomeOtherTable
WHERE 1 = 0
;



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PhilPacha
PhilPacha
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 2529
Thanks Jeff. I was hoping for something which could use "SELECT *", so knowledge about the extant columns wouldn't be necessary. Neat technique, though, if the column names and the column with the IDENTITY attribute are known in advance. I'll add this bit to my "tool box".



ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20390 Visits: 7427
Please try this:



SELECT *
INTO dbo.newtable
FROM dbo.oldtable_with_identity
UNION ALL
SELECT TOP (1) *
FROM dbo.oldtable_with_identity
WHERE 1 = 0




The identity property should be left off the column in the new table :-).

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7084 Visits: 4115
deleted

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222396 Visits: 42003
ScottPletcher (12/31/2012)
Please try this:



SELECT *
INTO dbo.newtable
FROM dbo.oldtable_with_identity
UNION ALL
SELECT TOP (1) *
FROM dbo.oldtable_with_identity
WHERE 1 = 0



The identity property should be left off the column in the new table :-).


Nicely done, Scott. You can also get away without using the TOP (1). The cpu time used and the number of reads are identical.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6712 Visits: 25623
Jeff Moden (12/27/2012)
PhilPacha (12/27/2012)
Using the simplest SELECT INTO syntax, the IDENTITY property of a column is part of the definition of the destination table. Is there a simple way to avoid this? I can't seem to find anything in the documentation, or my google-foo is deficient.

I'm trying to create an mirror image of a table, using a false WHERE clause to create only the table structure.


It's easy. Just recast the column as an INT during the SELECT/INTO. If you want it to be a NOT NULL column, just wrap an ISNULL around that...

 SELECT SomeBasicIntColumn = ISNULL(CAST(SomeIdentityColumn AS INT),0),
other columns
INTO #SomeTempTable
FROM dbo.SomeOtherTable
WHERE 1 = 0
;




You can also do this with an integer expression

SELECT SomeBasicIntColumn = 0 + SomeIdentityColumn,
other columns
INTO #SomeTempTable
FROM dbo.SomeOtherTable
WHERE 1 = 0



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search