Thread: SQL Server CREATE TABLE..LIKE

I was going to post this up as a question for the forum, but then hit on the answer during a L.S.M.F.T. break (30 points if you are old enough to know what that means). Anyway, first this history lesson (everything herein is for the SQL Server dialect):

Have a requirement to create an SQL Table from existing and append a new IDENTITY column. Since the existing tables come from different sources, and in different formats, I can only determine the structure of the input tables at runtime. Further, I can't use the Primary Key values as contained in the source tables as I need to combine multiple sources into 1 SQL Table in my system. Therefore, I create a [Local Key] to [Client Key] lookup table for each input source (which also solve the problem of compound keys, GUIDs as keys, non-integer keys and other horrors).

There are days (like those ending in 'Y') that I curse Microsoft. In most every other SQL dialect it is possible to write a query along the lines of CREATE TABLE myTable LIKE someOtherTable. But no, not in SQL Server. Well, I thought my first attempt was pretty good:

Code:

 SELECT aColumn
  INTO myTable
  FROM yourTable
  WHERE 1=0;
ALTER TALBE myTable
  ADD KeyTableID INT IDENTITY(1,1) NOT NULL;

And this worked great -- until it didn't.

Consider the following defintion for 'yourTable'

Code:

CREATE yourTable (
   aColumn INT IDENTITY(1,1) NOT NULL,
   bColumn nvarchar(20),
   cColumn money
)

In this case, my first solution would fail attempt to add the KeyTableID identity field. As it turned out, the SELECT..INTO retains the IDENTITY constraint on the input table, and multiple identity columns are not permitted.

Well, I figured I could just remove the IDENTITY constraint on the offending input column -- except this is NO T-SQL command to do this. The only way, it appears, is to create a new table without the identity field and copy the data from the original table, delete the original table and then rename the new table. This is what SS Management Console does in the background. Since I would need to create the new table with the same defintion as the original table, I'm back to the original problem and thus chasing my tail.

But wait, the first couple of dozen times I used my original solution it worked, and those tables had identity fields defined. As it turned out, in those cases I was use a View as my input source that JOIN 2, 3 (or 7) tables together each with identity fields. I therefore posited that when multiple identity fields are JOINed, SQL gets confused, throws up its hands, as tosses the IDENTITY constraint out the window. (As an aside, wrapping a View around a single table with an IDENTITY field retains the field).

So, what if I could 'confuse' SQL into thinking there were multiple IDENTITY fields being joined together? That should work. My first thought was to create a silly little, empty table with an IDENTITY field that I could join everything to. However, I realized that I have a ready made table for any input table I could use -- the input table itself!

Code:

SELECT I.aColumn
  INTO myTable
  FROM yourTable AS I
  CROSS JOIN yourTable AS DUMB
  WHERE (1=0)

The above code produces the same result as the original, but the IDENTITY constraint is stripped from [b]aColumn[b].

Q.E.D.

But wait! That code works great on my tiny 6 record example table, but what about my 6,000,000 record, 75 field clunker! Doesn't a CROSS JOIN produce something like 12*10^12 records in this case? Or perhaps, the Query Analyzer is smart enough to figure out that I have an impossible WHERE condition and act accordingly -- or wait, this is Microsoft.

So, I tried it on my 6,000,000 record, 75 field clunker and....it WORKED GREAT. I have my MyKey to YourKey lookup table defined.

So DB (which can be read as DataBase or Developer Barn) fans, there you have it -- the Wolffy approved, SQL Server CREATE TABLE..LIKE alternative.

i have a headache from reading that ...

btw ... Lucky Strike Means Fine Tobacco (i like smoke breaks)

Imagine the headache from Writing it!

(30 points, or 1 rep, to mehere).

Well, I tried to rep ya, gotta spread some love around first. I owe ya a rep point!