locked
Fill table with IDENTITY column without specifying column names RRS feed

  • Question

  • I have to copy data from an old server to a new one. There are several databases, some with 100 or more tables. All of the databases and tables have already been replicated on the new server (including indexes, constraints, etc) and have been in use for testing. The database names have been changed on the new server, but the table names are the same. Many of the tables have IDENTITY columns.

    I'm trying to write a procedure that will flush and refill the tables on the new server using a cursor and dynamic SQL. I see 2 options, but each has a big stumbling block:

    1. DROP and SELECT INTO each table. Problem: this doesn't carry forward the constraints and indexes.
    2. TRUNCATE and INSERT INTO each table. Problem: T-SQL requires a column list when I set IDENTITY INSERT ON.

    Is there a T-SQL (not SSIS) technique for solving this problem that doesn't require either 1. scripting out all the keys and indexes, or 2. specifying a column list for every table with an IDENTITY column?

    Thanx!


    Darrell H Burns

    Friday, October 6, 2017 7:55 PM

Answers

  • You could generate BCP commands to bulk data out and bulk data in. Use -E to specify that you want IDENTITY VALUES to be retained.

    Then again look at this:

    SELECT o.name, (SELECT quotename(c.name) + ', ' 
                    FROM   sys.columns c
                    WHERE  c.object_id = object_id
                    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
    FROM   sys.objects o
    WHERE  o.type = 'U'

    That is how you generate the column list. Now you only need to improve it, so that it generates the INSERT commands.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 6, 2017 9:55 PM

All replies

  • You could generate BCP commands to bulk data out and bulk data in. Use -E to specify that you want IDENTITY VALUES to be retained.

    Then again look at this:

    SELECT o.name, (SELECT quotename(c.name) + ', ' 
                    FROM   sys.columns c
                    WHERE  c.object_id = object_id
                    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
    FROM   sys.objects o
    WHERE  o.type = 'U'

    That is how you generate the column list. Now you only need to improve it, so that it generates the INSERT commands.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 6, 2017 9:55 PM
  • Erland,

    I executed the column list query you provided above but I confess that I don't understand the 2nd column output. How would I use that to build INSERT statements?

    This is the technique for generating a table schema that I use. I generate the script from dynamic SQL looping over the table list...

    Select 'DailyFundAccts' as Table_Name
    ,c.name as Column_Name 
    ,column_id as Column_ID
    ,t.name + CASE WHEN t.name like '%char' AND c.max_length is not null 
    	THEN '(' + cast(c.max_length/2 as varchar) + ')' ELSE '' END as DataType
    From MFD.sys.columns c
    INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
    Where object_id=OBJECT_ID('MFD.dbo.DailyFundAccts')
    	AND t.name not like 'sys%'

    Output:


    Darrell H Burns

    Saturday, October 7, 2017 3:33 PM
  • I executed the column list query you provided above but I confess that I don't understand the 2nd column output. How would I use that to build INSERT statements?

    Using copy and paste, it would be:

    INSERT tagettable (<columnlist>)
       SELECT <columnlist>
       FROM   srctable

    Or you augment my query, so that it generates that piece of code.

    This is the technique for generating a table schema that I use. I generate the script from dynamic SQL looping over the table list...

    I'm not sure how you use that result set, but you could use that as well to generate the INSERT statements. My point was that you can generate the code, and in that case the column list does not become a burden. The point with my query is that it shows to biuld a comma-separated list from a number of values. The syntax is quite obscure, but it works. (But beware: you get a trailing comma at the end, that you need to trim.)

    But if you have, say, a PowerShell script that reads the data and generates the code, that works too.

    Saturday, October 7, 2017 4:27 PM

  • Darrell H Burns

    Saturday, October 7, 2017 7:38 PM
  • The behavior of aggregate string concatenation using this method is undefined and may not produce the expected results. SQL Server 2017 introduces STRING_AGG to solve he problem. The XML PATH method is the proper solution in earlier versions.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Naomi N Sunday, October 8, 2017 5:03 AM
    Saturday, October 7, 2017 7:51 PM
  • It's understandable that you were not familiar with the FOR XML PATH syntax. It is anything but intuitive. The syntax you use is lot easier to grasp, but as Dan says, it may not produce the correct result. That's why I suggested FOR XML, since in all its crypticness, it fulfils it promise.

    STRING_AGG is a welcome addition in SQL 2017.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, October 7, 2017 9:33 PM
  • Thanx, guys. I learn a lot from you.

    Darrell H Burns

    Sunday, October 8, 2017 12:44 AM