insert into multiple tables from single insert

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 2005 Forums

Transact-SQL (2005)

insert into multiple tables from single insert

Author

Topic

Ronesh
Starting Member

33 Posts

Posted - 2009-09-03 : 09:18:06

Dear all,

I have 3 tables as under

CustomerTable(ClientId,Name,Address)
CustomerDetailTable(Id,ClientId,Phone,EmailAddress)
CustomerExtraDetailTable(Id,ClientId,FatherName,MotherName,SpouseName)

The CustomerTable is Master Table and Other two are child tables.
The ClientId is Identity Column.

And now

I would like to insert the Customer Information.
The tables CustomerDetailTable or CustomerExtraDetail may contain multiple rows with same ClientId(for multiple Phone and EmailAddress and so on).

The above information regarding Customer is filled from single form.(Client Entry form)

How do i do this?

Also if while entering the data,the insert statement need to be rollback if power goes off or link is down;something like that before the whole information is entered.

Looking forward for postive response. As usual.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-09-03 : 10:09:24

Write a stored procedure to insert the records.

An infinite universe is the ultimate cartesian product.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-03 : 10:53:05

Stored Proc + explicit transaction seems about right.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Ronesh
Starting Member

33 Posts

Posted - 2009-09-04 : 01:13:22

Dear Jesus,
Thank you for your suggestion.
I
have created a stored procedure to do the insert as under.

CREATE PROCEDURE s_Test
@Name VARCHAR(20),
@Address VARCHAR(50),
@Phone int,
@Email VARCHAR(25),
@Father VARCHAR(25),
@Mother VARCHAR(25),
@Spouse VARCHAR(25)
AS
SET XACT_ABORT ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

BEGIN TRANSACTION

DECLARE @DataID INT

INSERT INTO Test1(Name,Address)
VALUES (@Name,@Address)

SELECT @DataID = scope_identity()

INSERT INTO Test2
VALUES (@Phone,@Email,@DataID)

INSERT INTO Test3
VALUES (@Father,@Mother,@Spouse,@DataID)

COMMIT
GO

Now,
even if i run
EXEC s_Test 'test','ADDRESS2','adfdf','EMAIL2@EMAIL','FATHERENAME2',
'MOTHERNAME2','SPOUSENAME'

Here i had passed @Phone as 'adfdf' and it accepts.
This should not happen. Isn't so?
Also i would like to know that this is not the always case.
i mean the table may be different and the data to be entered also different. what to do in that case?
should i have to write stored procedure for each table whenever the entry form has to insert into multiple talbes.
i dont think so it is a good idea.
the solution must be general.i mean it should be applied to any number of tablse with different columns.
the procedure above is specific to only that very tables only.
so please help me.
Looking forward for the positive response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources