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.
| Author |
Topic |
|
masterdineen
Aged Yak Warrior
550 Posts |
 Posted - 2012-12-19 : 17:08:16
|
| Hello thereI 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?RegardsRob |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-19 : 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
52326 Posts |
Posted - 2012-12-19 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-12-20 : 04:08:50
|
| Hello allIt 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-20 : 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
550 Posts |
Posted - 2012-12-20 : 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
52326 Posts |
Posted - 2012-12-20 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
SP to create table via CSV