Trying to avoid cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to avoid cursor

Author

Topic

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2010-07-08 : 12:47:32

I am doing a bulk insert. I need to insert data into two tables, Transactions and TransactionSet. Transactions is a child of TransactionSet.

The problem: I need to insert rows into both tables at the same time, yet somehow also insert the identity-generated key from TransactionSet into Transactions.

INSERT INTO Transactions(TransactionSetId)
VALUES(INSERT INTO TransactionSet and return TransactionSetId)

As currently written, the ETL package takes about an hour to run. Other than the current problem, when I run the package as a bulk insert and then parse out the data once it's in the new database, it runs in 22 seconds.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

Sachin.Nand

2937 Posts

Posted - 2010-07-08 : 15:15:07

Well you can use the output clause.Something like this



Insert Transactions output Inserted.id,Inserted.col1,Inserted.col2 into TransactionSet values(val1,val2)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2010-07-09 : 08:52:44

Thanks for the tip. Was reading up on the output clause in BOL, and I have another question. If I join the OUTPUT from the insert into the first table with data in another table for the insert into the second table, will that insert a row in the first table for every row inserted into the second table? As I said before, I have multiple rows that need to be inserted.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2010-07-09 : 09:41:06

OK, after playing with the OUTPUT clause, I got this to work:

CREATE TABLE testOne (
testOneId INT IDENTITY(1,1)
,TransactionSetTypeId INT
)
go

CREATE TABLE testTwo (
testTwoId INT IDENTITY(1,1)
,testValue INT
)
go

CREATE TABLE testThree (
testThreeId INT IDENTITY(1,1)
,testOneId INT
,testTwoId INT
)
go

CREATE TABLE testFour(
testFourId INT IDENTITY(1,1)
,Code varchar(25)
)

INSERT INTO testTwo(testValue)VALUES(1)
INSERT INTO testTwo(testValue)VALUES(2)
INSERT INTO testTwo(testValue)VALUES(3)
INSERT INTO testTwo(testValue)VALUES(4)
INSERT INTO testTwo(testValue)VALUES(5)
go

INSERT INTO testFour(Code)
VALUES('Initial Deposit')
go

INSERT INTO testOne(TransactionSetTypeId)
OUTPUT inserted.testOneId
INTO testThree(testOneId)
SELECT x.testFourId
FROM (SELECT tf.testFourId
FROM testFour tf
WHERE Code='Initial Deposit') x
JOIN testTwo tt ON tt.testTwoId > 0

This inserts 5 rows into testOne. However, I ALSO need to insert 5 rows into testThree at the same time. Something like this:

INSERT INTO testOne(TransactionSetTypeId)
OUTPUT inserted.testOneId
,tt.testTwoId
INTO testThree(testOneId,testTwoId)
SELECT x.testFourId,tt.testTwoId
FROM (SELECT tf.testFourId
FROM testFour tf
WHERE Code='Initial Deposit') x
JOIN testTwo tt ON tt.testTwoId > 0

But that fails. Any ideas?

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2010-07-09 : 15:02:22

OK, I figured it out.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-07-09 : 15:13:15

quote:
If I join the OUTPUT from the insert into the first table with data in another table for the insert into the second table, will that insert a row in the first table for every row inserted into the second table?

I'm thinking of making this my part of my signature.

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-09 : 15:19:47

".....the ETL package...."

Oh the young....

Create 2 sprocs...set them up in jobs...launch them async...and DON'T EVER SAY ETL AGAIN

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-09 : 15:45:42

...and besides cursors..avoid clamidia

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Sachin.Nand

2937 Posts

Posted - 2010-07-11 : 05:12:49

quote:
Originally posted by stonebreaker

OK, I figured it out.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

will be helpful if you post the solution.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources