Results 1 to 13 of 13

Thread: Sql Server copying table

  1. #1

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Sql Server copying table

    I use this query to copy an existing table structure without data:

    Code:
    SELECT TOP 0 * INTO BusDataNew FROM BusData
    It works well, but in the process no contraints are copied and I need to add queries (in SSMS) to add default values to fields and PK's and FK's to the table.
    The internet queries I have found for this are difficult to interpret and use.
    Any help will be apreciated.

    Thanks
    PK

  2. #2
    coder. Lord Orwell's Avatar
    Join Date
    Feb 2001
    Location
    Elberfeld, IN
    Posts
    7,621

    Re: Sql Server copying table

    since you are using sql server, why don't you script your existing table as "create" and then just rename the table in the script? Your new table would then have all of the constraints your current one has.
    My light show youtube page (it's made the news) www.youtube.com/@artnet2twinkly
    Contact me on the socials www.facebook.com/lordorwell

  3. #3

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Sql Server copying table

    Ok thanks, I will rather do that then.
    PK

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Sql Server copying table

    Quote Originally Posted by Lord Orwell View Post
    since you are using sql server, why don't you script your existing table as "create" and then just rename the table in the script? Your new table would then have all of the constraints your current one has.
    Or write a stored procedure with an IN-Parameter being the new tablename
    https://social.msdn.microsoft.com/Fo...um=transactsql
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Sql Server copying table

    You can start with this and convert it to a create table using the results
    Code:
     SELECT 
                        COLUMN_NAME AS [Field Name] ,
                        DATA_TYPE AS [Data Type] ,
                        Case DATA_TYPE 
                            WHEN 'char' THEN CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH) END 
                            WHEN 'nchar' THEN CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH) END 
                            WHEN 'varchar' THEN CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH) END 
                            WHEN 'nvarchar' THEN CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH) END 
                            WHEN 'decimal' THEN '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ',' + CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')' 
                            WHEN 'float' THEN '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ',' + CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')' 
                            WHEN 'datetimeoffset' THEN '(' + CONVERT(VARCHAR(10), DATETIME_PRECISION) + ')'  
                        END AS [Sizing (Percision/Scale)] , 
                        CASE INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE 
                            WHEN 'YES' THEN 'Yes' 
                            ELSE 'No' 
                        END AS [Nullable] ,
                        CASE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') 
                            WHEN 1 THEN 'Yes' 
                            ELSE 'No' 
                        END AS [Identity] ,
                        COLUMN_DEFAULT AS [Default Value] ,
                        CASE COLUMNPROPERTY(object_id(TABLE_NAME),column_name,'IsComputed' ) 
                            WHEN 1 THEN 'Yes' 
                            ELSE NULL 
                        END AS [Computed Column] ,
                        ORDINAL_POSITION AS [Position in Table] 
                        ,CC.definition AS [Caclulated Field Formula]
    					,MC.masking_function AS [Masking Function]
                FROM INFORMATION_SCHEMA.COLUMNS 
                LEFT OUTER JOIN sys.computed_columns CC 
                        ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = CC.name 
                LEFT OUTER JOIN sys.masked_columns MC 
                          ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = MC.name 
                         AND ORDINAL_POSITION = MC.column_id 
                         AND MC.object_id = (SELECT object_id FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id  WHERE sys.objects.name = 'thetableName' AND  sys.schemas.name = 'theShecema')
    			WHERE TABLE_SCHEMA = 'theShecema' 
                        AND TABLE_NAME = 'thetableName' 
    			ORDER BY ORDINAL_POSITION
    I also have other parts that will create the code for PK, and indexes, FKs, check contstraints, and triggers
    Last edited by GaryMazzone; Feb 12th, 2020 at 09:26 AM.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Sql Server copying table

    script your existing table as "create" and then just rename the table in the script?
    That's what I always do but don't forget to rename all the constraints too
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Sql Server copying table

    Quote Originally Posted by FunkyDexter View Post
    That's what I always do but don't forget to rename all the constraints too
    That's why i said to write a parametrised SP with an IN-Parameter
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Sql Server copying table

    I thank you all for such rich contributions which I will disseminate.
    PK

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Sql Server copying table

    Quote Originally Posted by Zvoni View Post
    That's why i said to write a parametrised SP with an IN-Parameter
    That's really only necessary if the table copy isn't a one-off...

    But then when I see people writing code to "copy" tables but not the data... I start getting suspicious about their design and wondering what exactly they are up to, and that their design isn't proper.
    Typically I see this a lot in"accounting"systems where the dev seems to think that they need a new table for each year.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Sql Server copying table

    tg, true.
    What actually baffles me (and i repeat myself: no practical experience in MSSQL): I know from SQLite and MySQL, that i can get a "CREATE"-Statement for any table (at least in DBBrowser for SQLite and MySQL-Workbench).
    I would be surprised, if SSMS doesn't provide something like that.
    Get that CREATE-Statement, c&p to TextEditor (e.g. Notepad), change the "old" tablename to the new one (Search&Replace), c&p it back into SSMS, FIRE!
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Sql Server copying table

    Quote Originally Posted by Zvoni View Post
    tg, true.
    What actually baffles me (and i repeat myself: no practical experience in MSSQL): I know from SQLite and MySQL, that i can get a "CREATE"-Statement for any table (at least in DBBrowser for SQLite and MySQL-Workbench).
    I would be surprised, if SSMS doesn't provide something like that.
    Get that CREATE-Statement, c&p to TextEditor (e.g. Notepad), change the "old" tablename to the new one (Search&Replace), c&p it back into SSMS, FIRE!
    It does... right click on any table and select "Script As..." and there's options for complete CRUD statements, as well as CREATE... and the CREATE option has options, with/without constraints|Indexes|etc.

    However, I suspect in this case, he's creating new tables based on old ones in the app, and it's not necessarily a one-off case, which leads me to suspect a flawed design.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    Lively Member
    Join Date
    Jan 2020
    Posts
    120

    Re: Sql Server copying table

    Hello,@Peekay

    Please try this query,To Sql Server copying table:

    Code:
       SELECT * INTO ABC_1 FROM ABC;
    I hope above query will be useful for you.

    Thank you.

  13. #13

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Sql Server copying table

    Prahlad. Your query does not copy the table attributes.
    Lord Orwell suggested the best solution.
    PK

Posting Permissions

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



Click Here to Expand Forum to Full Width