asked on

Select from table and insert into multiple tables using a stored procedure

Hi Experts,

I am trying to learn and understand how to write stored procedures in SQL Server 2005. Specifically, I am working with some data that was originally in an Excel file (w/ over 30 columns of data) and used an SSIS package to import the data all into a single table called ImportTbl. What I'd like to understand now is how to go through each row in the ImportTbl and separate the information across multiple tables all while maintaining referential integrity and can't seem to figure it out. I was able to successfully create a procedure that will SELECT a number of columns from the ImportTbl and INSERT it into another table called CustomerTbl. But I can't figure out how to INSERT a different set of columns from ImportTbl into another table called CustOrdersTbl, then another set to another table and so on.

I'm not sure how to approach this as I am very new to stored procedures. Any guidance or examples would be greatly appreciated to help me get on my way!! I know it's not much but here's what I have thus far.

Snapshot of the tables/columns that are involved....

ImportTbl (storage table)
LNAME
FNAME
GENDER
PHONE
EMAIL
.......
....... (30+ columns)

CustTbl (parent table)
CUST_ID (PK)
LNAME
FNAME
GENDER
PHONE
EMAIL
........
........

CustAddrTbl (child table)
CUST_ADDR_ID (PK)
CUST_ID (FK) -- (reference CUST_ID from CustTbl)
ADDR1
ADDR2
CITY
STATE
........
........

CustOrderTbl (child table)
ORDER_ID (PK)
CUST_ID (FK) -- (reference CUST_ID from CustTbl)
ITEM_NUM
QUANTITY
PRICE
........
........

CREATE PROCEDURE dbo.usp_SelectMstrImport
AS
BEGIN
 
INSERT INTO CustomerTbl(LNAME,FNAME,GENDER,PHONE,EMAIL)
SELECT LNAME,FNAME,GENDER,PHONE,EMAIL FROM ImportTbl
SELECT SCOPE_IDENTITY() AS CUST_ID
 
END
GO

Open in new window

Randy Wilson

Can you edit the structure of the ImportTbl ?

sk1922

ASKER

Hi wrwilson,

But I'm not sure I follow. Would you mind elaborating a bit?

ASKER CERTIFIED SOLUTION

MikeToole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeToole

Also, if you have many rows in the import table for a single Customer, you need to make sure that the Select query on your customer table insert uses a Distinct clause to make sure you don't try to insert the same row twice.
And another thing, if the same customer can appear in the import table on different days, you'll need to make sure that you don't try to insert duplicates of existing rows

sk1922

ASKER

Hi MikeToole,

I appreciate all the guidance you've offered up.
In this particular case I don't need to care for that since this file actually lists the customer only once (but it's definitely necessary that I keep that in mind as I work with other stored procedures in the future).

I didn't realize that by adding just another INSERT would do the job!!
I was under the impression that I would have to learn CURSORS or setup WHILE LOOPS to accomplish this. Was I completely heading in the wrong direction or just another (possibly complex) way of achieving the same?

If you don't mind me asking, how would I go about caring for NULL values? In my ImportTbl, I have quite a few fields that are NULL (different columns for different rows).

MikeToole

When a Set-based solution exists it's better to use it, reserve Cursors for the circumstances when Sets won't do it.
You shouldn't need to worry about Nulls if you've set up the columns in your tables to accept them.

sk1922

ASKER

Really, really helpful. I appreciate all the assistance!! Thank you for making what seemed like a daunting task quite simple.