Query for Save that inserts and updates 2 tables

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

General SQL Server Forums

New to SQL Server Programming

Query for Save that inserts and updates 2 tables

Author

Topic

Seequiller
Starting Member

3 Posts

Posted - 2008-09-24 : 12:54:35

My Save function inserts and updates a record in my project. I have an Address table (adddress_ID, street_add, city, state, zip_code, county), and a Customer table (customer_ID, address_ID, name, trade_name, phone_number).

My customer table references address table to retrieve address info first. I know how to insert values into tables but need to insert, update both tables and join them together under one Save method. Any SP or Select is welcome.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-24 : 13:02:29

Your issue is not clear. Please show us a data example of what you mean.

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

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 13:03:13

[code]CREATE PROC InsertData
@street_add varchar(100),
@city varchar(100),..
@name varchar(100),
@trade_name varchar(100),...
AS
DECLARE @Address_ID int
IF NOT EXISTS (SELECT 1 FROM Address WHERE street_add=@street_add
AND city=@city
AND state=@state
AND zip_code=@zip_code
AND county=@county)
BEGIN
INSERT INTO Address(fields..)
SELECT @street_add, @city,..
SET @Address_ID = SCOPE_IDENTITY()
END
ELSE
BEGIN
SELECT @Address_ID =Address_ID
FROM Address
WHERE street_add=@street_add
AND city=@city
AND state=@state
AND zip_code=@zip_code
AND county=@county
END
INSERT INTO Customer(fields..)
SELECT @Address_ID,...
GO[/code]
i've not included all columns. remeber to include them in select and insert list

Seequiller
Starting Member

3 Posts

Posted - 2008-09-25 : 14:42:01

Thank you visakhlc

I got part of the select created and it is similar to your example. I had a migraine yesterday and had to leave for the day so sorry I did not reply back.

Thank you for your responses, I have an idea now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:57:22

quote:
Originally posted by Seequiller

Thank you visakhlc

I got part of the select created and it is similar to your example. I had a migraine yesterday and had to leave for the day so sorry I did not reply back.

Thank you for your responses, I have an idea now.

No worries
You're always welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources