SQL Server 2005 :Insert a flat file in a OLTP table

This article will show you 4 different ways to import a flat file in a SQL Server table.

The first question that you must have is:" Have I to manipulate some of my data?"

If no, you can use the following methods:

BULK INSERT

BULK INSERT is a T-SQL command that allows to import data from a flat file into a table.

Here an example:

BULK INSERT .dbo.[TheDestinationTableName] FROM 'FullPathToTheFlatFile'
WITH
(
FIELDTERMINATOR = '',
ROWTERMINATOR = '\n'
)

Here the end character between each column is '' and the final character to pass to the next record is '\n'

bcp

bcp can copy Microsoft® SQL Server™ data to or from a data file. bcp is a command prompt utility and not a T-SQL command. bcp is used in a batch file or in your .NET application, for instance. The bcp utility is written using the ODBC bulk copy application programming interface (API).

Import/Export Tasks

As the Copy tool of SQL Server 2005, it exists also an import tool in it.

From SSMS, connect you on your destination database (which one that contains the destination table). Right click on this database and select Tasks --> Import Data... The Sql Server Import and Export Wizard window opens itself.

Select Flat File Source as Datasource. Specify the File path and the formatting. You can preview the result in the Columns tab for the columns name and the data in the Preview tab.

Click Next and follow the steps.

if you have a manipulation:

In this case, you will need to use a tool that will make the changes. This tool is SSIS. SSIS is provided with the Developer and Enterprise version of SQL Server 2005.With SSIS you can create a connection to your flat file, make some changes on the values in your DataFlow and importing them in a table thanks to an OLE DB connection.

I hope that this post will help you ...