Insert into Multiple Tables

Question

Post reply

Insert into Multiple Tables

allan.kendall

SSC Enthusiast

Points: 114
More actions
February 9, 2010 at 6:35 am

#138734

Hello Everyone,
I am looking for some help with inserting data that is input into a single text box on my ASP.NET web form into two different tables within the same database. Naturally this is more of a SQL question than an ASP question so I was hoping to get some help here.
It was suggested to me to use TRANSACTION or Stored Procedure and I was wondering if anyone could help me with that?
I simply have a single text box that the user puts a value into, but I was the data from that text box to be inserted into two different tables.
Cheers

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

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

You have to use both TRANSACTION and STORED PROCEDURES to accomplish the task.

Here is a sample procedure that inserts a value in 2 different tables.

CREATE PROCEDURE dbo.usp_SaveUserValue

(

@UserValue VARCHAR(100)

)

AS

BEGIN

SET NOCOUNT ON

SET XACT_ABORT ON --This will ensure the auto rollback of transaction in case of any failure within the transaction

BEGIN TRANSACTION

INSERTdbo.Table1( UserValue ) VALUES( @UserValue )

INSERTdbo.Table2( UserValue ) VALUES( @UserValue )

COMMIT TRANSACTION

END

Here is the code to execute the procedure

EXECUTE dbo.usp_SaveUserValue 'Some Value'

For more information, look for stored procedures in book online.

--Ramesh

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

You can only insert into one table at a time as far as the TSQL commands are concerned. But as the last post showed, if you wrap these inserts in a transaction, it functions as a single event. Stored procedures, which you should use, are a different critter and unrelated to the question.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Paarthasarathy SSChasing Mays Points: 649 More actions · Answer 3

I have an idea.. but not sure how this will workout...

u can write a trigger on one of the tables, so that on update\insert\delete on that table , the other table is also update\insert\delete'd..

Let me know ur findings!!

Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com

mike 84768 Right there with Babe Points: 792 More actions · Answer 4

Maybe this is outside of your control...but..from a data normalization standpoint..particularly with text data types which typically consume considerable space...why are you writing the data in two places? That is not usually considered good form.

Uripedes Pants Hall of Fame Points: 3701 More actions · Answer 5

allowing a user to insert free form text is generally not a good idea. From the original post it sounds like you also want to parse out the string. That could be really buggy with user inputed text. Check BOL for string functions. Additionally MY preference would be to do data validation in your application prior to trying any insert at the DB level.