Results 1 to 4 of 4

Thread: Multiple INSERT

  1. #1
    Join Date
    Apr 2003
    Posts
    7

    Unhappy Unanswered: Multiple INSERT (two inserts in one)

    I have been using a Select and Insert into statement,

    INSERT INTO DetailsData(DetailsData_FundID, DetailsData_FundCountryID, DetailsData_FieldID, DetailsData_Data)
    SELECT @NewFund_ID, @NewFund_CountryID, DetailsData_FieldID, DetailsData_Data FROM DetailsData WHERE
    DetailsData_FieldID in (311, 814, 819, 820, 821, 822, 823, 824, 830, 831, 832, 833, 834, 826, 825, 841, 840, 843, 842, 813, 847, 848)
    AND DetailsData_FundID = @Fund_ID
    AND DetailsData_FundCountryID = @Fund_CountryID


    that inserts the result set of a Select statement into a table. In our database for each transaction performed a record is kept

    e.g.
    INSERT INTO TranDetailsData VALUES (1, GETDATE(), NULL, 1, @NewFund_ID, @NewFund_CountryID, 845, @GroupId);

    Is it possble to perform them both in one statement?
    Last edited by thomaskeegan; 02-09-04 at 09:37.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no because they insert into different tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2002
    Posts
    134

    Re: Multiple INSERT (two inserts in one)

    1. use trigger on insert into DetailsData for the second insert (i would use this approach if you must keep track of inserts).
    2. there is a special sql code constructions in the latest version of db2 udb, but it seems like you are using ms sql server. may be ms sql server has someting similar, ask in corresponding group.
    3. why do you need one statement, the same transaction for both sqls should be ok (either both inserts go in or both are rejected).

    regards,
    dmitri

  4. #4
    Join Date
    Apr 2003
    Posts
    7
    well thanks for your suggestions.

    I worked it out like this by using the data type table to store the data from the selectect statement.

    DECLARE @tblDetailData TABLE(DetailsData_FundID int, DetailsData_FundCountryID varchar(3), DetailsData_FieldID int, DetailsData_Data varchar(512))

    INSERT @tblDetailData SELECT @NewFund_ID, @NewFund_CountryID, DetailsData_FieldID, DetailsData_Data
    FROM DetailsData WHERE DetailsData_FieldID IN (311, 814, 819, 820, 821, 822, 823, 824, 830, 831, 832, 833, 834, 826, 825, 841, 840, 843, 842, 813, 847, 848)
    AND DetailsData_FundID = @Fund_ID
    AND DetailsData_FundCountryID = @Fund_CountryID

    INSERT INTO DetailsData(DetailsData_FundID, DetailsData_FundCountryID, DetailsData_FieldID, DetailsData_Data) SELECT * FROM @tblDetailData

    INSERT INTO TranDetailsData(TranSessionID, TranTime, TranSequence, TranType, DetailsData_FundID, DetailsData_FundCountryID, DetailsData_FieldID, DetailsData_Data) SELECT 1, GETDATE(), NULL, 1, * FROM @tblDetailData


    chuzhoi
    "3. why do you need one statement, the same transaction for both sqls should be ok (either both inserts go in or both are rejected)."

    I kinda guessed performing the statement twice would be an in efficient way of going about it.

Posting Permissions

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