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 |
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.