none
SQL QUERY PROBLEM RRS feed

  • Question

  • I  HAVE 2 TABLES. I NEED TO INSERT VALUES INTO TWO TABLES ON THE SAME TIME.

    AFTER INSERTING IN Recept_Header TABLE THE OTHER DATA NEED TO BE INSERTED INTO

    "RECEIPT_DETAILS" TABLE.."Recept_Header" TABLE CONTAINES "RECEIPT_ID" (PRIMARY KEY AND SET AS AUTOINCREMENT). THE RECEPT_DETAILS TABLE ALSO CONTAIN "RECEIPT_ID"COLUMN . HOW CAN I INSERT DATA IN TO BOTH TABLES. 

    I WROTE A SP FOR INSERTING ..

     -----------------------------------------------------------------
    ALTER PROC [dbo].[Receipt_ReceiptInsert]

    @Rcptseries varchar(50),
    @RcptDocId int

    AS
    BEGIN
    INSERT INTO [Scheme_MS].[dbo].[Recept_Header]
               ([series]
               ,[docNo]
             )
           VALUES
    (
    @Rcptseries ,
    @RcptDocId 
    )
    END

    -------------------------------------------------------------------------.

    Monday, September 10, 2012 6:42 AM

Answers

  • You can use BEGIN TRAN   and perform two INSERT commands

    ALTER PROC [dbo].[Receipt_ReceiptInsert]

    @Rcptseries varchar(50),
    @RcptDocId int

    AS
    BEGIN TRAN 
    INSERT INTO [Scheme_MS].[dbo].[Recept_Header]
               ([series]
               ,[docNo]
             )
           VALUES
    (
    @Rcptseries ,
    @RcptDocId 
    )

    INSERT INTO [Scheme_MS].[dbo].[RECEIPT_DETAILS]
               ([...]
               ,[...]
             )
           VALUES
    (
    @.....
    @.....
    )
    COMMIT TRAN


    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
    MS SQL Consultants: Improves MS SQL Database Performance

    Monday, September 10, 2012 6:50 AM
    Moderator