Bulk insert issue

Question

Bulk insert issue

srikant maurya

Hall of Fame

Points: 3293
More actions
April 1, 2010 at 6:23 am

#222735

How to use bulk insert to impot the data . Sample data is below
1"11321993-00""SO"4"0410""SPRINGFIELD""1NE""NORTHEAST""11""CONNECTICUT"3780215174"00""LANE VALENTE INDUSTRIES""2009-01-02 00:00:00""0929""2009-01-02 00:00:00""0934""2009-01-02 00:00:00""0934"0"01""PICK UP""2009-01-05 00:00:00""2009-01-05 00:00:00""A348""LESTER G LABARRE"1.002.000.000.000.000.000.000.00"YES""YES""NO""NO""10TH"0"8H21""NORTH TEXAS PARTS HOUSE ACCT""81P"100.000.000.000.00"NO""Y""Y""NO""NO""W""YES""YES""NO"0.00"NO"0.000.000"BRENDAN O.""97369"3"I"12.00"20X25X1PL40""1 IN PLEATED FILTER"12.000.0000046.5600031.6800014.8800046.5600031.6800014.88000"K6002096""8503""AIR FILTERS"30"PARTS""HVAC - ACCESSORIES""AIR FILTERS""HVAC - ACCESSORIES""HVAC - ACCESSORIES""HVAC - ACCESSORIES""PART""UP5""UP034""UP1""UP5""UP5"3.880002.640002.64000"NO"0.000000.000000.00000"0""EACH""8M00""8H21""NO""NO""2009-01-05 00:00:00"0.00000"Y""YES""LANE VALENTE INDUSTRIES"3780"3780"000"8SC""SOUTHCENTRAL""86""DALLAS""LANE VALENTE INDUSTRIES""3594 BERKWOOD LANE""FRISCO""TX""75034""LANE VALENTE INDUSTRIES""5127216787""8M00""8H21""3780"0"10TH"0.000.000.00000"YES""2009-01-02 00:00:00""Q1"117842"FLANDERS PRECISIONAIRE""Y"0.00000"2009-01-05 04:00:01""2009-01-05 04:45:37""2009-01-05 04:46:01""CS3"
1"11321993-00""SO"4"0410""SPRINGFIELD""1NE""NORTHEAST""11""CONNECTICUT"3780215174"00""LANE VALENTE INDUSTRIES""2009-01-02 00:00:00""0929""2009-01-02 00:00:00""0934""2009-01-02 00:00:00""0934"0"01""PICK UP""2009-01-05 00:00:00""2009-01-05 00:00:00""A348""LESTER G LABARRE"1.002.000.000.000.000.000.000.00"YES""YES""NO""NO""10TH"0"8H21""NORTH TEXAS PARTS HOUSE ACCT""81P"100.000.000.000.00"NO""Y""Y""NO""NO""W""YES""YES""NO"0.00"NO"0.000.003"BRENDAN O.""97369"1"I"12.00"16X20X1PL40""1 IN PLEATED FILTER"12.000.0000038.1600025.9200012.2400038.1600025.9200012.24000"K6002096""8503""AIR FILTERS"30"PARTS""HVAC - ACCESSORIES""AIR FILTERS""HVAC - ACCESSORIES""HVAC - ACCESSORIES""HVAC - ACCESSORIES""PART""UP5""UP034""UP1""UP5""UP5"3.180002.160002.16000"NO"0.000000.000000.00000"0""EACH""8M00""8H21""NO""NO""2009-01-05 00:00:00"0.00000"Y""YES""LANE VALENTE INDUSTRIES"3780"3780"000"8SC""SOUTHCENTRAL""86""DALLAS""LANE VALENTE INDUSTRIES""3594 BERKWOOD LANE""FRISCO""TX""75034""LANE VALENTE INDUSTRIES""5127216787""8M00""8H21""3780"0"10TH"0.000.000.00000"YES""2009-01-02 00:00:00""Q1"117842"FLANDERS PRECISIONAIRE""Y"0.00000"2009-01-05 04:00:01""2009-01-05 04:45:37""2009-01-05 04:46:01""CS3"
1"11321993-00""SO"4"0410""SPRINGFIELD""1NE""NORTHEAST""11""CONNECTICUT"3780215174"00""LANE VALENTE INDUSTRIES""2009-01-02 00:00:00""0929""2009-01-02 00:00:00""0934""2009-01-02 00:00:00""0934"0"01""PICK UP""2009-01-05 00:00:00""2009-01-05 00:00:00""A348""LESTER G LABARRE"1.002.000.000.000.000.000.000.00"YES""YES""NO""NO""10TH"0"8H21""NORTH TEXAS PARTS HOUSE ACCT""81P"100.000.000.000.00"NO""Y""Y""NO""NO""W""YES""YES""NO"0.00"NO"0.000.000"BRENDAN O.""97369"2"I"12.00"20X20X1PL40""1 IN PLEATED FILTER"12.000.0000042.3600028.8000013.5600042.3600028.8000013.56000"K6002096""8503""AIR FILTERS"30"PARTS""HVAC - ACCESSORIES""AIR FILTERS""HVAC - ACCESSORIES""HVAC - ACCESSORIES""HVAC - ACCESSORIES""PART""UP5""UP034""UP1""UP5""UP5"3.530002.400002.40000"NO"0.000000.000000.00000"0""EACH""8M00""8H21""NO""NO""2009-01-05 00:00:00"0.00000"Y""YES""LANE VALENTE INDUSTRIES"3780"3780"000"8SC""SOUTHCENTRAL""86""DALLAS""LANE VALENTE INDUSTRIES""3594 BERKWOOD LANE""FRISCO""TX""75034""LANE VALENTE INDUSTRIES""5127216787""8M00""8H21""3780"0"10TH"0.000.000.00000"YES""2009-01-02 00:00:00""Q1"117842"FLANDERS PRECISIONAIRE""Y"0.00000"2009-01-05 04:00:01""2009-01-05 04:45:37""2009-01-05 04:46:01""CS3"
1"11303410-00""SO"5"1302""BELTSVILLE""2MA""MID ATLANTIC""21""WASHINGTON"1612199811"00""ATS MECHANICAL SERVICES, INC""2008-12-29 00:00:00""1228""2008-12-29 00:00:00""1229""2008-12-29 00:00:00""1229"2"01""PICK UP""2008-12-29 00:00:00""2008-12-29 00:00:00""B498""ALAN GOFF"1.002.000.000.000.000.000.000.00"YES""YES""NO""NO""COD"0"2R10""TONY HOWARD""21R"100.000.000.000.00"NO""Y""Y""NO""NO""W""YES""YES""NO"0.00"YES"23.8223.821"TRK STK"1"I"1.00"P694-700-2-1""HOT SURFACE IGNITER/FLAT"1.000.0000022.4700016.849365.6206422.4700016.849365.62064"K6001656""8407""MISC HEATING COMPONENTS"30"PARTS""HEATING COMPONENTS""MISC HEATING COMPONENTS""HEATING COMPONENTS""HEATING COMPONENTS""HEATING COMPONENTS""PART""UP4""UP031""UP1""UP4""UP4"22.4700016.8493616.84936"NO"0.000000.000000.00000"0""EACH""2R10""2R10""NO""NO""2008-12-29 00:00:00"0.00000"Y""YES""ATS MECHANICAL SERVICES, INC"1612"130

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

because the data is quote delimited, you'll need to use a format file, i think; BOL or a google search will be your best bet to help you research and build the file; what you pasted ended up as one long ugly paragraph.

the format file tells SQL all the fields defined in the file, as well as each fields delimiters.

if you have the table to be imported already in SQL, you can use bcp to build a format file, and then tweak it if needed for the double quotes.

when i pasted your snippet, the data appears to be delimited with a single space between fields; spaces inside the dblquotes don't count.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!