Bulk Insert into table with more columns

Posted on 2007-07-26
Last Modified: 2008-01-09
I do a daily import of millions of rows so would prefer to use BULK INSERT.  

I would like to import the csv files into a table with two extra columns not in the source files, but it seems as though BULK INSERT does not allow this.  Are there any functions or properties of BULK INSERT that  allow this.  

It is imperative to use BULK INSERT over normal import as this reduces the import time from hours to minutes.

I've thought about importing into a temporary table on the SQL server db, and then moving this across to the actual table, but this also slows the daily import process quite considerably

Any ideas?
Question by:charb0y
    LVL 11

    Expert Comment

    1)  I suppose the csv files are generated somehow? Maybe you could add two columns just to get it imported?
    2) Maybe you could drop the columns, do the insert, and then recreate the columns? This would only work if there is no data in the columns of course.
    LVL 12

    Accepted Solution

    Doin't need drop columns or other crazy think.
    Read help and look fro BULK INSERT ...WITH FORMATFILE=...
    FORMATFILE  is file where you can simply specify which columns miss or should be omited.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now