Insert table via csv with non matching columns?

Posted on 2007-08-01
Last Modified: 2010-03-19
Hi all,

I am building a solution using SQL Server Express 2005. Part of this solution will be the ability to bulk upload a database table from a csv file once a day. The csv file will contain approximately 200 rows with about 50 fields in each row. The database table will contain columns that will not be present on the csv.

I am relatively new to "bulk inserts" and updating from csv/text files so could appreciate some assistance as to the best way of approaching this. I have imported from a CSV to MS Access before and could manipulate and match cvs columns to database table columns but I'm not sure as to the best way to do it in SQL 2005.

Many thanks for assistance in advance!

Question by:nellster
    LVL 4

    Expert Comment

    If it's only 200 rows, BULK INSERT into a #TempTable and then select the columns you need into your target table

    Author Comment

    Thanks for the reply bamboo7431.

    I get the TempTable storage but could you expand abit on the "Select columns into target table" bit?


    LVL 4

    Accepted Solution

    Say in the csv file you have fields Field1, Field2,...,Field50
    In your table you have fields ProperField11, ProperField12, ..., ProperField20
    What you'll do is:
    INSERT INTO MyTable(ProperField11, ProperField12, ..., ProperField20 )
    SELECT Field11, Field12,..., Field20
    FROM  #TempTable

    That way you only select the fields that you need.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    759 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

    13 Experts available now in Live!

    Get 1:1 Help Now