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, May 03 '13 at 13:25
But how exactly do you **want them stored** in the database? — Mike Perrenoud, May 03 '13 at 13:26
@Pondlife I must admit i'm not familiar with either solutions. I will look into BULK Insert thanks ! — phadaphunk, May 03 '13 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, May 03 '13 at 13:28
@PhaDaPhunk, you talk about the size being too large for a single field, then say you build a temp table and provide an example where each field is a new row (but then go against that), and so I'm trying to get a good understanding of what you **want.** You never said, `I want to have a table with each field separated on a single row.` for example. And consider this, if the string is too large for a single `NVARCHAR(MAX)`, I'm not sure you could build that many fields because there is a maximum number of fields as well. — Mike Perrenoud, May 03 '13 at 13:30
Seems like a homework question, NVARCHAR(MAX) will store this string thousands of times over. — softwarebear, May 03 '13 at 13:36
5 x 8000 = 20000 = ~20KB per entire file. sorry ... hundreds of thousands of copies. There is a max field limit on a table, but he's not creating fields, they are rows with one field. As Pondlife has said, bcp is your friend, but that probably won't answer your question properly. — softwarebear, May 03 '13 at 13:43
your file is over 20GB ... then you definitely need BCP tool or use the Bulk load api. — softwarebear, May 03 '13 at 13:45
Maybe there's something in here that could help: http://www.sommarskog.se/arrays-in-sql-2008.html — the_lotus, May 03 '13 at 13:45
but why do you need each number in separate row ... what are you doing to do with the data once it's in the database ... do you actually need them storing in the database at all ? — softwarebear, May 03 '13 at 13:46
@softwarebear Yes I do need them in the database. And saving a text file and using bulk insert seems like the right solution for me thanks :)! — phadaphunk, May 03 '13 at 13:47
you'll need to consider the max row limit per table, you will most likely need to partition the data to get any kind of performance out of the system — softwarebear, May 03 '13 at 13:48
Are you reading **myString** values from Database? If yes then it is very simple and fast. Let me if this is the case. — Mohsin JK, May 03 '13 at 14:31

score 2 · Accepted Answer · answered May 03 '13 at 13:37

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:

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

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

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

edited May 23 '17 at 11:57

Community

1
1

answered May 03 '13 at 13:44

Cemafor

1,633
12
27

Serge · Answer 3 · 2013-05-03T14:04:36.757

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)

score 0 · Answer 4 · answered May 03 '13 at 14:43

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.

Split and insert string into database

4 Answers4