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?

no because they insert into different tables

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

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.