How do I insert data into two related tables in SQL Server 2008 database

[southpawjoe]

Hi,
I would like to insert data into two related tables in a SQL Server 2008 database. I created the following stored procedure but it has a problem. When I add a record, the State table gets updated for every entry. I would like it to be normalized properly. My question is how do I check the State table to see if the record exist and add it only if it is needed. Any assistance will be greatly appreciated!

CREATE PROCEDURE CustomerInsert
 
    (
    @Company nvarchar(50)= NULL,
    @CustomerName nvarchar(50),
    @Phone nchar(12)= NULL,
    @Address1 nvarchar(50),
    @Address2 nvarchar(50)= NULL,
    @City nvarchar(50),
    @State nchar(2)= NULL,
    @Zipcode nchar(10)= NULL
    )
 
AS
    /*SET NOCOUNT ON*/
 
    DECLARE @State_ID int
 
    INSERT INTO State (City, State, Zipcode)
    VALUES (@City, @State, @Zipcode)
 
    SELECT @State_ID=@@IDENTITY
 
    INSERT INTO Customers (Company, CustomerName, Phone, Address1, Address2, State_ID)
    VALUES (@Company, @CustomerName, @Phone, @Address1, @Address2, @State_ID) 
RETURN
 

[debasisdas]

For your case the two table in questions must be related.
One must be the parent and the other child.
As per the rule to create a record in child table there must be a parent record in the parent table to maintain REFERENTIAL INTEGRITY.
So before inserting a new record to the child table check if it has a parent record.
If it exists insert into child table
else insert a record into the parent table first and then into the child table.

[southpawjoe]

Hi,

The two tables are related. The State table is the parent with a primary key on the ID column (not shown in the example) and the Customer table has a foreign key Customer.State_ID. Both tables are set to Cascading update and delete.

[ck9663]

Why do you need to insert table in State table for every insert in Customer Table? Shouldn't your state table be a fix dimension table? You can get that in the net.

Happy Coding!!!

~~ CK