+ Reply to Thread
Results 1 to 3 of 3

Thread: SQL Server CREATE TABLE..LIKE

  1. #1
    Wolfmaster Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy's Avatar
    Join Date
    Mar 2008
    Location
    Peoria, IL
    Posts
    1,037
    Blog Entries
    2
    Rep Power
    13

    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.
    Wolffy
    ------------------------
    Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.

  2. #2
    Super Sarcasm Mistress mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere's Avatar
    Join Date
    Mar 2008
    Location
    Wide Awake In Dreamland
    Posts
    436
    Rep Power
    7

    i have a headache from reading that ...

    btw ... Lucky Strike Means Fine Tobacco (i like smoke breaks)
    Quote of the Month:
    Leaders: Leaders are like eagles. We don't have either of them here.

    Questions to Ponder:
    Why do banks charge you a "non-sufficient funds fee" on money they already know you don't have?

    iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
    copyright © 2008 sbenj69

    Sarchasm: The gulf between the author of sarcastic wit and the person who doesn't get it.

  3. #3
    Wolfmaster Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy's Avatar
    Join Date
    Mar 2008
    Location
    Peoria, IL
    Posts
    1,037
    Blog Entries
    2
    Rep Power
    13

    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!
    Wolffy
    ------------------------
    Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.

+ Reply to Thread

Similar Threads

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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

SEO by vBSEO