Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: multiple inserts

    Hi all,

    I am using a mulitiple insert statement in a stored procedure wrapped up in a transaction. Supposing I am inserting in 3-4 tables, how will I get get each last inserted record identities? thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you're on 2005 or later you can use the OUTPUT clause of a query.
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    yes george I am on 2005. Can you please give me an example as am pretty new to this sql programming. Are you saying declare some OUTPUT variables and then assign, say, IDENTITIES() to those variables each time an insert is done? Thanks

  4. #4
    Join Date
    Jul 2009
    Posts
    168
    Code:
    ALTER PROCEDURE dbo.StoredProcedure1 
    	
    	(
    	@firstNames varchar(50),
    	@surname varchar(30),
    	@postalAddress nvarchar(100),
    	@residentialAddress nvarchar(100),
    	@registrationDate datetime,
    	@title char(5),
    	@nationality varchar(20),
    	@idNumber nvarchar(50),
    	@dateOfBirth datetime,
    	@studentID int output,
    	@addressID int output
    	)
    	
    AS
    begin try
    		begin transaction
    	/* SET NOCOUNT ON */ 
    	insert into Student(firstName,surname,title,nationality,idNumber,dateOfBirth)
    	values (@firstNames,@surname,@title,@nationality,@idNumber,@dateOfBirth)
    	set @studentID = (select @@identity) 
    	insert into address(address)
    	values (@postalAddress)
    	/* set @addressID = SCOPE_IDENTITY() */
    	commit
    end try
    begin catch
    		if @@trancount > 0 rollback
    		DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
    		SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
    		RAISERROR(@ErrMsg, @ErrSeverity, 1)
    	END CATCH
    
    	
    return @studentID
    I have posted the stored procedure that I try using but its giving me errors. I want to retrieve the identities newly created from both tables or even more later. Can somebody help?

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    ok dont worry guys! i got the silly mistake after 2-3 hours of exhaustion. Am using VWD 2005 and I forgot to refresh the parameters list in the wizard so the returned output parameters could not be found. Thanks guys.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE TABLE destination_1 (
       a int identity(1,1) PRIMARY KEY
     , b char(1)
    )
    
    CREATE TABLE destination_2 (
       a int identity(1,1) PRIMARY KEY
     , b char(1)
    )
    
    CREATE TABLE destination_3 (
       a int identity(1,1) PRIMARY KEY
     , b char(1)
    )
    
    
    
    DECLARE @inserted table (
       source   sysname
     , pk_value int
    )
    
    BEGIN TRAN
    
      INSERT INTO destination_1 (b)
      OUTPUT 'destination_1', inserted.a INTO @inserted (source, pk_value)
      VALUES ('A')
      
      INSERT INTO destination_2 (b)
      OUTPUT 'destination_2', inserted.a INTO @inserted (source, pk_value)
      VALUES ('B')
           , ('C')
           , ('D')
      
      INSERT INTO destination_3 (b)
      OUTPUT 'destination_3', inserted.a INTO @inserted (source, pk_value)
      VALUES ('E')
    
      SELECT source
           , pk_value
      FROM   @inserted
    
    COMMIT TRAN
    
    GO
    DROP TABLE destination_1
    DROP TABLE destination_2
    DROP TABLE destination_3
    George
    Home | Blog

Posting Permissions

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