Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vishu_av
Yak Posting Veteran
69 Posts |
 Posted - 2007-06-12 : 04:37:44
|
| Hi All,I have a simple table script as below..create table test(ID numeric identity(1,1),FirstName varchar(12),LastName varchar(12))i tried inserting data using following statement...BULK INSERT TXS_TXNSTAGING FROM 'd:\csv\trscsv1.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' FORMATFILE = 'd:\csv\bcp.txt' )My bcp.txt file reads as below..8.021 SQLCHAR 0 0 "," 2 FIRSTNAME ""2 SQLCHAR 0 0 "\r\n" 3 FIRSTNAME ""Can i create a bcp.txt which only says i would like to neglect first row as it is my identity column and still work with above bulk insert statement.Thanks in advance |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-12 : 10:00:53
|
| The format file will define the terminators so you don't need those in the statement.>> i would like to neglect first row as it is my identity column You mean you want to ignore the first column not first row I guess.seehttp://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
vishu_av
Yak Posting Veteran
69 Posts |
Posted - 2007-06-13 : 02:54:20
|
| Hi nr,Pardon me,I meant omitting first column.What i understood from above link is that i have to mention all the other fields except for the first field( Identity column) in the bcp file format if i am going to omit it.Please, do correct if my interpretation in wrong.Thanksvishu |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-13 : 09:46:07
|
| Depends what you need.In the format file specify all fields in the text file. If you don't want to import a field then set the destination column number to 0.If you don't want to populate a destination column then don't put that column number in the format file.If you don't have the identity in the text file then it's easiest to create a view on the destination table which omits the identity and bulk insert into that view without a format file.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
vishu_av
Yak Posting Veteran
69 Posts |
Posted - 2007-06-14 : 04:28:25
|
| Thanks nr, It was of greatest helpvishu |
|
|
|
|
|
|
bulk insert