Insert one row in one table and multiple rows on a second table inside transaction

Question

Post reply

Insert one row in one table and multiple rows on a second table inside transaction

Carlos Shoji-387174

SSC Enthusiast

Points: 155
More actions
September 22, 2007 at 7:48 pm

#118283

Scenario:
I have a requirement that needs to insert data from excel spreadsheet in two sql Server tables (one control table with file information) and (other data table with all related rows referent to the row in the first table.
How can I best do that?
Is it possible to do it using one stored procedure with transaction?
Any detailed ideas would be greatly appreciated.
Thanks
Carlos

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the more details you can provide, the better the answer.

here's an example, but I'm not sure how helpful this will be:

Create Procedure PR_Sample(@somedata varchar(30),@moredata varchar(30) )

As

Begin

SET XACT_ABORT ON

BEGIN TRAN --xact abort says if anything in this group fails, rollback everything.

Declare @id int -- a variable to catch the identity of the row inserted by the proc

Insert into SomeTable(SomeData,MoreData)

Values(@somedata,@moredata) --assumes an Identity column exists int he table

SET @id =Scope_Identity() --scope of your current session/connection...better than @@IDENTITY

Insert into OtherTable(SomeTableID,SomeData)

Values (@id,@somedata) --Assuming this table has a foreign key relationship to the first table

Insert into YetAnotherTable(SomeTableID,SomeData)

Values (@id,@somedata) --Assuming this table has a foreign key relationship to the first table Insert into TheFinalTable(SomeTableID,SomeData)

Values (@id,@somedata) --Assuming this table has a foreign key relationship to the first table

COMMIT TRAN

End

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Carlos Shoji-387174 SSC Enthusiast Points: 155 More actions · Answer 2

Lowell,

Thanks for your reply.

My problem is the following the first insert in the first table will have just one row for control information. (i.e. the stored procedure will be called once).

The second insert will insert many rows corresponding to the rows in the excel file. (threrefore the second stored procedure will have to be called many times).

I believe that the other way to do it, is to make the transaction outside of the stored procedures, i.e. at the .NET code level, using SQLTransaction, but I am not to familiar with these kind of implementation.

Thanks again.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

"The second insert will insert many rows corresponding to the rows in the excel file. (threrefore the second stored procedure will have to be called many times)."

Why? You can call procedure once and insert any number of rows. Calling the procedure for each row separately is one of the best ways to produce slow-running code with big potential performance problems.

Lowell SSC Guru Points: 323518 More actions · Answer 4

it sounds like you are entering some master...detail information into a pair of tables;

As Vladan identified, you should be doing this as a couple of set based operations, instead of Row-By-Agonizing-Row (RBAR).

RBAR is a method where programmers handle data when being palced into a text file; It's easy to get lockedinto this kind of logic, because it's what you know.

you should be able to insert into both tables, with just two commands, all the data from an excel sheet. below I'm assuming you are using an excel sheet as a linked server:

insert into table1 (col1,col2)
SELECT col1,col2. FROM XLTEST_DMO...Sheet1$
insert into table2(sample1ID,col1,col2
select sample1.sampleid,col3,col4 from XLTEST_DMO...Sheet1$
inner join sample1 on XLTEST_DMO...Sheet1$.col1=sample1.col1 and XLTEST_DMO...Sheet1$.col2=sample1.col2

the above is an example of how I might insert some data that was unique based on two columns int he excel spreadsheet into a table with an identity column.

if you posed the structure of the excel, as well as the structure of the two tables, we could offer better suggestions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mengus Ten Centuries Points: 1187 More actions · Answer 5

Carlos,

There are a number of ways to accomplish this. The implementation will depend on the requirements.

Options:

1. Create a DTS/SSIS package to extract, transform, and load the data into staging tables.

2. Create a .NET app to read from the Excel file, and then use Bulk-Insert to load the data into staging tables.

Note:

1. Perform any validation against the data in the staging tables.

2. Create a stored procedure to copy the data from the staging tables to the actual tables. (You can wrap the T-SQL statements inside a transaction if applicable.)

3. Again as Lowell has stated, a more detailed explanation of the requirements will help us answer and/or simplify your question.

Will the implementation be user-interactive?
If so, how do the user(s) plan to use it?
Are there any applications involved?
Is the request a one-time deal?

Regards,

Wameng Vang

MCTS

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

If you treat Excel like any other application, and you should, you'll soon realize that the best way to do this is to write a little VBS to push the data into the database... like any other app... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)