Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: OUTPUT INSERTED INTO can't get column value when that column is not inserted

    Hi,

    I'm doing a data migration job from our old to our new database. The idea is that we don't want to clutter our new tables with the id's of the old tables, so we are not going to add a column "old_system_id" in each and every table that needs to be migrated.

    I have created a number of tables in a separate schema "dm" (for Data Migration) that will store the link between the old database table and the new database table. Suppose that the id of a migrated record in the old database is 'XRP002-89' and during the insert into the new table the IDENTITY column id gets the value 1, the link table will hold : old_d = 'XRP002-89', new_id = 1, and so on.

    I knew I can get the value of IDENTITY columns with the OUTPUT INTO clause, although I have never actually used it. And now I can't get it to do what I need.

    Below is some code to set up three tables: the old table, the new one, and the table that will hold the link between the id's of records in the old database table and the new database table.
    Code:
    CREATE TABLE DaOldTable(
    	pk		CHAR(10)	NOT NULL,
    	a_column	CHAR(10)
    )
    
    CREATE TABLE DaNewTable(
    	id		INT	NOT NULL 	IDENTITY,
    	a_column	CHAR(10)
    )
    
    CREATE TABLE link_old2new_DaTable(
    	DaOld_id	CHAR(10)	NOT NULL,
    	DaNew_id	INT	NOT NULL
    )
    
    INSERT INTO DaOldTable(pk, a_column) VALUES
    ('1st rec', 'first rec'),
    ('2nd rec', 'second rec'),
    ('Nth rec', 'last rec')
    
    SELECT * FROM DaOldTable
    --pk		a_column
    --1st rec 	first rec 
    --2nd rec 	second rec
    --Nth rec 	last rec
    Below I tried to use the OUTPUT INSERTED INTO clause. Beside getting the generated IDENTITY value, I also need to capture the value of the old id that will not be migrated to the new table. When I use "OUTPUT DaOldTable.pk" the system gives me the error: "The multi-part identifier "DaOldTable.pk" could not be bound." Using INSERTED .id gives no problem.
    Code:
    INSERT INTO DaNewTable(a_column)
    --OUTPUT DaOldTable.pk, INSERTED.id link_old2new_DaTable--(DaOld_id, DaNew_id)
    SELECT a_column
    FROM DaOldTable
    
    SELECT * FROM DaNewTable
    --id	a_column
    --1	first rec 
    --2	second rec
    --3	last rec  
    
    SELECT * FROM link_old2new_DaTable
    --Expected result in link_old2new_DaTable
    --DaOld_id	DaNew_id
    --1st rec	1
    --2nd rec	2
    --Nth rec	3
    
    --but getting "The multi-part identifier "DaOldTable.pk" could not be bound."
    
    DROP TABLE DaOldTable
    DROP TABLE DaNewTable
    DROP TABLE link_old2new_DaTable
    How can I populate a table that must hold the link between the id's of records in the old database table and the new database table? The records are migrated with set-based inserts.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That is a good question!

    OUTPUT Clause (Transact-SQL)
    <column_name> ::=
    { DELETED | INSERTED | from_table_name } . { * | column_name }


    from_table_name
    Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.
    If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.
    As for a workaround... I'm going to toddle off and have a play..
    Last edited by gvee; 08-22-13 at 08:38.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Bingo!
    Code:
    MERGE DaNewTable AS target
    USING DaOldTable AS source
      ON 1=2
    WHEN NOT MATCHED THEN
      INSERT (a_column)
        VALUES (source.a_column)
          OUTPUT inserted.*, source.*
    ;
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Dang, George, you're good!!

    I tweaked your code a little bit to have it INSERT the result into the link_old2new_DaTable table. This is the result:
    Code:
    MERGE DaNewTable as target
    USING	(SELECT pk, a_column
    	FROM DaOldTable
    	) AS source
    ON     (1=0) -- make sure the result is False
    WHEN NOT MATCHED THEN
    	INSERT (a_column) VALUES (source.a_column)
    	OUTPUT source.pk, INSERTED.id INTO link_old2new_DaTable (DaOld_id, DaNew_id);
    Thanks a lot, George!
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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