locked
BULK INSERT into "new" table possible? RRS feed

  • Question

  • Hello,

    I'm trying to find a way (outside of OPENROWSET) to import data into a SQL table where the source CSV file has varying column headers.

    Is it possible to run a BULK INSERT command where the destination table doesn't already exist?  If so, can you please provide an example?

    Thanks for your help!

    Matt  :)

    Friday, February 4, 2011 4:59 PM

Answers

  • 1.      „Create table“ a temporary table with one column (varchar(max))

    2.      „bulk insert“ in this table without seperator

    3.      Split first row of the temporary table to get the columnnames

    4.      „Create table“ with this columnnames

    5.      Insert the rest oft the rows

     

     

     

     

    • Marked as answer by mmw_pdx Thursday, February 17, 2011 12:16 AM
    Thursday, February 10, 2011 10:41 AM

All replies

  • Hello,

    I'm trying to find a way (outside of OPENROWSET) to import data into a SQL table where the source CSV file has varying column headers.

    Is it possible to run a BULK INSERT command where the destination table doesn't already exist?  If so, can you please provide an example?

    Thanks for your help!

    Matt  :)


    The better way i can suggest is -

    use import - export wizard.

    it will have an option to create new table

     

    Very Nice and detailed explanation about import export wizard

    http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm

     

     

    You can also create(then modify) the SSIS after running the wizard to import it on daily basis.


    Thanks, Cool Mind -- If you find my answer helpful, please mark it as Answer.
    Friday, February 4, 2011 5:03 PM
  • you can use BULK INSERT but table should be created before.

    here is the example


    create table Test(
    ModuleId varchar(20),
    EntryDate varchar(50), 
    EntryTime varchar(50),
    )

    BULK INSERT Test
       FROM 'R:\GoldenTicket_all_entries.csv'
       WITH
         (
            ROWTERMINATOR = '\n',FIELDTERMINATOR = ','
          )

    Friday, February 4, 2011 5:04 PM
  • Thanks for the replies.... but neither will work.

    The key here is the "varying column headers." in the CSV source.  Each time the package runs, it will have to deal with the fact that it won't know the names of the columns in the source CSV file going into it.  That's why it needs to be able to create the destination file dynamically.

    Any other ideas?

    :)

     

    Friday, February 4, 2011 5:07 PM
  • I know you said no OPENROWSET but I thought I'd throw this out there.

    SELECT * INTO myNewTable

    FROM OPENROWSET(...)

    Adam


    Ctrl+Z
    Friday, February 4, 2011 5:07 PM
  • Thanks "OkThen".  I wish I could use OPENROWSET!  The DBAs have security concerns and won't allow it.

    Any other ideas?

    :)

    Friday, February 4, 2011 5:13 PM
  • You're going to have to create an SSIS package/CLR because the columns will always be different. You'll need some procedural code to parse it out.

    Adam


    Ctrl+Z
    Friday, February 4, 2011 5:39 PM
  • This is quite common in ETL processes,

    there is always a good way, when you export data, export all headers in a separate file. Then you can design your table schema or SSIS package accordingly.

    HTH.


    Sevengiants.com
    Friday, February 4, 2011 5:58 PM
  • Thanks "OkThen".  I wish I could use OPENROWSET!  The DBAs have security concerns and won't allow it.

    Any other ideas?

    :)


    Easy! Then it is not your "yob". Have the DBA-s do it.

    There are only two ways to create a table:

    1. CREATE TABLE

    2. SELECT INTO

    SELECT INTO link: http://www.sqlusa.com/bestpractices2005/createemptytable/

    You can use dynamic SQL: http://www.sqlusa.com/bestpractices/dynamicsql/

    You have to dig out the meta data from the csv file first. That is how SSIS import/export Wizard is working.

     

     

     


     

    Kalman Toth, Business Intelligence Developer; SQL 2008 GRAND SLAM

    Thursday, February 10, 2011 10:02 AM
  • 1.      „Create table“ a temporary table with one column (varchar(max))

    2.      „bulk insert“ in this table without seperator

    3.      Split first row of the temporary table to get the columnnames

    4.      „Create table“ with this columnnames

    5.      Insert the rest oft the rows

     

     

     

     

    • Marked as answer by mmw_pdx Thursday, February 17, 2011 12:16 AM
    Thursday, February 10, 2011 10:41 AM
  • create table Test(column1 datatype,......,columnX datatype
    )

    BULK INSERT Test
       FROM 'file path'
       WITH
         (
            ROWTERMINATOR = '\n',FIELDTERMINATOR = ',' 
          )

    Thursday, February 10, 2011 10:55 AM
  • Brad Schulz has an interesting recent blog about migrating CSV files, you may check it out

    So, You Want To Read CSV Files, Huh?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, February 15, 2011 2:53 PM
  • Wizard is NOT a better way at all.

    Import wizard requires manual activation (unless you go down the route of saving as package and running in a job etc.

    You didn't answer his question at all...

    Is it possible to run a BULK INSERT command where the destination table doesn't already exist? 

    Thursday, March 5, 2020 12:29 PM
  • Is it possible to run a BULK INSERT command where the destination table doesn't already exist? 

    You posting in a thread that is nine years old...

    The answer to the question is: no, that is not possible.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, March 5, 2020 10:52 PM
  • For Praktikant solution to work there can be no embedded delimiters in the column data. for example, it will not work for the example below. 

    "Smith, John", "M"

    Friday, March 6, 2020 5:47 PM