none
how can i get the id's of the inserted row??? + stored procedure

    Question

  • how can i get the id's of the inserted row???  in stored procedure......

    Table1 has many ids and description. ids in Table1 are autoincrement. I am inserting 10 descriptions to table1. I want to get corresponding ids of the 10 rows i inserted. I am using table variable to store all the 10 ids, cursor to loop through to get each and every id with the help of @@identity. but I am only able to view last inserted id. Can somebody say how can i view all ids of the 10 rows i inserted? so that i can store into the table variable.

    CREATE PROCEDURE test_abc
    AS
    DECLARE @cx_id TABLE

    (
     Customer_ID int
    )
    DECLARE @custid INT
    DECLARE @descript varchar(255)
    BEGIN
    INSERT INTO OPENQUERY (mysql, 'SELECT description from table1') select result from table0
    DECLARE firstcurse1 CURSOR for SELECT  id, description from table1');
    open firstcurse1
    fetch firstcurse1 into @custid, @descript
    INSERT INTO @cx_id (Customer_ID) select @custid
    insert into openquery (mysql, 'select mandu from testid') select Customer_ID from @cx_id
    close firstcurs1
    deallocate firstcurs1

    END

    Wednesday, February 20, 2013 6:25 AM

Answers

  • Hi KJayd,

    Actually, since the ID of Table1 is Auto Increment, you can then first get the MAX(ID) of Table1 then stored it into a variable. Then after you inserted the 10 records descriptions, you can now get the newly inserted records using > and < argument. 

    Say: 

    Declare @cur_id int

    set @cur_id = OPENQUERY (mysql, 'SELECT description from table1')  --i don't know if this works; but the logic is here.

    --copying the text of Uri. just add some arguments

    DECLARE @cx_id TABLE

    (
     Customer_ID int
    )

    INSERT INTO @cx_id SELECT  Customer_ID  FROM OPENQUERY (mysql, 'SELECT description from table1')  WHERE ID > @cur_id -- you can add < argument here if you want to add something.



    What worth the price is always worth the fight. --Silvers R.


    Wednesday, February 20, 2013 11:43 AM

All replies

  • DECLARE @cx_id TABLE

    (
     Customer_ID int
    )

    INSERT INTO @cx_id SELECT  Customer_ID  FROM OPENQUERY (mysql, 'SELECT description from table1') 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, February 20, 2013 6:31 AM
  • Hi KJayd,

    Actually, since the ID of Table1 is Auto Increment, you can then first get the MAX(ID) of Table1 then stored it into a variable. Then after you inserted the 10 records descriptions, you can now get the newly inserted records using > and < argument. 

    Say: 

    Declare @cur_id int

    set @cur_id = OPENQUERY (mysql, 'SELECT description from table1')  --i don't know if this works; but the logic is here.

    --copying the text of Uri. just add some arguments

    DECLARE @cx_id TABLE

    (
     Customer_ID int
    )

    INSERT INTO @cx_id SELECT  Customer_ID  FROM OPENQUERY (mysql, 'SELECT description from table1')  WHERE ID > @cur_id -- you can add < argument here if you want to add something.



    What worth the price is always worth the fight. --Silvers R.


    Wednesday, February 20, 2013 11:43 AM