Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2008
    Posts
    33

    Unanswered: Output field that is not inserted

    Hello,
    Rather simple question I believe. I am importing data from an old database that is still used but also have other data sources so the surrogate ids in this old database are not used in the main database. Basically it's a legacy database that we are trying to phase out.

    The problem is I want to insert new rows into the main database and on the insert I would like to retrieve the newly created ids and the corresponding oldids as an output to store in another table. The query is below:


    Code:
    	DECLARE @Ids Table(
    		Id int Primary Key, 
    		OldId int Unique  
    	)
    
    
    INSERT INTO Seeds(EntryDate)
    		OUTPUT 
    			INSERTED.Id,
    			D.OldId--this is not allowed
    		INTO @Ids
    	SELECT
    		D.[Entry Date]
    	FROM
    		Data D

    I've high lighted the part of the code that doesn't work. Does anyone know how to do this? Thanks,
    Mike

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    OUTPUT
    inserted.id, deleted.id
    ??
    George
    Home | Blog

  3. #3
    Join Date
    May 2008
    Posts
    33
    Sorry, maybe I didn't make it clear what I meant. The data table has it's own set of ids I want as an output (column oldid) alongside the newly created identity id from the seeds table. Thanks.

  4. #4
    Join Date
    May 2008
    Posts
    33
    This must be a common issue, right?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You amy have to use a trigger to capture this information... (it may actually be possible if you're on 2008 to use output though)
    George
    Home | Blog

  6. #6
    Join Date
    May 2008
    Posts
    33
    Here's better sample code:

    Code:
    --This is an example of the old database data with strange ids.
    DECLARE @OldDataBaseTable TABLE(
    	Id char(5),
    	OtherData varchar(50),
    	EntryDate datetime
    )
    
    INSERT INTO @OldDataBaseTable
    SELECT 'A1000','Some data',getdate()
    UNION ALL
    SELECT 'A1001','Some different data',getdate()
    UNION ALL
    SELECT 'B1000','Some of the same data',getdate()
    UNION ALL
    SELECT 'A2000','Some of the same data',getdate()
    
    
    --this is the new database table with identity generated ids (has other datasources besides old database)
    DECLARE @NewDatebaseTable TABLE(
    	Id int identity(1,1),
    	OtherData varchar(50),
    	EntryDate datetime
    )
    
    --Stores old to new ids
    DECLARE @OldtoNewIds TABLE(
    	New_Id int,
    	OldId char(5)
    )
    
    
    --inserts data into new database from old
    INSERT INTO @NewDatebaseTable(otherdata,entryDate)
    	OUTPUT
    		INSERTED.Id,
    		Null	--This should be O.Id, but it wont work
    	INTO @OldtoNewIds
    SELECT O.OtherData,O.EntryDate
    FROM @OldDataBaseTable O
    	
    
    --This table should be the new ids next to the old ones
    SELECT * FROM @OldtoNewIds

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah, according to BoL (http://msdn.microsoft.com/en-us/library/ms177564.aspx) the from_table_name is only available in DELETE, UPDATE or MERGE.
    George
    Home | Blog

  8. #8
    Join Date
    May 2008
    Posts
    33
    So is there any way round this issue? I seem to have a pretty standard problem... Usually there's pretty standard answers to something like this! Thanks.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't suppose you have a natural key available?
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Workaround/
    Code:
    --This is an example of the old database data with strange ids.
    CREATE TABLE dbo.OldDataBaseTable (
    	Id char(5),
    	OtherData varchar(50),
    	EntryDate datetime
    )
    
    INSERT INTO dbo.OldDataBaseTable
    SELECT 'A1000','Some data',getdate()
    UNION ALL
    SELECT 'A1001','Some different data',getdate()
    UNION ALL
    SELECT 'B1000','Some of the same data',getdate()
    UNION ALL
    SELECT 'A2000','Some of the same data',getdate()
    
    
    --this is the new database table with identity generated ids (has other datasources besides old database)
    CREATE TABLE dbo.NewDatabaseTable (
    	Id int identity(1,1),
    	OtherData varchar(50),
    	EntryDate datetime,
      OldId char(5)
    )
    
    BEGIN TRAN
      --Stores old to new ids
      DECLARE @OldtoNewIds TABLE(
      	New_Id int,
      	OldId char(5)
      )
      
      --inserts data into new database from old
      INSERT INTO dbo.NewDatabaseTable (otherdata, entryDate, OldId)
      	OUTPUT inserted.Id
             , inserted.OldId
        INTO  @OldtoNewIds
      SELECT OtherData
           , EntryDate
           , id
      FROM   dbo.OldDataBaseTable
      
      --This table should be the new ids next to the old ones
      SELECT *
      INTO   dbo.OldtoNewIds
      FROM   @OldtoNewIds
    COMMIT TRAN
    
    ALTER TABLE dbo.NewDatabaseTable
      DROP COLUMN oldid
    
    SELECT *
    FROM   dbo.NewDatabaseTable
    
    SELECT *
    FROM   dbo.OldtoNewIds
    
    GO
    DROP TABLE dbo.NewDatabaseTable
    DROP TABLE dbo.OldDataBaseTable
    DROP TABLE dbo.OldtoNewIds
    George
    Home | Blog

  11. #11
    Join Date
    May 2008
    Posts
    33
    Well the combination of every field is _usually_ unique, but as this is researched data, sometimes the research is incomplete so two objects can appear indistinguishable for a time (apart from their surrogate Ids).

    The work around seems a bit extreme, creating a column and then dropping it! Wont this cause all kind of locking problems? Luckily on this project that is not an issue as the table is not accessed that much, but I wonder how someone on a regularly accessed table would get around this...

  12. #12
    Join Date
    May 2008
    Posts
    33
    I also posted the question here and got the same answer:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116740

    Thanks

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I assumed this was a one off requirement.
    If it's not, then don't drop the column!

    If you don't want that column in your resultset, then simply create a view of the table and don't include that column in your select list
    George
    Home | Blog

  14. #14
    Join Date
    May 2008
    Posts
    33
    I really don't want to have another column in the table just because T SQL doesn't have the right command to do what I want to do. I know my design is fine.

    What if I had several different research sources all with different IDs of different datatypes? I wouldn't want to store them in a single column, so I'd need a column for each old id, and that would mean null value expcept in one of these columns... not ideal. I prefer different tables for each of these as only a minimum amount of rows are created in each and Nulls are not needed.

    I think I'm going to use cursors for now (regretfully) until I can think of something more elegent or we change the database system to one that supports a better syntax for this kind of procedure!
    Last edited by michael.appleton; 12-30-08 at 18:53.

  15. #15
    Join Date
    May 2008
    Posts
    33
    Double Post
    Last edited by michael.appleton; 12-30-08 at 18:53.

Posting Permissions

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