Dynamically creating a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamically creating a table

Author

Topic

Yellowdog
Starting Member

34 Posts

Posted - 2009-01-28 : 11:10:16

I am looking for some info on dynamically creating a table with unknown columns.

I am writing a an ssis package to search a directory for csv or txt files and import them into our database. The problem is there are going to be many different kinds of files in the directory so I need to find a way to put them into hold table and import them accordingly.

So, can someone point me in the right direction on how I can create a hold table that can be created on the fly without knowing the columns.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 12:29:26

cant you just use column info from file to create the table? using something like

SELECT * INTO #Temp1 FROM OPENROWSET(......)t

Yellowdog
Starting Member

34 Posts

Posted - 2009-01-28 : 15:18:17

quote:
Originally posted by visakh16

cant you just use column info from file to create the table? using something like

SELECT * INTO #Temp1 FROM OPENROWSET(......)t

If it is that easy I would like to find out how.

I have just spent some time looking into this and it seems that openrowset only provides a way to do a bulk one line insert into one column like so,

select * into #table1
from openrowset
(bulk 'C:\alliance\datx\archive\ClairmontServiceJan16.csv', single_blob) t

this puts all the data from the whole file into one column,

another option I have seen is to use

INSERT INTO #table1
SELECT *
FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\alliance\datx\archive\;
Extensions=csv;', 'SELECT * FROM bedardSalesJan23.csv')

unfortunatly I get an error

Msg 208, Level 16, State 0, Line 1
Invalid object name '#table1'.

Any ideas on the error here or possably another way to get this done?

Yellowdog
Starting Member

34 Posts

Posted - 2009-01-28 : 15:41:44

found it....

sometimes I feel so dumb...lol

select * INTO #table1
from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\alliance\datx\archive\;','select * from
ClairmontServiceJan16.csv') t

Yellowdog
Starting Member

34 Posts

Posted - 2009-01-29 : 12:49:08

Perhaps someone can help me a bit more with this.

The above query works great but I am having a problem using variables in place of the file name and was hoping someone can help me with it.

I have tried many things
select * from " + @[User::FileName] + ".csv
select * from (select files from myfiles where numnber = 1).csv

just to name a few.

If anyone has any Ideas on how this works much thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-29 : 12:53:00

use dynamic sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 12:53:32

are you using this expression inside ssis expression builder? then i think it should be

"select * from " + @[User::FileName] + ".csv"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-29 : 12:56:36

[code]declare @sql_string varchar(1000),@filename varchar(1000)
set @filename='filename.csv'
set @sql_string='
INSERT INTO #table1
SELECT *
FROM OPENROWSET (''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\alliance\datx\archive\;
Extensions=csv;'', ''SELECT * FROM '+@filename+'bedardSalesJan23.csv'')
'

exec (@sql_string)[/code]

Yellowdog
Starting Member

34 Posts

Posted - 2009-01-29 : 14:44:49

Thanks for the replies,
Unfortunately I don't think I explained myself really well.

This works great
select * INTO #table1
from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\alliance\datx\archive\;','select * from
ClairmontServiceJan16.csv') t

it takes a file and imports it into a temp table #table1.

I am writing an ssis package that loops through a folder and is going to import many .csv files.
The for each loop gets the file name and puts it in a table and I am trying to get that file name and insert it in the last part of the above query so I can process many files with one ssis package.

visakh16, I dont think there is a way that I can add the entire query into expression builder due to the nature of the query and I cannot seem to get the dynamic sql to run, I may just be missplaceing a quotation mark or two.

If this helps clear things up I am more than happy to hear what you all have to say,
and thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 02:21:32

see this to understand how to get file names dynamically

http://bisqlserver.blogspot.com/2008/08/ssis-dynamic-file-name-load.html

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/SSIS_3A00_-Enumerating-files-in-a-Foreach-loop.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources