How can I insert an entire text file into a SQL Server database table without having to read a row at a time?
Solution: A very simple way to do this is by using a BULK insert SQL statement. This statement inputs a text file location + FileName and the specification on a delimiter for the records to import and then copies the data into a SQL Server table. The limitations to this approach are: • It does not create the table • All records need to match the destination table format. If any of them is incorrect the entire file is rejected. To use the bulk insert: Where: &(1:) = File Name with Path, all surrounded by Single-quote (FIELDS/FileName, 128) &(2:) = Table Owner &(3:) = Table Name Notes: If the application is NOT RUNNING ON THE SERVER and the path to the text file is through a relative path (with a drive letter like C:\file.txt) the process that performs the BULK insert WILL NOT work. In this case a Universal Naming Convention (UNC) path is required. To get it browse through My Network Places, Entire Network, and then through the folders to find the file. The UNC name for the path will be
Related Questions
- Is there a single database file housing all the data, data definitions, stored procedures, security, table relationships, etc. as in Microsoft’s SQL server?
- My Access database stores the file names of images in a text field. How do I use the ActiveX (OLE) controls to view the images in an Access form?
- How to use BULK insert to import data from file to SQL Server database?
