Insert Records with Foreign Key relationships

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

Transact-SQL (2008)

Insert Records with Foreign Key relationships

Author

Topic

tavo2k4
Starting Member

4 Posts

Posted - 2010-03-29 : 17:29:44

Hello,

I was wondering if any one of you can share some code on how to insert records using multiple tables with relationships between each other.
My scenario is the following: I have 3 tables, CUSTOMERS, CUSTOMERADDRESS, AND ADDRESSES.
CUSTOMERS table has a field CustNbr as PK.
CUSTOMERADDRESS table has two PK fields CustNbr and AddressID, which in turn are FK to CUSTOMERS (CustNbr) and ADDRESS (AddressID ) tables.
ADDRESS table has AddressID as PK, this table will hold address information.
CUSTOMERADDRESS table will hold one to many relationship with CUSTOMERS table. And Many to one with ADDRESS table.
My question is how do I Insert a new address using this relationship and make sure this 3 tables get populated keeping this relationship? For example I want to insert 2 addresses, Mailing and Billing for a single customer. Do I use Join to do this, I never used Joins to Insert a record before.
I already manually tested this relationship with one customer (Edit Table View). It works well when I read the customer record it shows the two addresses that I manually entered. Now I want to do it with a SQL Query. Any help would be appreciated.
Gus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-29 : 17:36:54

You insert into the parent tables child, then the child tables. Looks like you'll insert into customers and address first (doesn't matter which one) and then into customeraddress.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tavo2k4
Starting Member

4 Posts

Posted - 2010-03-29 : 17:51:17

Thanks, I am not inserting anything into the Customers Table. What I am doing is, getting the address information from each customer in Customers table and then insert that information in Address table, and from the new inserted record get @@IDENTITY

This part is working OK, but after I create new record in Address table I need to insert value from @@IDENTITY and the current Customer Number from the Customer Record that I pulled the Address from.

Basically the CustomerAddress table is reference from Customers and Address Table.

Any code on this will be appreciated.

Gus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-29 : 17:53:37

Please show us a data example to make it very clear what you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tavo2k4
Starting Member

4 Posts

Posted - 2010-03-30 : 10:30:37

Here is the code, on this example, second Select I am manually inputing cusNbr. That is where my problem is, I need to get custNbr from the Customers table in when I get the address information, first Select.


use GruberSystems
--use tableExample
go
declare @Identity int

INSERT INTO dbo.Address(addressLine1, addressLine2, city, stateProvince,
postalCode, countryRegion)

select Customers.address, Customers.address2, Customers.city,
Customers.state, Customers.zip, Customers.country from Customers

join CustomerAddress on Customers.CustNBR = CustomerAddress.CustNBR
join Address on CustomerAddress.AddressID = Address.AddressID

where Customers.custNbr = '1'

set @Identity = @@IDENTITY

insert into CustomerAddress(addressID, custNbr, addressTypeID)

Select @Identity,  '1', '1'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-30 : 10:48:50

I'm not sure why you are join to address to insert an address. But I think wha you want to do is something like:


-- Populate Addresses
INSERT Addresses
(
	<Columns>
)
SELECT
	Customers.address, 
	Customers.address2, 
	Customers.city,
	Customers.state, 
	Customers.zip, 
	Customers.country 
FROM 
	Customers
LEFT OUTER JOIN
	Addresses
	ON Customers.address = Addresses.address
	AND Customers.address2 = Addresses.address2
	AND Customers.city = Addresses.city
	AND Customers.state = Addresses.state
	AND Customers.zip = Addresses.zip
	AND Customers.country = Addresses.country
WHERE
	Addresses.AddressID IS NULL
	
-- Use the populated addresses to insert any missing CustomerAddresses
INSERT CustomerAddresses
SELECT
	Customer.CustomerID,
	Addresses.AddressesID
FROM 
	Customers
INNER JOIN
	Addresses
	ON Customers.address = Addresses.address
	AND Customers.address2 = Addresses.address2
	AND Customers.city = Addresses.city
	AND Customers.state = Addresses.state
	AND Customers.zip = Addresses.zip
	AND Customers.country = Addresses.country
LEFT OUTER JOIN
	CustomerAddresses
	ON Customer.CustomerID = CustomerAddresses.CustomerID
	AND Addresses.AddressesID = Addresses.AddressesID

EDIT: Forgot ANDs

tavo2k4
Starting Member

4 Posts

Posted - 2010-03-30 : 11:46:33

Do I have to JOIN Addresses and Customers tables on each address field. I try your code but it gives me error messages on the Inner Joins.

Do you know of a on-line tutorial where they show how to Insert on multiple related tables using relationships? I just need to see a couple of examples on how it is done.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-30 : 15:23:39

I updated my query a little bit. I forgot the ANDs on the JOIN condition.

Are you able to write a query similar to the one I posted that just selects teh data you want to insert into the Addresses table? Start small by writing a SELECT. Once it is returning to correct data if you have trouble with the insert post back and we can help you out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources