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.
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

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

Any idea how this could be done?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
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.
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

Does your csv file have the identifiers you want to use it in?  If so, I'm guessing it'd look something like

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')
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

Anthony PerkinsCommented:
>>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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.