0

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.

I am not trying to insert the entire string into a single field. I am trying to take a string that has FIVE numbers and put them into 5 rows. So 425020,547538,548029,548853,552373 should go into the table as:

+--Number6--+
|  425020   |
|  547538   |
|  548029   |
|  548853   |
|  552373   |
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jason Paw
  • 96
  • 13
  • possible duplicate of [Insert multiple values using INSERT INTO (SQL Server 2005)](http://stackoverflow.com/questions/2462517/insert-multiple-values-using-insert-into-sql-server-2005) – Mahesh Mar 28 '15 at 04:22
  • Thanks for the duplicate ones. They are close and I see how some of my attempts work only in 2008. If I do not get any other solutions I will mark this as answered and make the INSERT command in ASP run through a series of UNION ALL statements. – Jason Paw Mar 28 '15 at 05:58
  • I changed my mind. Instead I wrote a VBscript that wrote the values to a text file and will build a stored procedure I can call to BULK Insert that text file. – Jason Paw Mar 28 '15 at 17:24

2 Answers2

0

SQL considers a comma to be the delimiter between values. So 123,456 represents two values, rather than one string.

To solve your problem change this:

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

To This:

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

I do wonder why you want to insert comma delimited strings into a single field, this is probably a bad design decision, but I assume you have a good reason...

EDIT

Ok, so you changed the question a bit. If you've got a text box with multiple values that you want to insert into a table as multiple rows the simplest thing to do would be to use string.split to convert your comma separated list into an array and then you can iterate through the elements of the array inserting them one at a time.

Greg the Incredulous
  • 1,676
  • 4
  • 29
  • 42
  • I couldn't figure out how to do code blocks in this comment so I edited my original post to answer your question. – Jason Paw Mar 28 '15 at 05:51
0

I changed my code in ASP to write the textbox values into a text file. Actually I wrote two sets of values into two seperate text files.

<%
function WriteToFile(FileName, Contents, Append)
on error resume next

if Append = true then
   iMode = 8
else 
   iMode = 2
end if
set oFs = server.createobject("Scripting.FileSystemObject")
set oTextFile = oFs.OpenTextFile(FileName, iMode, True)
oTextFile.Write Contents
oTextFile.Close
set oTextFile = nothing
set oFS = nothing

end function

%>
<%
WriteToFile "C:\INSTALL\Test1.txt", Response.Form("values1"), True
WriteToFile "C:\INSTALL\Test2.txt", Response.Form("values2"), True
%>

Then with the amazing help of StackOverlow I built the following SQL Query which reads each file into its own table and then combines the data from each file side-by-side, just as if I were merging them like I asked to do in this question.

--Drop Tables in case they exist, Re-create them, import data from text file
DROP TABLE Table1
CREATE TABLE dbo.Table1 (NUMBER VARCHAR(16)) 
BULK INSERT dbo.Table1
FROM 'c:\install\Test1.txt' 
WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n');

DROP TABLE Table2
CREATE TABLE dbo.Table2 (BIN VARCHAR(6)) 
BULK INSERT dbo.Table2
FROM 'c:\install\Test2.txt' 
WITH (FIELDTERMINATOR = '\t',ROWTERMINATOR = '\n');

--Prepare Table3 for merge of other two tables 
DROP TABLE Table3
CREATE TABLE dbo.TEMP (NUMBER VARCHAR(16), BIN VARCHAR(6));

--Combine Table1 with Table2 into Table3 (They called this a CTE)
WITH C1 AS
(SELECT ROW_NUMBER() OVER (ORDER BY dbo.Table1.NUMBER) AS Rn1,NUMBER FROM dbo.Table1),
C2 AS
(SELECT ROW_NUMBER() OVER (ORDER BY dbo.Table2.BIN) AS 
Rn2,BIN FROM dbo.Table2) 
INSERT INTO dbo.Table3 SELECT C1.NUMBER,C2.BIN FROM C1 INNER JOIN C2 ON C1.Rn1 = C2.Rn2;

I put the code above into a single line and stuffed it into an INSERT RECORD post in ASP, woot! I did not paste in the code that gets the values and puts them into the values variable above in case you see that missing.

So, didn't solve the javascript join question with javascript, but it still has been solved and that is what matters to me.

Jason Paw
  • 96
  • 13