Mass insert from file to SQL Server

Results 1 to 2 of 2

Thread: Mass insert from file to SQL Server

  1. #1
    Join Date
    Dec 1969

    Default Mass insert from file to SQL Server

    I was wondering how I would insert the contents of a file separated by ~ into a SQLServer database. I want to dump what&#039;s in the table and populate it with what&#039;s in the file. This would all be done in VB.<BR><BR>Thanks

  2. #2 Guest

    Default RE: Mass insert from file to SQL Server

    You just read the the file one line at a time (assuming that the each record is on a separate line). Then you split the line with the split function, into an array:<BR><BR>rowArray=Split(strLine, "~")<BR><BR>Then you either use the Recordset Object&#039;s AddNew method, or build sql insert commands for each row.<BR><BR>AddNew:<BR>&#060;%<BR>rs.AddNew<BR>rs( 0) = rowArray(0)<BR>rs(1) = rowArray(1)<BR>...<BR>rs(n) = rowArray(n)<BR>Rs.Update<BR>%&#062;<BR><BR>Sql Insert method:<BR><BR>strSQLInsert = "Insert into YourTable (Field1, Field2, ... Fieldn) Values (&#039;" & rowArray(0) & "&#039;, &#039;" & rowArray(1) & "&#039;, &#039;" & ... rowArray(n) & "&#039;) " <BR>objConn.Execute(strSQLInsert)<BR><BR><BR>Not e: Both the above methods depend each row of the data from the file having the same number of fields, and you would have to do some kind of data conversion depending on the field types of your table.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts