How can I perform multiple inserts? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

How can I perform multiple inserts?

Author

Topic

midi25
Starting Member

24 Posts

Posted - 2005-01-31 : 17:38:55

Hi I have two data tables and I want to insert into them both. They are related and cascading updates is set.

One is a customers table that stores cutomers details and another is an address table. What I want to be able to do is populate both rows using one sp.

This code inserts into the cutomer table fine. But I need a way to insert the related address infomation.

Insert Into Customers
(FirstName,LastName,Sex,Age)
VALUES ('lee','dee','male','29')

I want to be able to say. Insert Into customers their first name,last name,sex,age. And insert thier corresponding house number, street name, city, postcode into the address table.

Has anyone got an idea how to do this. Thanks

chadmat
The Chadinator

1974 Posts

Posted - 2005-01-31 : 17:50:59

I assume there is some primary key on this table? Presumably an identity column?


CREATE PROC myProc(...)
AS
DECLARE @CustID INT

Insert Into Customers 
(FirstName,LastName,Sex,Age)
VALUES ('lee','dee','male','29')

SET @CustID = Scope_Identity()

INSERT INTO Address
Values(...)

Does that get you started?

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

midi25
Starting Member

24 Posts

Posted - 2005-01-31 : 18:05:34

Customers has a

Pk Cust_ID

Address table has

PK Adr_ID
FK Cust_ID

The only problem is that I have to hand code the FK Cust_ID every time to keep a tally with the PK it refers too.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-31 : 18:31:35

What Chad has shown you is the use of SCOPE_IDENTITY() which will keep track of the PK (in this case Cust_ID) allowing you to perform the insert into thre address table using a local variable..

Does that make sense?

midi25
Starting Member

24 Posts

Posted - 2005-02-01 : 07:38:26

Thanks yes. Nice way of doing it too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources