SQL - Insert Data into multiple tables in one statement

C

cismajor97

Weaksauce
Joined
Mar 23, 2006
Messages
88
Hello,

I'm trying to figure out how in MS SQL 2005 Express one is able to insert data into multiple tables at once. Here are my tables:

Contact
----------------------
ContactID, int, not null
FirstName, nvarchar(50), not null
LastName, nvarchar(50), not null
AddressID, int, not null
HomePhone, nchar(14), not null
HomeEmail, nvarchar(100), not null
WorkPhone, nchar(14), not null
WorkPhoneExtension, nvarchar(50), not null
WorkEmail, nvarchar(100), not null

Address
------------------------
AddressID, int, not null
Address, nvarchar(50), not null
City, nvarchar(50), not null
StateID, int, not null
ZipCode, nvarchar(50), not null

State
---------------------------
StateID, int, not null
StateName, nvarchar(13), not null

Now the State table will be already filled out since the name of US States won't change. How do I enter the contact address in the Contact table when both tables are being referenced? I think that logically you first enter data in to the address table, and then some how return the ID that was just entered and then use that ID number and enter into the Contact table. Am I on the right track?
 
if you want to know the value of the last Identity column value that was inserted, do a google search on @@IDENTITY
 
cismajor97 said:
How do I enter the contact address in the Contact table when both tables are being referenced? I think that logically you first enter data in to the address table, and then some how return the ID that was just entered and then use that ID number and enter into the Contact table. Am I on the right track?
Click to expand...

I'm not sure you're on the right track, but I also can't figure out your question. What do you mean by "when both tables are being referenced"? You say both, but you show three tables. Referenced by what?

If you mean that you want to insert something into the Address table, then use the AddressID that you just created when you insert a new contact into the Contact table, just do so: set the AddressID to the correct value when you do your INSERT statement on the Contact table.
 
Master mikeblas,

mikeblas said:
If you mean that you want to insert something into the Address table, then use the AddressID that you just created when you insert a new contact into the Contact table, just do so: set the AddressID to the correct value when you do your INSERT statement on the Contact table.
Click to expand...

This is exactly what I'm trying to do, but I'll but doing via and ASP.NET web form. My plan is to make this process a stored procedure, but first I wanted to get the basics down first.

mikeblas said:
set the AddressID to the correct value when you do your INSERT statement on the Contact table.
Click to expand...

Do you mean manually, or programitically?
 
cismajor97 said:
Do you mean manually, or programitically?
Click to expand...
Either.

Say I'm going to insert a new contact named Mike with ContactID 39. Mike lives at ADdressID 113, on Any Street. Yeah, I know you have lots more columns, but it's boring for me to type all that.

Code: 
INSERT INTO Address (AddressID, Address) VALUES (113, 'Any Street')
INSERT INTO Contact (ContactID, FirstName, AddressID) VALUES (39, 'Mike', 113)

If you're using ASP.NET, I'll assume you're using Data.SqlClient. You can bind the same parameter for the two values, if you'd like:

Code: 
INSERT INTO Address (AddressID, Address) VALUES (@NewAddressID, @Address)
INSERT INTO Contact (ContactID, FirstName, AddressID) VALUES (@NewContactID, @FirstName, @NewAddressID)

Note that in both cases I insert into Address first, since I know that Contact uses AddressID as a foreign key into Address so Address must have the row I'm referencing before I reference it.

maw's point is entirely valid, though. You haven't told us how you're computing new IDs, so maybe there's a way for you to get one. Some people do it with a stored procedure, some people use @@IDENTITY, some people use a key generator service, and so on.

Now is also the time to make sure your data model is correct. If other things in your database have addresses, then you're probably okay. If only Contacts have addreses, then you should fix things up a bit.
 
as far as I know i've always used SELECT SCOPE_IDENTITY() instead of @@IDENTITY I read that it is safer somewhere.
 
cismajor97 said:
I'm creating the IDs using auto increment in SQL Server Express 2005.
Click to expand...

Then SELECT @@IDENTITY back and use the result in your next INSERT. Or, write a batch that does it all on the server.
 
Looks like you need to do something like this

INSERT INTO tbl1 (ID, col1, col2, ...) VALUES ('data1','data2',.....)

SELECT @@IDENTITY AS lastID

INSERT INTO tbl2 (tbl1_ID,col1, col2) VALUES (lastID, 'moredata1', 'moredata2'....)
 
You'll need a variable.

Code: 
INSERT INTO tbl1 (ID, col1, col2, ...) VALUES ('data1','data2',.....);
DECLARE @newKey AS INT;
SET @NewKey = @@IDENTITY;
INSERT INTO tbl2 (tbl1_ID,col1, col2) VALUES (@NewKey, 'moredata1', 'moredata2'....);
 
I'm getting a ' Must declare the scalar variable for "@new_Key" ' error message when I run my SQL script. Any ideas where I'm going wrong?

-- Select the database to use
USE adlacircuit;
GO

-- Create variable for ID from Address table
DECLARE @new_Key AS INT;
GO

-- Insert data into Address table and get the value of last AddressID
INSERT INTO dbo.Address (AddressLine, City, StateID, ZipCode)
VALUES ('23456 Test Street', 'TestCity', '3', '12345')
GO

--Capture last created ID from Address table
SELECT @@IDENTITY

-- Set ID to the variable
SET @new_Key = @@IDENTITY
GO

-- Insert data into Contact table, use the value of variable to fill AddressID column of Contact table
INSERT INTO dbo.Contact (FirstName, LastName, AddressID, HomePhone, HomeEmail, WorkPhone, WorkPhoneExtension, WorkEmail)
VALUES ('Jane', 'Doe', '@new_Key', '555-555-1212', 'doe.jane@test.com', '555-555-2121', '#321', 'doe.jane@test.com')
GO

Select *
FROM dbo.Contact
GO
 
Why are you using so many GO statements? Have you tried pulling a few out?

MSDN GO (Transact-SQL) - Link

202276
 
I've removed the all the GO statements

With the statements removed I get the following error:
Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the varchar value '@new_Key' to data type int.

So is the SELECT @@IDENTITY statement selecting the AddressID as a varchar instead of an int? If so how do I convert the SELECT @@IDENTITY statement to an int so that I can use it in my @new_Key variable?
 
You've got '@new_key' instead of @new_key. The variable reference is code, not a string literal.
 
Eureka!

Thanks mikeblas. I thought that the variable had to be wrapped around single quotes for odd reason.
 
Checking my work here.

I've created a stored procedure for my multiple insert query. I've wrapped it a transaction to ensure that if anything goes wrong no tables will be updated. I'm looking for any advice if I need to make changes for the better.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Michael Walker II
-- Create date: September 22, 2007
-- Description: Stored Procedure to add a contact to the database
-- =============================================
ALTER PROCEDURE sp_AddContact
-- Parameters for Address Table
@AddressLine nvarchar(50),
@City nvarchar(50),
@StateID int,
@ZipCode nchar(5),

-- Parameters for Contact table
@FirstName nvarchar(50),
@LastName nvarchar(50),
@HomePhone nchar(14),
@HomeEmail nvarchar(100),
@WorkPhone nchar(14),
@WorkPhoneExtension nvarchar(50),
@WorkEmail nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Start transaction
BEGIN TRANSACTION

BEGIN TRY
-- Insert data into Address table and get the value of last AddressID
INSERT INTO dbo.Address (AddressLine, City, StateID, ZipCode)
VALUES (@AddressLine, @City, @StateID, @ZipCode)

--Capture last created ID from Address table
SELECT @@IDENTITY

-- Create variable for ID from Address table
DECLARE @new_Key INT

-- Set ID to the variable
SET @new_Key = @@IDENTITY

-- Insert data into Contact table, use the value of variable to fill AddressID column of Contact table
INSERT INTO dbo.Contact (FirstName, LastName, AddressID, HomePhone, HomeEmail, WorkPhone, WorkPhoneExtension, WorkEmail)
VALUES (@FirstName, @LastName, @new_Key, @HomePhone, @HomeEmail, @WorkPhone, @WorkPhoneExtension, @WorkEmail)

-- End Catch statement
END TRY

BEGIN CATCH

-- If error rollback transaction
ROLLBACK TRANSACTION

END CATCH

COMMIT TRANSACTION
END
GO
 
Recommend changing "@@IDENTITY" to be "SCOPE_IDENTITY()" instead. Otherwise, you could wind up capturing the new IDENTITY value from another simultaneous transaction. Using SCOPE_IDENTITY() ensures that you only get the new IDENTITY value from the current transaction.

More info: http://msdn2.microsoft.com/en-us/library/ms190315.aspx
 
Will do, but does everything look okay with the transaction? I know I have alot of "newbie" comments but it's the only way I'm going to learn what's happening later on.
 
Getting into the habit of commenting code is always beneficial. What makes sense now may seem fuzzy later on when doing maintenance. You'll thank yourself later for starting this habit now.

I would drop this statement:
Code: 
--Capture last created ID from Address table
SELECT @@IDENTITY
It seems redundant, especially since you're storing that value in a local variable.
 
There's no danger of @@IDENTITY returning an identity value from another transaction. It might give an unexpected value if you have different scopes, but no data ever crosses transactions as is being suggested here.
 
You must log in or register to reply here.
Back
Top