Split and insert string into database

Question

I'm sure the answer is right there in my face but I can't seem to find a good way to do this.

I have a string containing a lot of values all seperated by ,. I never know (before runtime) how many values there will be in that string. I want to insert them in a database (in this case SQL Server 2008) but the string can be way too large for a NVARCHAR(MAX) so I create a temp table and want to insert the values in it.

I could easily do something like this (I'll make a small one for the example) :

VB

myString = "101,102,103,104,105,106,107,108,109"

For Each value As String in myString.Split(",") 
    myCommand.CommandText = "INSERT INTO tempTable VALUES @value"
    myCommand.Parameters.AddWithValue("@value", value) 
Next

C#

myString = "101,102,103,104,105,106,107,108,109";

foreach (string value in myString.Split(','))
{
    myCommand.CommandText = "INSERT INTO tempTable VALUES @value";
    myCommand.Parameters.AddWithValue("@value", value);
}

But let's face it this is not the way to do it. If I have 8000 records it would result in 8000 different insert and I would probably get fired for even thinking about doing this.

Can someone point me out in the right direction ?

tl;dr

String with values look like this "001,002,003,004,005...8000" how can I insert each of the values separated by , without making 8000 INSERT statements.

Have you considered writing the numbers to a text file, then importing that file with bcp.exe or BULK INSERT? Or use the SqlBulkCopy API directly, perhaps. — Pondlife
– Pondlife, Commented May 3, 2013 at 13:25
But how exactly do you want them stored in the database? — Mike Perrenoud
– Mike Perrenoud, Commented May 3, 2013 at 13:26
@Pondlife I must admit i'm not familiar with either solutions. I will look into BULK Insert thanks ! — phadaphunk
– phadaphunk, Commented May 3, 2013 at 13:27
@MichaelPerrenoud What do you mean ? I want them stored in a table, all values separatly, as NVARCHAR format. Am I missing something ? — phadaphunk
– phadaphunk, Commented May 3, 2013 at 13:28
your file is over 20GB ... then you definitely need BCP tool or use the Bulk load api. — softwarebear
– softwarebear, Commented May 3, 2013 at 13:45

03Usr · Accepted Answer · 2013-05-03 13:37:00Z

2

Like @pondlife suggested you can save your string into a text file and then using the Bulk Insert you can insert the data into your table:

BULK
INSERT YourTable
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

See more info here:

answered May 3, 2013 at 13:37

03Usr

3,4456 gold badges43 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:57:19Z

0

You could build a larger Insert statment, as shown here.

INSERT INTO tempTable (ColumnName) VALUES
    (@value1), (@value2)

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered May 3, 2013 at 13:44

Cemafor

1,65312 silver badges28 bronze badges

Comments

Serge · Accepted Answer · 2013-05-03 14:04:36Z

0

You could split the string then join its elements into several smaller groups you would then insert.

(I write it withouth testing it)

var step = 1000; /* other value ? */
var parts = str.Split(",");

for (int i = 0; i < parts.length; i += step) {
    var vals = String.Join(",",
        Enumerable.Range(i, Math.Min(step, parts.length - i)).Select(i => String.Format("({0})", parts[i])).ToArray()
    );

    ... INSERT ... vals
}

it should lead to a set of statements like this:

INSERT MySchema.MyTable VALUES (1),(2),(3),...
INSERT MySchema.MyTable VALUES (1001),(1002),(1003),...
INSERT MySchema.MyTable VALUES (2001),(2002)

edited May 3, 2013 at 14:04

answered May 3, 2013 at 13:59

Serge

6,7425 gold badges33 silver badges58 bronze badges

Comments

Mohsin JK · Accepted Answer · 2013-05-03 14:43:17Z

0

If you are reading values from Database to application then you should do like this.

myString = "101,102,103,104,105,106,107,108,109";

myCommand.CommandText = String.Format(@"INSERT INTO tempTable 
                          SELECT Id from Guides Where Id in ({0})",myString);

This is fast and appropriate way to insert multiple values in one go.

answered May 3, 2013 at 14:43

Mohsin JK

5712 gold badges8 silver badges18 bronze badges

Collectives™ on Stack Overflow

Split and insert string into database

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related