Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Bulk Insert Into Table that Requires an Auto Generated ID from another Table

Posted on 2007-08-07
5
Medium Priority
?
376 Views
Last Modified: 2013-11-05
I'm trying to avoid using a cursor.  I have a flat table that is populated by a CSV file that the user uploads.  I need to move the data from this table into two other tables.  Once table is called individuals, the other is called individualphonenumbers.  IndividualPhoneNumbers has a column for the IndividualID that is generated in the Individuals Table when a record is inserted.  Is there anyway to do my insert(s) using a bulk insert statement or am I going to be forced to cursor through each record.
0
Comment
Question by:davidcahan
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 19651333
Hi,

Just a thought - bulk insert into a staging table, then insert/update individuals, then insert/update individualPhoneNumbers.

My $0.10 worth
  David
0
 
LVL 19

Accepted Solution

by:
frankytee earned 1000 total points
ID: 19652056
i usually do what dtodd suggested, into a staging table free of constraints, indexes etc to speed up the load.
or if you have triggers on the real table you can disable with
alter table mytable disable trigger mytrigger
or you can disable/drop whatever constraints and then enable/recreate them

and then update/insert it afterwards with whatever sql that is more efficient
0
 

Author Comment

by:davidcahan
ID: 19654088
but if i understand what you are both saying, you mean do my insert into the Individuals Table, then update my staging table with those ID's then insert into my PhoneNumbers table using the ID's?
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 1000 total points
ID: 19657757
Hi,

What we are suggesting is creating a third table that can accept the entire CSV file via a bulk insert. That table will be denormalised. It will be, if you like, a SQL copy of the CSV file. There will be no or few constraints. Do figure out some form of rowID such as an identity ...

Your process would go like this:
Bulk insert into this new staging table
Update/Insert Individuals
Update/Insert PhoneNumbers - now can use the ID etc from Individuals.

HTH
  David


0
 

Author Comment

by:davidcahan
ID: 19658590
yea, i get it.  I was already using the third table.  I just wanted to make sure I understood what you had meant by Update/Insert Individuals

Thanks
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question