October 13, 2016 at 9:23 am
Hi All,
I would like to set a flag in source table, when a record is inserted a target table.
Example:
Source table:
Name company flag
AAA XXXX 0
BBB YYYY 0
Once the target table has data
ID name company
1 AAA XXXX
Source table :
Name company flag
AAA XXXX 1
Basically, I want to check if ID has been generated in target table, If it is genereated then make flag 1 in source.
I can do this in sql or in execute sql task. anything is fine.
How can I do this?
any help is appreciated.
October 13, 2016 at 9:48 am
Are the two tables in the same database? If so, have you considered putting the INSERT and UPDATE statements in a transaction?
John
October 13, 2016 at 10:00 am
They are in different database.
Basically, I am looping the files in SSIS and loading each file in target table. so each time I loop the file and load in target If I can check ID generation in Target and set the source flag=1 it would be easier. Can we do something like this?
October 14, 2016 at 2:13 am
You haven't provided much detail, but would something like this work?SET XACT_ABORT ON -- either use this setting or handle errors for the INSERT in your code
BEGIN TRAN
INSERT INTO DB2.dbo.TargetTable (
Name
,Company
)
SELECT
Name
,Company
FROM DB1.dbo.SourceTable
WHERE <Your WHERE condition here>;
UPDATE DB1.dbo.SourceTable
SET Flag = 1
WHERE <Same WHERE condition here>;
END TRAN
You say these are files - are you actually doing any processing on the files themselves, or just the INSERTs and UPDATEs in the tables? If there's no processing of the files, there's no need to use a loop: you can do all the work with a single INSERT and a single UPDATE.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply