How can I split a table more efficently

Question

0.00/5 (No votes)

See more:

I am trying to insert rows between datatables. However, the way I have it now is very inefficient as it takes up a lot of time and memory to complete the process.

here is an example of how I am using my code:

VB

For Each row As DataRow In temptable.Rows
Insert statement
select ID statement- for each iteration to show the number of rows has changed
5 more insert statements with the ID column number being used in the individual_ID column of each table.
Next

I am breaking up tables based upon their columns. I am using an Individual_ID in each table so that corresponding records will be selected as well.

What I want to know is can I do something similar with bulk copy where I can import to each table and have the Individual_ID inserted as rows are added, or there another way?

What I have tried:

I have tried to rule out insert statements by using

VB

if dt.columns.contains then

statement. It does improve the time, however, it is still slow.

Posted 27-Aug-18 10:50am

Member 11856456

Updated 28-Aug-18 4:01am

Richard MacCutchan

v2

Add a Solution

1 solution

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)

Gerry Schmitz · Answer 1 · 2018-08-28T04:02:00

Solution 1

I think what you want to do is "bulk insert" with an "IDENTITY" column.

Keep Identity Values When Bulk Importing Data (SQL Server) | Microsoft Docs[^]

This one includes "staging":

sql server - BULK INSERT with identity (auto-increment) column - Stack Overflow[^]

Posted 28-Aug-18 4:02am

Gerry Schmitz

Comments

Member 11856456 28-Aug-18 10:16am

The only issue with this is it requires that the source already has an Identity. What I am trying to do as I import the information into the tables, they will use the identity column of my main table. It would be a 1 to 1 insert. So, if the new row in the main table column is 4000 then the individual_ID corresponding to the other tables should be 4000. Then the next import 4001 and so on.

As it stands only my target tables have the ID and individual_ID columns. my table called profile is the main table which all tables will be referenced from. how can I place the ID value into each table that it needs to be inserted into?