Insert multiple columns into one using bulk upload

I have 20 columns of data in csv format that currently go into 20 columns however I also want them all to go into one column as well using bulk upload and a DTS package / statement.
e.g.
The following data - 1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9,1,2 - currently goes into 20 different columns. I want it now to go into one but not so that it is in the one cell seperated by commas but as
COLUMN
1
2
3
4
5
6
7
etc

with an identifier from the csv file also in each row e.g.
COLUMN      ID
1                   abc
2                   abb
3                   acc
4                   abc
5                   add
6                   aee
7                   aff
etc

Any idea how this could be done?

Thanks.
herschellrdAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>however I also want them all to go into one column as well using bulk upload and a DTS package / statement.<<
That would seem counter-productive (not to mention violating one of principles of a good DBMS).  Why not make that column a computed column that concatenates all 20 columns.  That way you do not use more disk space and more relevantly maintain the integrity of your data.
0
 
HoggZillaCommented:
Use the package wizard. From DTS, Right Click on Data Transformation Services, All Tasks, Import Data. Define the source as a Text File, select the file and do NOT use a column delimeter. It will insert the data into a single column in a new table.
0
 
laneduncanCommented:
You can do this using BULK INSERT with a format file.  There are lots of examples of how to use one; I've got a simple example at http://lanestechblog.blogspot.com/2008/08/sql-server-bulk-insert-using-format.html

Does your csv file have the identifiers you want to use it in?  If so, I'm guessing it'd look something like
1,aaa,2,bbb,3,ccc,4,ddd
or
aaa,1,bbb,2,ccc,3,ddd,4

If that's the case, the short of it is that you'd set up a format file with two fields, both delimited by a comma, as below (assuming the first case).

Your bulk insert command would look like
bulk INSERT <tablename>FROM 'csvfilename' WITH (FIRSTROW = 1, FORMATFILE='formatfilename')
8.0
2
1 SQLCHAR 0 2 "," 1 columnnum SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 3 "," 2 id SQL_Latin1_General_Cp437_BIN

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.