Updating 2 tables together

Question

Updating 2 tables together

Bob Baisc

SSC Enthusiast

Points: 123
More actions
August 29, 2005 at 12:33 pm

#60365

Hi,
I have a text file with 5 fields: EmpName, Age, Salary, StartDate & EndDate. I have 2 database tables: Employees & EmpInfo. In Employees I have an auto-increment int ID field, EmpName & Age. In the EmpInfo table I have an int ID field (this is not auto-increment), Salary, StartDate & EndDate.
The insert in the Employees table will have to happen first so that I can get my identity ID and then insert that in the EmpInfo table. My question is does anyone know how I can DTS that one text file into the 2 tables simultaneously and link them together with the int ID fields?
Any help is greatly appreciated.
Bob

Viewing 7 posts - 1 through 6 (of 6 total)

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

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 1

You could DTS the text data into a temp table and then use an Execute SQL Task to perform the insert into the Employees table and a second Execute SQL task to insert into the EmpInfo using a Join.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

You could also put both INSERTs in a single stored procedure or Execute SQL task. Insert the Employees table, then use the SCOPE_IDENTITY function to get the new ID value for the EmpInfo insert.

Greg

Bob Baisc SSC Enthusiast Points: 123 More actions · Answer 3

How would I call the stored procedure or Execute SQL task from within the Transform Data Task ActiveX Script. I need the syntax.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

You wouldn't call the stored procedure or Execute SQL task from the Transform Data task. Both DSP's and my solutions depend on the source data being in a SQL Server table, so you would have to use a Transform Data task to get the data from the text file into a table, then use an Execute SQL Server task to do the inserts to the Employees and EmpInfo tables. The two tasks would be connected by a "success" workflow.

Greg

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 5

Bob,

Take a look at this site for some helpful ideas and examples.

http://www.sqldts.com/

Good Luck,

Darrell

Bob Baisc SSC Enthusiast Points: 123 More actions · Answer 6

I have a Transform Data Task which works great. I also have an Execute SQL Task to insert data into the second row. How would I call this SQL Task from the Transform Data Task is my question. I have code below:

Function Main()

DTSDestination("TName") = DTSSource("Col003")

DTSGlobalVariables("test").Value = DTSSource("Col002")

DTSGlobalVariables.Parent.Steps("DTSStep_DTSExecuteSQLTask_1").Execute

Main = DTSTransformStat_OK

End Function

Whenever it his the ("DTSStep_DTSExecuteSQLTask_1").Execute the DTS hangs. My question is how do I execute this step in my Main Transform Data Task function?

Thanks for the link Darrell, that site had some good info.

Bob