InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 03:23:28 GMT20http://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspxThank you, Scott! With the addition of a WHERE clause on the first SELECT, I end up with an empty table without a IDENTITY property, which is EXACTLY what I was looking for. Prefacing the * with my additional control columns doesn't mess up the solution, either. SSC rules!Thu, 03 Jan 2013 09:22:07 GMTPhilPachahttp://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspx[quote][b]Jeff Moden (12/27/2012)[/b][hr][quote][b]PhilPacha (12/27/2012)[/b][hr]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.[/quote]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...[code="sql"] SELECT SomeBasicIntColumn = ISNULL(CAST(SomeIdentityColumn AS INT),0), other columns INTO #SomeTempTable FROM dbo.SomeOtherTable WHERE 1 = 0;[/code][/quote]You can also do this with an integer expression[code="sql"]SELECT SomeBasicIntColumn = 0 + SomeIdentityColumn, other columns INTO #SomeTempTable FROM dbo.SomeOtherTable WHERE 1 = 0[/code]Mon, 31 Dec 2012 16:29:16 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspx[quote][b]ScottPletcher (12/31/2012)[/b][hr]Please try this:[code="sql"]SELECT *INTO dbo.newtableFROM dbo.oldtable_with_identityUNION ALLSELECT TOP (1) *FROM dbo.oldtable_with_identityWHERE 1 = 0[/code]The identity property should be left off the column in the new table :-).[/quote]Nicely done, Scott. You can also get away without using the TOP (1). The cpu time used and the number of reads are identical.Mon, 31 Dec 2012 16:11:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspxdeletedMon, 31 Dec 2012 15:27:33 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspxPlease try this:[code="sql"]SELECT *INTO dbo.newtableFROM dbo.oldtable_with_identityUNION ALLSELECT TOP (1) *FROM dbo.oldtable_with_identityWHERE 1 = 0[/code]The identity property should be left off the column in the new table :-).Mon, 31 Dec 2012 14:40:28 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspxThanks 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".Thu, 27 Dec 2012 08:53:44 GMTPhilPachahttp://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspx[quote][b]PhilPacha (12/27/2012)[/b][hr]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.[/quote]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...[code="sql"] SELECT SomeBasicIntColumn = ISNULL(CAST(SomeIdentityColumn AS INT),0), other columns INTO #SomeTempTable FROM dbo.SomeOtherTable WHERE 1 = 0;[/code]Thu, 27 Dec 2012 08:46:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspxWhat 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. [code]create table #Source( SourceID int identity, SomeValue varchar(10))insert #Sourceselect 'Source 1' union allselect 'Source 2'select * into #Test from #Sourcewhere 1 = 2insert #Testselect 'Test'select * from #Testdrop table #Sourcedrop table #Test[/code]Thu, 27 Dec 2012 08:42:06 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspxI 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.Thu, 27 Dec 2012 08:41:29 GMTPhilPachahttp://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspxWhy not drop the column after creation and before putting anything in it?Thu, 27 Dec 2012 08:37:05 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspxUsing 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.Thu, 27 Dec 2012 08:35:36 GMTPhilPacha