Insert multiple columns into one using bulk upload

Posted on 2008-11-03
Last Modified: 2013-11-30
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?

Question by:herschellrd
    LVL 17

    Expert Comment

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

    Expert Comment

    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

    LVL 75

    Accepted Solution

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how the fundamental information of how to create a table.

    737 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