how to transfer from flatfiles to sql server DB

[rite2sekhar]

Hi
I want to know how to transfert data from flatfiles to sqlserver DB

explain with an example Manual procedure not by im port method


Urgent Plz

Regds

[admin]

Right click on the database and you should see an option to import , select that, then you will be given options about the data source, select text file from that list and browse to your file. click next to select the destination db and the trabsformations.

if you have column names, you can check the box in wizard, import uses them as column names.

[sanereddy]

You can use the bulk insert creating the format files with the layout of your import file....

search for bulk insert in books online, the documentation is pretty good.


here is the sample format file and the stored procedure to read the file and load in SQL Server ....


Hope this helps.

Format File : save as text file and keep on the server some where to be accessable by SQl Server.

sample.fmt

8.0
5
1 SQLCHAR 0 1 "" 1 FIELD1 ""
2 SQLCHAR 0 5 "" 2 FIELD1 ""
3 SQLCHAR 0 4 "" 3 FIELD1 ""
4 SQLCHAR 0 4 "" 4 FIELD1 ""
5 SQLCHAR 0 10 "\r\n" 5 FIELD1 ""

This is the stored procedure which reads the import file and uses the format file , loads the data in toe sample_data table.

CREATE procedure sample_load as

BULK INSERT northwind..Sample_data FROM 'F:\Microsoft SQL Server\sample.txt'
WITH (
FORMATFILE = 'F:\Microsoft SQL Server\sample.fmt'
)



Hope this helps.