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