DeveloperBarn

Wolffy · #1 March 12th, 2009, 04:32 PM

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.

mehere · #2 March 12th, 2009, 04:49 PM

i have a headache from reading that ...

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

Wolffy · #3 March 12th, 2009, 04:51 PM

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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Create History Table / Outcome results	Rebelle	Database Design Help	11	June 10th, 2009 10:54 AM
Create control according to their data type	guddu	.Net Development	53	January 23rd, 2009 12:21 PM
Create/Print Form data	Rebelle	ASP Development	5	December 10th, 2008 10:25 AM
Is there an easier way to create a countif function?	Lauramc	Visual Basic Programming	0	August 7th, 2008 06:26 PM
Create Virtual Directories in IIS	jmurrayhead	Microsoft IIS	0	March 21st, 2008 10:08 AM

DeveloperBarn

SQL Server CREATE TABLE..LIKE

SQL Server CREATE TABLE..LIKE

Similar Threads