This is a discussion on SQL Server CREATE TABLE..LIKE within the SQL Code Samples forums, part of the SQL Development category; I was going to post this up as a question for the forum, but then hit on the answer during ...
| |||||||
|
#1
| ||||
| ||||
| 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; Consider the following defintion for 'yourTable' Code: CREATE yourTable ( aColumn INT IDENTITY(1,1) NOT NULL, bColumn nvarchar(20), cColumn money ) 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) 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
| ||||
| ||||
| i have a headache from reading that ... btw ... Lucky Strike Means Fine Tobacco (i like smoke breaks)
__________________ Quote of the Month: Mistakes: It could be that the purpose of your life is only to serve as a warning to others. 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
| ||||
| ||||
| 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. |
![]() |
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
| |
| ||||
| 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 |