vendredi 27 mars 2015

Simple SQL Insert - Many Comma Delimited Values from Textbox to ONE SQL Column

This should be simple. I have a textbox (textarea) with comma separated values. Like this:


425020,547538,548029,548853,552373


I have done this two ways. One with a table that has two columns, |Number6|Number16| ... and one that just has one column |Number6| in order to try and remove any confusion. For what is below they are being run against the one column table.


Here are four of the ways I tried:



INSERT INTO MYDB.dbo.MYTABLE (Number6) VALUES (425020,547538,548029,548853,552373);
INSERT INTO MYDB.dbo.MYTABLE VALUES (425020,547538,548029,548853,552373);
INSERT INTO MYDB.dbo.MYTABLE (Number6) VALUES (425020),(547538),(548029),(548853),(552373);
INSERT INTO MYDB.dbo.MYTABLE VALUES (425020),(547538),(548029),(548853),(552373);


Since I am submitting this via an ASP page I am trying to avoid writing an insert line for every value. I have had over 20,000 values before.


Obviously the above code failed because in the first and third insert each comma indicates a column to SQL. In the second and fourth insert it is incorrect syntax near ","


I have built much more complicated queries and yet for some reason I can't figure out this simple insert.


Aucun commentaire:

Enregistrer un commentaire