 | |
Re: Appending Data via BCP
microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Appending Data via BCP -
06-26-2009
, 01:49 PM
|
Accesshelp (Accesshelp (AT) discussions (DOT) microsoft.com) writes: I append/import data from a csv file into a table in SQL Server via BCP. The twist is the table that I append into has two extra columns than the number of columns in the csv file. I use the following code to import the data, and I got an error and it would not import. bcp myDB.dbo.my_Table in c:\File_2009.csv -c -t , -T -S SERVERNAME\NAMEDINSTANCE Is there something that I need to do import the data into a table with two extra columns? The two extra columns will be blank and will be populated with update query. I am using SQL Server 2005 Express. You need to use a format file in this case. Here is a sample format file for your case, assuming that your file have 4 fields, and your table has 6 columns, and that the extra columns are columns 2 and 5 9.0 4 1 SQLCHAR 0 0 "," 1 col1 "" 2 SQLCHAR 0 0 "," 3 col3 "" 3 SQLCHAR 0 0 "," 4 col4 "" 4 SQLCHAR 0 0 "\r\n" 6 col6 "" The first line is the version number of the format. The second is the number of fields in the file. The following lines describe the fields. The first field in the field-desciprion is the field number, a running number from 1 and up. The next is the data type for the *field* in the file. If you import a text file, this is always SQLCHAR or SQLNCHAR, the latter for Unicode files. The the third field is the prefix length, which you only use with binary data files. The fourth field is the length of the field. You would used it for fixed-length format. The fifth field is the delimiter, which is comman for the first three fields, and CR-LF for the last. The sixth field gives the column number in the target table, with 1 for the first column. By specifying 0, you can opt to not import a certain field in the file. The seventh field holds the column name, but this is informational only. BCP ignores it. The eighth field finally is the collation of the data. If you specify "" as I have done here, SQL Server will apply some default. You use the -f option to specify a format file. -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx |
#2
| |||
| |||
|
|
In addition, you may be able to BCP the data into a view on that table. The view can exclude the two extra columns. Linchi "Accesshelp" wrote: Hello all, I append/import data from a csv file into a table in SQL Server via BCP. The twist is the table that I append into has two extra columns than the number of columns in the csv file. I use the following code to import the data, and I got an error and it would not import. bcp myDB.dbo.my_Table in c:\File_2009.csv -c -t , -T -S SERVERNAME\NAMEDINSTANCE Is there something that I need to do import the data into a table with two extra columns? The two extra columns will be blank and will be populated with update query. I am using SQL Server 2005 Express. Thanks. |
#3
| |||
| |||
|
|
Sure, as I discuss in the book, I recommend that you BCP into a work table and run validating queries to move the data into production table(s). Another approach is to use SqlBulkCopy from ADO.NET where the initial query that selects the columns to import can be modified to include just those columns of the source table that you need. I expect this can be done in an SSIS script as well. -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition) http://betav.com http://betav.com/blog/billva __________________________________________________ __________________________________________ "Accesshelp" <Accesshelp (AT) discussions (DOT) microsoft.com> wrote in message news:8680B4EE-D16D-42AD-853F-2A58CECBF780 (AT) microsoft (DOT) com... Hello all, I append/import data from a csv file into a table in SQL Server via BCP. The twist is the table that I append into has two extra columns than the number of columns in the csv file. I use the following code to import the data, and I got an error and it would not import. bcp myDB.dbo.my_Table in c:\File_2009.csv -c -t , -T -S SERVERNAME\NAMEDINSTANCE Is there something that I need to do import the data into a table with two extra columns? The two extra columns will be blank and will be populated with update query. I am using SQL Server 2005 Express. Thanks. |
#4
| |||
| |||
|
|
Thanks for the code and detail explanations. Unfortunately, I don't get a chance to see your post until today. Unless I am missing something, how is the code going to know where to grab the source file that is importing from? Can you help me more? |
of the file you are importing. But it does not say where the source
data is. This you specify as the third parameter to BCP. (The first is
target table, the second is the direction, in/out.)
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.