Import into multiple tables

Results 1 to 2 of 2

Thread: Import into multiple tables

  1. #1
    Join Date
    Dec 1969

    Default Import into multiple tables

    How does one go about importing from Excel into 3 SQL Server tables where referential integrity must be maintained. Our product information is in one Excel spreadsheet and respective portions must be imported into tblProducts, tblProductDetails, tblSupplier.<BR><BR>Can it all be done at once or do I have to run through DTS once for each table?<BR><BR>thanks,<BR><BR>Cameron

  2. #2 Guest

    Default RE: Import into multiple tables

    When you are talking about referential integrity, this must mean that one of your db tables has a primary key (PK) which forms part of the primary key of the other related tables:<BR><BR>Table1 has [ID] as its PK.<BR><BR>related table (Table2) has [ID],[SubID] as its PK. (one to many relationship)<BR><BR>Therefore for each row out of your spreadsheet, you need to create a new record in table1 each time you get a new ID. Then when you have saved that record you create the related records in the table2.<BR><BR>If your spreadsheet has columns:<BR><BR>ID, SubID, v1, v2, v3<BR><BR>Then it would go abit like this:<BR><BR>&#060;%<BR>ID=Null<BR>do while not rsXl.EOF<BR>&nbsp;&nbsp;&nbsp;&nbsp;if rsXl(0) &#060;&#062; ID then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;ID=rsXl(0)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;rsT1.AddNew<BR>&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rsT1("ID")=ID<BR>&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rsT1 ("v1")=rsXl(2)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;rsT1.Update<BR>&nbsp;&nbsp;&nbsp;& nbsp;End If<BR>&nbsp;&nbsp;&nbsp;&nbsp;rsT2.AddNew<BR>&nbsp ;&nbsp;&nbsp;&nbsp;rsT2("ID") = ID<BR>&nbsp;&nbsp;&nbsp;&nbsp;rsT2("SubID") = rsXl(1)<BR>&nbsp;&nbsp;&nbsp;&nbsp;rsT2("v2") = rsXl(3)<BR>&nbsp;&nbsp;&nbsp;&nbsp;rsT2("v3") = rsXl(4)<BR>&nbsp;&nbsp;&nbsp;&nbsp;rsT2.Update<BR> &nbsp;&nbsp;&nbsp;&nbsp;rsXl.Movenext<BR>loop<BR>% &#062;<BR><BR>This code is quite crude, just to give you a clue. It assumes that the Excel recordset is sorted by ID, SubID.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts