1

I began writing my SQL with the intention of using a multi-line insert statement, however, the amount of records are much larger than I was expecting, therefore, I want to try a BULK INSERT statement. I have a .txt file with records in the following format (one record per line):

(1234, 1, 'Document Name', 'Author' , 'User', 'Security', 'Date Created', 'Location'),

Each punctuation mark is included above (including the open and closed parenthesis and comma at the end). How can I write the BULK INSERT statement such that each record is inserted correctly into the database using my current format? I would rather not have to change the format of the .txt file because code has already been written to output the record in the above format.

I'm looking for something similar to the following:

BULK INSERT EmployeeDB.dbo.Employees
FROM 'C:\Data\EmployeeData.txt'
WITH 
  (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\r\n'
  );
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
neuquen
  • 3,991
  • 15
  • 58
  • 78
  • possible duplicate of [SQL Server Bulk insert of CSV file with inconsistent quotes](http://stackoverflow.com/questions/782353/sql-server-bulk-insert-of-csv-file-with-inconsistent-quotes) Essentially, SQL Server needs all the columns to quoted; if that isn't the case then you need to pre-process the file or use a different tool that can handle inconsistent quoting. – Pondlife May 09 '13 at 21:35

1 Answers1

0

Well, it's easier to use the Import-Export wizard, and save the created package for later usage.

http://msdn.microsoft.com/en-us/library/ms140052.aspx

Oscar
  • 13,594
  • 8
  • 47
  • 75