Need Help writing SP to insert into two tables at the same time

  • Hello,

    My name is Greg. I am new to T-SQL as our SQL guru quit for a better job two week ago and I am trying to pick up the pieces until we can find another one. I am an ASP.NET developer and am trying to create a stored procedure that will allow me to insert information into two tables at the same time. Table one has account information (CustInfo) and table 2 has telephone numbers that I could assign to these customers (DIDs).

    CustInfo (Table 1)

    CustID int(identity is yes and this is the PK for this table)

    C_Namevarchar(50)Customer Name

    Etc

    Etc

    RoutingDID

    Etc

    Etc

    DIDs (Table2)

    DID_ID(identity is yes and this is the PK for this table)

    CustIDint(FK)

    DIDvarchar(50)

    Assignedvarchar(50)Needs to be the RoutingDID from CustInfo(Table1)

    Her e is my Stored Procedure, which works for inserting all the info into CustInfo(Table 1) but I am trying to add code that will allow me to insert the RoutingDID value into the DIDs table2 at the same time.

    CREATE PROCEDURE [dbo].[usp_NewCustomer]

    @C_Name varchar(50),

    @C_Address1 varchar(50),

    @C_Address2 varchar(50),

    @C_City varchar(50),

    @C_State varchar(50),

    @C_Zip varchar(50),

    @C_DID varchar(50),

    @C_email varchar(50),

    @T_Name varchar(50),

    @T_DID varchar(50),

    @T_email varchar(50),

    @MACAddr varchar(50),

    @RoutingDID varchar(50),

    @DID1 varchar(50),

    @DID2 varchar(50),

    @DID3 varchar(50),

    @DID4 varchar(50),

    @DID5 varchar(50),

    @DID6 varchar(50),

    @DID7 varchar(50),

    @DID8 varchar(50),

    @DID9 varchar(50),

    @DID10 varchar(50),

    @PWD varchar(50),

    @CallPaths varchar(50),

    @CallerID varchar(50)

    AS

    INSERT INTO CustInfo

    (C_Name,

    C_Address1,

    RoutingDID,

    PWD,

    CallPaths,

    CallerID)

    VALUES

    (@C_Name,

    @C_Address1,

    @RoutingDID,

    @PWD,

    @CallPaths,

    @CallerID)

    GO

    INSERT INTO DIDs

    (Assigned)

    Values

    (RoutingDID)

    WHERE CustID =@CustID

    Thank to everyone who assists me.

    Greg

  • Hello Greg,

    What are all of the columns that you want to populate in the DIDs table, and which parmeters do you want to assign to the columns in the DIDs table.

    Thx,

    Mike

  • Hello Greg,

    What are all of the columns that you want to populate in the DIDs table, and which parameters do you want to assign to the columns in the DIDs table.

    Thx,

    Mike

  • Hi Greg

    You have to use the SCOPE_IDENTITY() function to get the currently inserted/created CustID. Then you can insert the data into your DID table.

    USE tempdb

    GO

    IF (OBJECT_ID('Did') IS NOT NULL)

    DROP TABLE Did

    GO

    IF (OBJECT_ID('Cust') IS NOT NULL)

    DROP TABLE Cust

    GO

    CREATE TABLE Cust

    (

    Id INT IDENTITY,

    Name VARCHAR(100),

    RoutingDid VARCHAR(100),

    PRIMARY KEY CLUSTERED (Id)

    )

    GO

    CREATE TABLE Did

    (

    Id INT IDENTITY,

    DID VARCHAR(100),

    CustId INT,

    PRIMARY KEY CLUSTERED (Did),

    FOREIGN KEY (CustId) REFERENCES Cust (Id)

    )

    GO

    IF (OBJECT_ID('usp_Cust_Insert') IS NOT NULL)

    DROP PROCEDURE usp_Cust_Insert

    GO

    CREATE PROCEDURE usp_Cust_Insert

    @Name VARCHAR(100),

    @RoutingDID VARCHAR(100),

    @Did1 VARCHAR(100),

    @Did2 VARCHAR(100)

    AS

    DECLARE @CustId INT

    INSERT INTO Cust

    SELECT @Name, @RoutingDID

    SELECT @CustId = SCOPE_IDENTITY()

    INSERT INTO Did

    SELECT @RoutingDID, @CustId

    UNION SELECT @Did1, @CustId

    UNION SELECT @Did2, @CustId

    GO

    EXECUTE usp_Cust_Insert 'MyCust', 'RoutingDID', 'DID1', 'DID2'

    SELECT * FROM Cust

    SELECT * FROM Did

    Greets

    Flo

  • Hi

    In addition to the "Florian Reischl" store procedure its good to use TRANSACTIONS also.

    If one INSERT fails then dont proceed with the next INSERT.

    Thanks -- Vijaya Kadiyala

    http://www.dotnetvj.vom

  • Hi,

    I just want to insert a 'Yes' in the Assigned Column of the DIDs table.

  • gsmccoy (4/1/2009)


    Hi,

    I just want to insert a 'Yes' in the Assigned Column of the DIDs table.

    Into your CustInfo table or the DIDs table? What is the problem?

    Greets

    Flo

  • Vijaya Kadiyala (4/1/2009)


    Hi

    In addition to the "Florian Reischl" store procedure its good to use TRANSACTIONS also.

    If one INSERT fails then dont proceed with the next INSERT.

    Hi Vijaya

    Sure it should be included into a TRY-CATCH block with transaction handling. Was just a sample 😉

    Greets

    Flo

  • Hi Flo

    Thanks for adding Try and Catch 😉 ..I hope from next time onwrods i will Catch my posting exception 😉

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply