Multiple inserts with ref. integrity within a transaction

Question

Multiple inserts with ref. integrity within a transaction

kazim.raza

SSCrazy

Points: 2390
More actions
October 11, 2011 at 11:46 am

#260264

Good day all;
I have to insert data periodically which will effect multiple tables. These tables include one master table while others are child tables with primary key of master table within them. How can I make inserts into all of them while having them within one or more transactions so in case of an exception I can roll them back?
Regards,
Kazim

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

kazim.raza (10/11/2011)
Good day all;
How can I make inserts into all of them while having them within one or more transactions so in case of an exception I can roll them back?
Regards,
Kazim

sounds like you need to declare an explicit transaction, and to use the OUTPUT clause to capture the new values inserted into the main table, so you can isnert them into a child table(s).

with more details we could offer some 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!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

As Lowell mentioned, you need an explicit transaction. Start with BEGIN TRANSACTION in Books Online to get an idea of what he's referring to. OUTPUT clause will help with ID re-usage, but if it's a single row in the primary table you'll be just as good using SCOPE_INDENTITY(). If it's multi-row OUTPUT is your friend.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

kazim.raza SSCrazy Points: 2390 More actions · Answer 3

Scope_Identity() did the trick for me, I am wondering to myself how come I missed it..

Regards,

Kazim Raza

Rich Mechaber SSChampion Points: 11130 More actions · Answer 4

As Lowell and Craig both mentioned, you must differentiate between INSERTs involving a single row (OK to use SCOPE_IDENTITY) and those involving multiple rows (cannot use SCOPE_IDENTITY). SCOPE_IDENTITY will only return one value, so you would lose the results of a multi-row INSERT.

Here's a generic example query I keep for setting up the trapping of multi-row INSERTS using the OUTPUT clause. I can never remember the syntax of these things that I use infrequently, so it's handy for me to have this:

--Example of using OUTPUT clause to snag the input autonumber IDs for a large INPUT statement

--From RBarry Young at http://www.sqlservercentral.com/Forums/Topic632792-145-1.aspx

--Slightly modified to trap more than one field to the table variable, @IDList

USE dev;

GO

--====== Make our test table to insert to

Create Table InsertIDTst(

ID int identity primary key

, ColName nvarchar(255)

, object_id int);

GO

--====== Make a table variable to hold the new ID's [and column names]

Declare @IDList Table(ID int, c_name VARCHAR(100));

--====== Insert a bunch of rows,

-- and save the new IDs at the same time

INSERT INTO InsertIDTst

(ColName, object_id)

OUTPUT Inserted.ID, Inserted.ColName Into @IDList(ID, c_name)

SELECT name, object_id

FROM sys.columns

--====== Show that we have the new IDs

SELECT * from @IDList

--=====Cleanup

DROP TABLE InsertIDTst

HTH,

Rich

kazim.raza SSCrazy Points: 2390 More actions · Answer 5

Thanks for the template, Rich. This would be very useful for me, specially, since I tend to forget the syntax.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 6

Great, glad to pass on good stuff I've found useful!

Rich