none
having an insert that runs in sections

    Question

  • Hello.  I am not the regular dba that does the stored procs in the db (not my forte).  But that person is out for while so I'm left to do the developing and the db work for the project.   My question stems around a transact sql statement that is an insert.  But it would be in sections.  Too explain, we are looking at having a form that has comboboxes, textboxes, etc for entering information.  Also on the form is a tab control.  You might see where this is headed.  The form is the one while the tab control is the many.  If adding a new record, the tab control many can not be saved until the form record is saved and the PK returned to the form so the tab control many has the form PK to add to their FK field when saving.  Currently, the tab control will have 3 tabs which is 3 tables in the db.  All the saving is to be done with the click of one button.  Hope this is clear enough explaining this. 

    First, is saving in pieces like this possible?

    If yes, how does begin?  Is there something that goes before the INSERT statement?  If so, can you provide an example?  The INSERT I can do.  What is used after the insert to get the PK back to the form?  Can an example be provided?  Then the tab control info inserts would be done one after the other, correct?  If correct, then thanks.

    Thanks for any info.

    ...John

    Saturday, December 21, 2013 7:56 PM

Answers

  • First, is saving in pieces like this possible?

    If yes, how does begin?  Is there something that goes before the INSERT statement?  If so, can you provide an example?  The INSERT I can do.  What is used after the insert to get the PK back to the form?  Can an example be provided?  Then the tab control info inserts would be done one after the other, correct?  If correct, then thanks.

    Typically, you would have a different stored procedure for each table. Consider an example where you create a new customer along with one or more addresses (e.g. home, business, etc).  The application would insert the customer row first and use the returned IDENTITY value for the subsequent inserts into related tables.  For example:

    CREATE PROC dbo.usp_InsertCustomer
    	  @FirstName varchar(30)
    	, @LastName varchar(30)
    AS
    
    SET NOCOUNT ON;
    
    INSERT INTO dbo.Customer (
    	  FirstName
    	, LastName
    	)
    VALUES (
    	  @FirstName
    	, @LastName
    	);
    
    SELECT SCOPE_IDENTITY() AS CustomerID;
    
    GO
    
    CREATE PROC dbo.usp_InsertCustomerAddress
    	  @CustomerID int
    	, @AddressType char(1)
    	, @AddressLine1 varchar(100)
    	, @AddressLine2 varchar(100)
    	, @City varchar(50)
    	, @Province varchar(50)
    	, @PostalCode varchar(20)
    AS
    
    SET NOCOUNT ON;
    
    INSERT INTO dbo.CustomerAddress (
    	  CustomerID
    	, AddressType
    	, AddressLine1
    	, AddressLine2
    	, City
    	, Province
    	, PostalCode
    	)
    VALUES (
    	  @CustomerID
    	, @AddressType
    	, @AddressLine1
    	, @AddressLine2
    	, @City
    	, @Province
    	, @PostalCode
    );
    
    RETURN;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by johnboy0276 Saturday, December 21, 2013 11:45 PM
    Saturday, December 21, 2013 9:24 PM

All replies

  • First, is saving in pieces like this possible?

    If yes, how does begin?  Is there something that goes before the INSERT statement?  If so, can you provide an example?  The INSERT I can do.  What is used after the insert to get the PK back to the form?  Can an example be provided?  Then the tab control info inserts would be done one after the other, correct?  If correct, then thanks.

    Typically, you would have a different stored procedure for each table. Consider an example where you create a new customer along with one or more addresses (e.g. home, business, etc).  The application would insert the customer row first and use the returned IDENTITY value for the subsequent inserts into related tables.  For example:

    CREATE PROC dbo.usp_InsertCustomer
    	  @FirstName varchar(30)
    	, @LastName varchar(30)
    AS
    
    SET NOCOUNT ON;
    
    INSERT INTO dbo.Customer (
    	  FirstName
    	, LastName
    	)
    VALUES (
    	  @FirstName
    	, @LastName
    	);
    
    SELECT SCOPE_IDENTITY() AS CustomerID;
    
    GO
    
    CREATE PROC dbo.usp_InsertCustomerAddress
    	  @CustomerID int
    	, @AddressType char(1)
    	, @AddressLine1 varchar(100)
    	, @AddressLine2 varchar(100)
    	, @City varchar(50)
    	, @Province varchar(50)
    	, @PostalCode varchar(20)
    AS
    
    SET NOCOUNT ON;
    
    INSERT INTO dbo.CustomerAddress (
    	  CustomerID
    	, AddressType
    	, AddressLine1
    	, AddressLine2
    	, City
    	, Province
    	, PostalCode
    	)
    VALUES (
    	  @CustomerID
    	, @AddressType
    	, @AddressLine1
    	, @AddressLine2
    	, @City
    	, @Province
    	, @PostalCode
    );
    
    RETURN;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by johnboy0276 Saturday, December 21, 2013 11:45 PM
    Saturday, December 21, 2013 9:24 PM
  • I would suggest that the best is to send down all data at once. The main form data you send as scalar parameters, and the rows that are on the tabs, you send down in table-valued parameters. In this way you don't need to send the key back to the client.

    I have an article on my web sites that shows some examples with TVPs:
    http://www.sommarskog.se/arrays-in-sql-2008.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 21, 2013 9:25 PM
  • Mr Guzman, thanks for the reply.  Following along with your comment and example, it would be better if each action had its own stored proc that would then be called.  In your example the usp_InsertCustomerAddress is a separate stored proc would be called at the end of the usp_InsertCustomer stored proc.  I see where this is going.  This might not be as difficult as I imagined. 

    Thank you for the info and the talk thru.  It is clearer as to how to proceed.

    ...John

    Saturday, December 21, 2013 11:44 PM