How to Access Flat Files From SQL Server-Stored Procedures

T-SQL is usually called to query tables within an SQL Server database. T-SQL can also be used to import and query flat files. Flat files are comma-delimited files that store records in plain text. The files can be imported and used for database procedures as well. Once the file has been saved in a directory on the computer, it can be imported using an SQL Server stored procedure with only a few steps.

Instructions

1. Create a temporary table to store the data. This table should represent the data that is located in the CSV file. Below is a sample of a table created for the CSV data:
   create table myCSVtable
   (id int,
   first_name varchar(25),
   last_name varchar(25),
   address varchar(100))

2. Insert the CSV data into a temp table. The following code retrieves the data from the flat file and imports it into the new table created in Step 1.
   bulk
   insert myCSVtable
   from 'c:\mytestdata.csv'
   with
   (
   fieldterminator = ',',
   rowterminator= '\n'
   )
   The "fieldterminator" string sets the delimiter for the fields in the file. The "rowterminator" string of "\n" is typical for windows files. It means a new record is determined by a carriage return.

3. Select the imported information from the temporary table. Now that the data is imported from the file, the table can be used to manipulate and read the data. The following line reads data from the imported file:
   select * from myCSVtable

References

• SQL Authority