?
Solved

Insert multiple columns into one using bulk upload

Posted on 2008-11-03
4
Medium Priority
?
851 Views
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.
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.
0
Comment
Question by:herschellrd
3 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22873335
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
 
LVL 10

Expert Comment

by:laneduncan
ID: 22873341
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 22878906
>>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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 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