none
Get the id of inserted rows --> insert into table1 select * from table 2 ?

    Question

  •  

    hi

     

    I want to do a "bulk" insert and then get the id (primary key) of the inseret rows:

     

    insert into table1 select * from table2

     

    If I get the value of the @@identity, I always get the last inserted record.

     

    Any idea how to get the ids of all inserted values?

    Thx

     

    Olivier

    Thursday, April 17, 2008 3:04 PM

Answers

  • Why are you using a cursor to accomplish this?  A cursor is not scalable and can be EXTREMELY slow because it proceses data one row at a time.

     

    Why not do something like this?

    Code Snippet

     

    insert into destination (filename,hashcode,directory)

    select [filename],[hashcode],[directory]

    from source

    where not exists (select 1

       from destination

       where destination.filename + destination.hashcode =

        source.Filename + source.hashcode)

     

     

    Thursday, April 24, 2008 1:28 PM
    Moderator

All replies

  • I'm sure that there's a cleaner way, but you could always to an AFTER INSERT trigger and write the identity value from the INSERTED rows back to Table2. You'd need to create a field in Table2 to hold that value (if one doesn't already exist).

     

    Code Snippet

    CREATE TRIGGER MyTrigger ON Table1

    AFTER INSERT

    AS

    BEGIN

    UPDATE Table2

    SET T1Identity = INSERTED.IdentityColumn

    FROM Table2

    JOIN INSERTED ON Table2.SomeField = INSERTED.SomeOtherField

    END

     

     

    Thursday, April 17, 2008 3:14 PM
    Moderator
  • Use the output clause for you insert.

     

    Code Snippet

    create table #test(

    id int identity not null primary key,

    db varchar(50) not null unique

    )

    create table #new ( id int not null, db varchar(50) not null)

    insert into #test (db)

    --use output here and get the values you need

    --and put them into a temp table

    output inserted.id, inserted.db into #new

    select name from sys.databases

    select * from #new

    drop table #new, #test;

    go

     

     

    Thursday, April 17, 2008 3:26 PM
    Moderator
  • Perhaps the OUTPUT clause of INSERT can help you: http://msdn2.microsoft.com/en-us/library/ms177564.aspx

     

    Code Snippet

    use tempdb;

    go

    create table test (a int identity(1,1), b nvarchar(4000));

    go

    insert into test (b)

    output inserted.a, inserted.b

    select 'a' union all select 'b' union all select 'c'

     

     

    Thursday, April 17, 2008 3:30 PM
  •  Adam Haines wrote:

    Use the output clause for you insert.

     

    lol, beat my reply to the minute

    Thursday, April 17, 2008 3:34 PM
  • hi

     

    thanks of all of you.

     

    I use SQL 2000, that output doesn't work (or do I do something wrong?).

     

     

    again:

     

    insert into table1
    select * from table2

     

    In table 1 I have an id which is unique and automatically increasing in value:

    after inserting I need the ID that I will put in another table.

     

     

    Thursday, April 17, 2008 3:34 PM
  • Output is new to sql 2005. 

     

    If you always need to write to table 2, after an insert of table 1, you should considering using a after insert trigger.  You can still perform bulk loads, within a trigger and you will have access to the inserted table via a trigger.

     

    Thursday, April 17, 2008 3:38 PM
    Moderator
  • what is INSERTED here?

    Thursday, April 17, 2008 3:48 PM
  • Sorry, for being vague.  When you use a trigger you have the ability to query a table called inserted.  The table contains all the values that are being inserted into the table.  You can take the inserted id and perform an insert into table2 select with the ids retrieved from table1 insert.

     

    You should read through this at the below link.  They also have good examples of how to implement and use triggers.

    http://msdn2.microsoft.com/en-us/library/ms189799.aspx

    Thursday, April 17, 2008 4:01 PM
    Moderator
  •  

    good morning

     

    you all helped me a lot.

     

    The trigger is a great idea!

     

    one more question is: how can i change the scope of my variable? I want to save the id of an inserted value into a variable. I need to write an entry to a table only after having 3 values from 3 different triggers.

     

    thx

    Friday, April 18, 2008 7:42 AM
  • Just a little update:


    I tried to do my stuff with a cursor but it's toooooooooo slow.

     

    declare @filename varchar(1000)

    declare @hashcode varchar(1000)

    declare @directory varchar(1000)

     

    declare test_cursor cursor for

    select name,hashcode,directory from source

    open test_cursor

    fetch next from test_cursor into @name,@hashcode,@directory

    while @@fetch_status = 0

    begin

    insert into destination (filename,hashcode,directory)

    select @filename,@hashcode,@directory

    where @filename+@hashcode not in (select filename+hashcode from destination)

    fetch next from test_cursor into @filename,@hashcode,@directory

    end

     

     

    Any Idee to improve the speed?

    Thursday, April 24, 2008 1:17 PM
  • Why are you using a cursor to accomplish this?  A cursor is not scalable and can be EXTREMELY slow because it proceses data one row at a time.

     

    Why not do something like this?

    Code Snippet

     

    insert into destination (filename,hashcode,directory)

    select [filename],[hashcode],[directory]

    from source

    where not exists (select 1

       from destination

       where destination.filename + destination.hashcode =

        source.Filename + source.hashcode)

     

     

    Thursday, April 24, 2008 1:28 PM
    Moderator
  • Great, that's it!

    Thx a lot

     

    cheers

     

    Thursday, April 24, 2008 2:35 PM