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 |
|
dbonneau
Yak Posting Veteran
50 Posts |
 Posted - 2008-12-16 : 09:43:15
|
| Hi, I have a question about bulk insert. I created tables for two securities called EY and RY. Because I get those two securities in one text file, I need to seperate those two securities and put them in the each table. I dont' need to store START SECURITY|EY|, END SECURITY|EY| 0 |START SECURITY|RY|,END SECURITY|RY| 0 | that are in first line and the last line of each security. could you please help me writing Bulk Insert logic ? Thanks !create table [dbo].[EY](Instrument Char(20) not null,TradeDate datetime not null, TradeTime varchar(20) not null,TradePrice float not null,Vol int not null,Cond_Code char(5) not null,) create table [dbo].[RY](Instrument Char(20) not null,TradeDate datetime not null, TradeTime varchar(20) not null,TradePrice float not null,Vol int not null,Cond_Code char(5) not null,) Bulk insert [dbo].[EY] from 'C:\EYRY'; with ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' ) START SECURITY|EY|EY omy|12/12|17:00:00|124.34375|||SETT|EY omy|12/12|16:59:58|124.484375|1|||EY omy|12/12|16:59:58|124.46875|1|||EY omy|12/12|16:59:57|124.453125|6|||EY omy|12/12|16:59:29|124.453125|1|||EY omy|12/12|16:59:26|124.453125|12|||EY omy|12/12|16:59:22|124.46875|1|||EY omy|12/12|16:59:19|124.46875|1|||EY omy|12/12|16:59:10|124.46875|1|||EY omy|12/12|16:59:08|124.484375|1|||EY omy|12/12|16:59:03|124.484375|1||| . . .EY omy|10/23|22:02:28|113.328125|||LB|EY omy|10/23|22:02:00|113.3125|||LB|EY omy|10/23|22:01:58|113.296875|||LB|EY omy|10/23|22:00:34|113.28125|||LB|EY omy|10/23|21:58:41|113.265625|||LB|EY omy|10/23|20:25:38|113.25|||LA|EY omy|10/23|20:23:48|113.265625|||LA|EY omy|10/23|20:22:36|113.359375|||LA|EY omy|10/23|18:33:15|113.59375|||LA|END SECURITY|EY| 0 |START SECURITY|RY|RY omy|12/12|17:00:00|125.34375|||SETT|RY omy|12/12|16:59:58|125.484375|1|||RY omy|12/12|16:59:58|125.46875|1|||RY omy|12/12|16:59:57|125.453125|6|||RY omy|12/12|16:59:29|125.453125|1|||RY omy|12/12|16:59:26|125.453125|12|||RY omy|12/12|16:59:22|125.46875|1|||RY omy|12/12|16:59:19|125.46875|1|||RY omy|12/12|16:59:10|125.46875|1|||RY omy|12/12|16:59:08|125.484375|1|||RY omy|12/12|16:59:03|125.484375|1||| . . .RY omy|10/23|22:02:28|112.328125|||LB|RY omy|10/23|22:02:00|112.3125|||LB|RY omy|10/23|22:01:58|112.296875|||LB|RY omy|10/23|22:00:34|112.28125|||LB|RY omy|10/23|21:58:41|112.265625|||LB|RY omy|10/23|20:25:38|112.25|||LA|RY omy|10/23|20:23:48|112.265625|||LA|RY omy|10/23|20:22:36|113.359375|||LA|RY omy|10/23|18:33:15|113.59375|||LA|END SECURITY|RY| 0 | |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
dbonneau
Yak Posting Veteran
50 Posts |
Posted - 2008-12-16 : 14:46:14
|
quote:
Originally posted by visakh16
did you try using format file?http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html
Thank you so much for your reply. But Is there any other way to do that ? Since I am kind a newbie in SQL, I am having hard time applying the contents of the link you gave me. I heard there is a way to do it with sql loader... Does MS SQL SERVER 2005 has that functionality too ? Thanks ! |
 |
|
|
|
|
|
|
|
Bulk Insert Help