sqlsql-server-2005insertmultiple-insert

Simple SQL Server Insert - Many Comma Delimited Values from Textbox to ONE 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.

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   |

Solution

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