insert record into 2 table - 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

SQL Server 2008 Forums

Transact-SQL (2008)

insert record into 2 table

Author

Topic

s2002
Starting Member

49 Posts

Posted - 2010-02-14 : 02:24:23

hi,
2 tables exist in DB as follow

Table "Products" contains fields as (ProductID,ProductName,Price)
Table "Product-cat" contains fields (ProductID,CategoryID)

I want to get (ProductID,ProductName,Price,CategoryID) from users and insert record into 2 tables at the same time.
But I need to make sure that inserting records to tables executes successfully for 2 tables at the same time and if Inserting record to 2nd table fails then no record is also inserted into 1st table.

Kristen
Test

22859 Posts

Posted - 2010-02-14 : 03:07:48

[code]
BEGIN TRANSACTION
DECLARE @ErrorNo int
INSERT INTO Table1 ...
SELECT @ErrorNo = @@ERROR

IF @ErrorNo = 0
BEGIN
INSERT INTO Table2 ...
SELECT @ErrorNo = @@ERROR
END

IF @ErrorNo = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
[/code]

s2002
Starting Member

49 Posts

Posted - 2010-02-14 : 03:53:55

thanks Kristen,
I would like to know Should I only use SP for this purpose or I can do this through web application.
Suppose I am using .NET.

Regards,

Kristen
Test

22859 Posts

Posted - 2010-02-14 : 06:13:20

Either will do.

My preference would be for a Stored Procedure.

If from your application send all the SQL as a single statement - if you send the BEGIN TRANS and then a second SQL call for INSERT INTO Table1 ... and then your application crashes, then you will leave the transaction open, blocking other users.

If you are using AdHoc SQL (from your application) best to use parametrised queries, or sp_ExecuteSQL, and make sure that any string concatenation etc. including data from users (such as a web form) is properly sanitised to prevent SQL Injection.

s2002
Starting Member

49 Posts

Posted - 2010-02-14 : 08:22:24

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources