Importing Thousands of records from csv file

Question

0.00/5 (No votes)

See more:

Hi All,

I want to import thousands of record from a csv file and same time I also need to manage parent child relationship for Inserting Data in tables.

I have used SQLBulkCopy to Import data, and in terms of performance its fast.
But I am not able to manage parent child Relationship for Inserting Data in two different tables.

Can Any one guide me that how can its possible ?
or is there any other way to do this except SQLBulkCopy ?

MY Requirement IS : I want to Import thousands of records using Parent Child relationship (performance is key point).

Please provide suggestions.

Thanks,
Rajan Bajania

Posted 23-Jun-14 21:55pm

RBajania

Add a Solution

Comments

Prasad Avunoori 24-Jun-14 4:59am

Are parent and child records exist in same csv file? Can you please share the field details of it?

RBajania 24-Jun-14 9:21am

Hi Prasad,
Thanks for your response.

Yes Both Parent and Child records are in same csv file.

CompanyName,Address,City,Contact1,JobTitle1,Contact2,JobTitle2,Contact3,JobTitle3,Contact4,JobTitle4

The Above is The Header Of My csv
In My Case I have Parent Table of Company and Child table of Contact. so One Company and multiple Contacts.
Currently I have created a cursor and passed a Table to it from front end (Using User Defined Table Type).
but its too slow and time out occurs as there are around 50000 records in csv and in future I can have more records too.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2014-06-23T23:11:00

One quick suggestion is to import all the records in a temporary table using SQLBulkCopy. Once all records gets imported then you execute a stored procedure who first import parent records and then import child records from temp table with updated reference keys with respect to the parent tables

Thava Rajan · Answer 2 · 2014-06-23T23:36:00

Solution 2

Then you have to try some thing Like SSIS(SQL Server Integration services) Google it you have lot info you will get for your solution

Posted 23-Jun-14 23:36pm

Thava Rajan