Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Insert table via csv with non matching columns?

Posted on 2007-08-01
3
Medium Priority
?
303 Views
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!

Nellster
0
Comment
Question by:nellster
  • 2
3 Comments
 
LVL 4

Expert Comment

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

Author Comment

by:nellster
ID: 19618177
Thanks for the reply bamboo7431.

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

thanks,

Nellster
0
 
LVL 4

Accepted Solution

by:
bamboo7431 earned 1000 total points
ID: 19619097
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

577 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