SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SP to create table via CSV
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 12/19/2012 :  17:08:16  Show Profile  Reply with Quote
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

5155 Posts

Posted - 12/19/2012 :  19:38:59  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/19/2012 :  22:00:15  Show Profile  Reply with Quote
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/

Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 12/20/2012 :  04:08:50  Show Profile  Reply with Quote
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. ?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/20/2012 :  06:22:40  Show Profile  Reply with Quote
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).
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 12/20/2012 :  08:46:50  Show Profile  Reply with Quote
ok thank you, just looking for a solution to automate a process and cut down some time.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/20/2012 :  11:05:05  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000