Importing data from flat file to SQL Table

Question

Post reply

Importing data from flat file to SQL Table

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
July 15, 2009 at 3:24 pm

#384995

Is it possible to import data from flat file to sql table through SQL Command without creating table first.

For example we use Import/Export wizard in Management Studio to import data from flat file to sql table and destination tables gets created automatically.

I would like to import data from flat file and destination table gets created automatically based on the data in flat file.

Can we do ths same thing in TSQL. ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

I am a DBA SSC Veteran Points: 228 More actions · Answer 1

No. The only reason the "import/export wizard" "automatically" create the table is because in the underlying package it has "create table" tasks.

In the meantime, if you have lots of data, you can write T-SQL to: 1) create the destination table, and 2) "bulk insert" to load data from the flat file to the destination table.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

Let me expain our process. May there there is another way to do that.

- We run nightly job which converts data from Cache database and to flat files.

- We run another job which coverts data from flat files to sql tables.

- While converting data from flat files to sql tabels, we sometimes gets right truncation error because user in Cache database write more data then sql column can handle.

- When we get a right truncation error, we don't fail the whole batch, we let the process continue inserting into the sql table and rest of the records which are not inserted stores in another flat file so that later we can manually insert the data by increasing the field length in sql table.

- We then use SSIS package to insert data (which was not inserted because of the field length) from flat file to sql server.

I just wanted to make this process simple and created stored procedure which will automatically increase the length of the sql field if there is any right truncation error.

I just wanted to add more in this process that if the there is right truncation error then get the flat file and load into temp table automatically, remember at this time we don't know the structure of the flat file.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Is there any specific reason you have to use flat files (e.g. large amount of data using the performance gain of bcp)?

Would it be possible to add the Cache database as a linked server and do the import directly from the source (eliminating the step to generate the flat file)?

Regarding table definition on the target system: you could export the column definition from the source and use this information in the target system to create the table prior to data import. This would ensure the target column size to be identical to the source, eliminating the data truncation error.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 4

We cannot do the remote link from Cache database to sql server. as far as I know there are no drives avaiable for that and also it's a big system and impossible to change.

You also mentioned that exporting the column definition from the flat files. How can you do that with using export/import in management studio in sql 2005.

If you use bulk insert and BCP, you have to have a table first.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 5

Sorry about the last reply. I totally screwed up the english.

here is the updated reply

We cannot do the remote link from Cache database to sql server. as far as I know there are no Drivers avaiable for that and also it's a big system and impossible to change.

You also mentioned that exporting the column definition from the flat files. How can you do that without using export/import in management studio in sql 2005.

If you use bulk insert and BCP, you have to have a table first.

Lowell SSC Guru Points: 323482 More actions · Answer 6

here's another option;

you can create a linked server for a Folder on the server, and each text file becomes a table. every file that is comma delimited can be seen by running sp_tables_ex [linkedservername]

because of that, no table gets truncated, the file remains the same, and you can then query the table to insert into a table, etc.

only wierdness is a filename can only have ONE period in it., so a filename like "converted.source.txt" would need to be renamed to get rid of one of the periods.

once it's linked you can use the INSERT INTO syntax to create a table on the fly, ie



SELECT *

   INTO MYNEWTABLE

   FROM TxtSvr...[xmlmap#txt]

I really like using linked servers.

for reference, here is the syntax:



--#################################################################################################

--Linked server Syntax for Folder Full Of Text Files

--#################################################################################################

--add a folder as a linked server to access all .txt and .csv files in the folder

DECLARE @server     sysname,

        @srvproduct nvarchar(256),

        @provider   nvarchar(256),

        @datasrc    nvarchar(100),

        @location   nvarchar(100),

        @provstr    nvarchar(100),

        @catalog    sysname,

        @sql        varchar(1000)

SET @server = N'TxtSvr'

SET @srvproduct = N'Jet 4.0'

SET @provider = N'Microsoft.Jet.OLEDB.4.0'

SET @datasrc = N'C:\'

set @provstr    = 'Text'

EXEC sp_addlinkedserver  @server,@srvproduct,@provider,@datasrc,NULL,@provstr

--===== Create a linked server to the drive and path you desire.

   EXEC dbo.sp_AddLinkedServer TxtSvr,

        'Jet 4.0',

        'Microsoft.Jet.OLEDB.4.0',

        'C:\',

        NULL,

        'Text'

GO

--===== Set up login mappings.

EXEC dbo.sp_AddLinkedSrvLogin TxtSvr, FALSE, NULL, Admin, NULL

GO

--===== List the tables in the linked server which is really a list of

     -- file names in the directory.  Note that the "#" sign in the

     -- Table_Name is where the period in the filename actually goes.

   EXEC dbo.sp_Tables_Ex TxtSvr

GO

--===== Query one of the files by using a four-part name.

SELECT *

   FROM TxtSvr...[xmlmap#txt]

--===== Drop the text server

   EXEC dbo.sp_DropServer 'TxtSvr', 'DropLogins'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 7

I didn't mention to get the column information from the flat file you've been talking about.

What I've been talking about is to export the relevant column definitions from the Cache database in a separat file - if that's possible at all.

Then I would import this file into SQL server and create the target table based on those columns.

After that I'd import the flat file using bcp.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]