July 15, 2009 at 3:24 pm
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. ?
July 16, 2009 at 8:25 am
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.
July 16, 2009 at 9:43 am
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.
July 16, 2009 at 11:32 am
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.
July 16, 2009 at 11:46 am
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.
July 16, 2009 at 12:00 pm
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.
July 16, 2009 at 12:08 pm
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
July 16, 2009 at 12:12 pm
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.
July 16, 2009 at 12:40 pm
when I run following which you have mentioned, I get following error
sp_Tables_ex txtsvr
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
July 16, 2009 at 12:55 pm
Msg 7403, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
I've searched the web and see this is an issue with the 64 bit version of
SQL 2005. Are there any updates from MS? Any plans on ing us out?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply