Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I insert into two tables all at once in a stored procedure? [duplicate]

Possible Duplicate:
How can I INSERT data into two tables simultaneously in SQL Server?

Doing a project for school so any help would be great thank you!

I have two tables - how do I insert into two tables? So both tables are linked.

First table is called Customer with primary key called CID that auto increments

CREATE TABLE [dbo].[Customer](
    [CID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](255) NOT NULL,
    [FirstName] [varchar](255) NOT NULL,
    [MiddleName] [varchar](255) NULL,
    [EmailAddress] [varchar](255) NOT NULL,
    [PhoneNumber] [varchar](12) NOT NULL        
 CONSTRAINT [PK__CInforma__C1F8DC5968DD69DC] PRIMARY KEY CLUSTERED 
(

And a second table called Employment that has a foreign key linked to the parent table

CREATE TABLE [dbo].[Employment](
    [EID] [int] IDENTITY(1,1) NOT NULL,
    [CID] [int] NOT NULL,
    [Employer] [varchar](255) NOT NULL, 
    [Occupation] [varchar](255) NOT NULL,
    [Income] [varchar](25) NOT NULL,
    [WPhone] [varchar](12) NOT NULL,
 CONSTRAINT [PK__Employme__C190170BC7827524] PRIMARY KEY CLUSTERED 
(
like image 795
user996502 Avatar asked Nov 10 '12 03:11

user996502


1 Answers

You need to do something like this:

DECLARE @NewID INT

INSERT INTO Customer(LastName,FirstName,......) VALUES(Value1, Value2, .....)

SELECT @NewID = SCOPE_IDENTITY()

INSERT INTO Employment(CID,Employer,.....) VALUES(@NewID, ValueA,..........)

SCOPE_IDENTITY: Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

like image 50
andy Avatar answered Sep 17 '22 16:09

andy