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

sk1922
sk1922 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Randy Wilson.Net Developer

Commented:
Can you edit the structure of the ImportTbl ?

Author

Commented:
Hi wrwilson,

But I'm not sure I follow. Would you mind elaborating a bit?
To insert to more tables, just add the extra insert statements.
But ScopeIdentity will only return the id of the last inserted row. What you need is a way of retrieving it for all rows.
I would guess that Lanme, Fname, Phone would be a unique identifier, you can use that in subsequent inserts to retrieve the Cust_ID  

E.g.
 
Insert Into CusOrdTbl(CUST_ID, ITEM_NUM, QUANTITY, PRICE)
Select CUST_ID, ITEM_NUM, QUANTITY, PRICE
From CustomerTbl C Inner Join ImportTable I on C.FName = I.FName, C.LName = I.LName, C.Phone = I.Phone

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

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

Author

Commented:
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).  
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.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial