| Author |
 Topic  |
|
|
masterdineen
Aged Yak Warrior
United Kingdom
517 Posts |
 Posted - 12/19/2012 : 17:08:16
|
Hello there
I want to create a SP that will create a table based on
how many column headings the csv has in the top row.
How would i go about doing this?
Regards
Rob |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/19/2012 : 19:38:59
|
Does it necessarily have to be a stored proc? If you use Import/Export Wizard to import the data into the database, you have the option of specifying that the table be created.
If you do need to create a table in a database based on a csv, the most straightforward approach that I can think of is to write a .Net program or Powershell script to read the csv header, parse the column names, compose a DDL script based on that, connect to the database server using SMO or ADO (or some other means) and run the DDL script. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 12/19/2012 : 22:00:15
|
i dont think creating table inside proc is a good idea. So what happens during subsequent runs? Are you checking for existence of table before creating it? also is it a permanent persisting table or just a temp table? If former, I would prefer keeping creation script separate or configured inside Import/Wizard as Sunita suggested
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
masterdineen
Aged Yak Warrior
United Kingdom
517 Posts |
Posted - 12/20/2012 : 04:08:50
|
Hello all
It was just an idea for automating csv's i get in work and having to quickly import all data into a table, i know how to dump into a temp table.
wondering how you would create a table based on the column names in the CSV file.
Would i be best in writing a VB prgram that would point to a file. to list all the column headings,
then define the datatype, then call a sp to create the table using the headings / datatypes as parms. ? |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/20/2012 : 06:22:40
|
If you need to do this process of creating the table on a regular basis, or if you need to do it for a large number of files, using a VB program might be the path of least resistance.
If it is infrequent, or if you have on one or two files, use Import/Export Wizard (right click on the database name in Object Explorer, Tasks -> Import Data). |
|
|
|
masterdineen
Aged Yak Warrior
United Kingdom
517 Posts |
Posted - 12/20/2012 : 08:46:50
|
ok thank you, just looking for a solution to automate a process and cut down some time.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 12/20/2012 : 11:05:05
|
quote:
Originally posted by masterdineen
ok thank you, just looking for a solution to automate a process and cut down some time.
Why do you want to automate create table process? Ideally you should be doing it one time right?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
SP to create table via CSV
