CODEWITHSUNDEEP
c#vb.netsql-server-2008insert
phadaphunk
phadaphunk

Reputation: 13313

Split and insert string into database

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.

Upvotes: 1

Views: 3082

Answers (4)

Mohsin JK
Mohsin JK

Reputation: 571

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.

Upvotes: 0

Serge
Serge

Reputation: 6712

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)

Upvotes: 0

Cemafor
Cemafor

Reputation: 1653

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

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

Upvotes: 0

03Usr
03Usr

Reputation: 3435

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:

Upvotes: 2

Related Questions