This is a discussion on I give up on this one... within the SQL Server forums, part of the Microsoft SQL Server category; --> I have tried and tried to get this to work and I'm doing something dumb. I'm going to include ...
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
| ||||
| I have tried and tried to get this to work and I'm doing something dumb. I'm going to include sample data and my format file. Can someone help me figure out my dumb mistake on a bulk insert using a format file? Don't worry about data types, I'll handle that later. Thanks a lot!!! CSV File: ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,ADDRESS1,CITY, STATE,ZIP,PHONE,COMPET ITOR,PLAN,HARVESTDATE,EIDs,EIDCount,sourceIds,minS core,maxScore,hasContr act,matchFilter,matchFilterBGR,matchFilterCAS,matc hFilterMTV,forceMatch, forceMatchUserId,paymentPlatforms,matchStatusColor ,recordId,dupId,Networ kComparedTo 1,name1,Family Practice General Practice,FP,PCP,address1,Deerfield Beach,FL,33442,phone1,C1,PPO,5/15/2004,1000067851,2,BADG_0874992,9,14.4, Y,Y,Y,Y,Y,0,,3,G,1, ,netcomp1 2,name2,Family Practice General Practice,FP,PCP,address2,Margate,FL,33063,phone2,C 2,PPO,5/15/2004,100006 7851,2,BADG_0874992,9,10,Y,Y,Y,Y,Y,0,,3,G,2, ,netcomp2 3,name3,General Practice,GP,PCP,address3,Deerfield Beach,FL,33442,phone3,C3,PPO,5/15/2004,1000067851,2,BADG_0874992(14.4 Y)|BADG_1901859,9,14.4,Y,Y,Y,Y,Y,0,,3,G,3, ,netcomp3 4,name4,Reproductive Endocrinology,OBEN,OB,address4,Davie,FL,33328,phon e4,C4,PPO,5/15/2004,10 00083687,1,CAS_650410436,10.3,10.3,Y,N,N,N,N,0,,0, Y,4, ,netcomp4 Format File: 8.0 31 1 SQLCHAR 0 1 "\r\n" 0 quote Latin1_General_CI_AS 2 SQLCHAR 0 3000 "," 1 Provider_Raw_ID Latin1_General_CI_AS 3 SQLCHAR 0 3000 "," 0 none_name Latin1_General_CI_AS 4 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS 5 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS 6 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS 7 SQLCHAR 0 3000 "," 0 none_Address Latin1_General_CI_AS 8 SQLCHAR 0 3000 "," 0 none_City Latin1_General_CI_AS 9 SQLCHAR 0 3000 "," 0 none_State Latin1_General_CI_AS 10 SQLCHAR 0 3000 "," 0 none_Zip Latin1_General_CI_AS 11 SQLCHAR 0 3000 "," 0 none_Phone Latin1_General_CI_AS 12 SQLCHAR 0 3000 "," 0 none_Competitor Latin1_General_CI_AS 13 SQLCHAR 0 3000 "," 0 none_Plan Latin1_General_CI_AS 14 SQLCHAR 0 3000 "," 0 none_HarvestDate Latin1_General_CI_AS 15 SQLCHAR 0 3000 "," 0 none_EIDs Latin1_General_CI_AS 16 SQLCHAR 0 3000 "," 5 EIDCount Latin1_General_CI_AS 17 SQLCHAR 0 3000 "," 0 SourceIds Latin1_General_CI_AS 18 SQLCHAR 0 3000 "," 6 minScore Latin1_General_CI_AS 19 SQLCHAR 0 3000 "," 7 maxScore Latin1_General_CI_AS 20 SQLCHAR 0 3000 "," 8 hasContract Latin1_General_CI_AS 21 SQLCHAR 0 3000 "," 9 matchFilter Latin1_General_CI_AS 22 SQLCHAR 0 3000 "," 10 matchFilterBGR Latin1_General_CI_AS 23 SQLCHAR 0 3000 "," 11 matchFilterCAS Latin1_General_CI_AS 24 SQLCHAR 0 3000 "," 12 matchFilterMTV Latin1_General_CI_AS 25 SQLCHAR 0 3000 "," 13 forceMatch Latin1_General_CI_AS 26 SQLCHAR 0 3000 "," 14 forceMatchUserId Latin1_General_CI_AS 27 SQLCHAR 0 3000 "," 15 paymentPlatforms Latin1_General_CI_AS 28 SQLCHAR 0 3000 "," 16 matchStatusColor Latin1_General_CI_AS 29 SQLCHAR 0 3000 "," 17 recordId Latin1_General_CI_AS 30 SQLCHAR 0 3000 "," 18 dupId Latin1_General_CI_AS 31 SQLCHAR 0 3000 "," 4 NetworkComparedTo Latin1_General_CI_AS Here's a script for the table! if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Provider_Results]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Provider_Results] GO CREATE TABLE [dbo].[Provider_Results] ( [Provider_Raw_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Category] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SpecialtyCode] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [NetworkComparedTo] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EIDCount] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [minScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [maxScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [hasContract] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [matchFilter] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [matchFilterBGR] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [matchFilterCAS] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [matchFilterMTV] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [forceMatch] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [forceMatchUserId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [paymentPlatforms] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [matchStatusColor] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [recordId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [dupId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO Thanks again. I am dying on this one. With everything included hopefully someone (smarter than me) can figure it out. Thanks, Andrew *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
| |||
| Might look at the solution Erland gave me... see subject "Bulk Insert Problems" HTH CBL "Andrew Slentz" <ajslentz@yahoo.com> wrote in message news:40cf845e$0$25534$c397aba@news.newsgroups.ws.. . > I have tried and tried to get this to work and I'm doing something dumb. > I'm going to include sample data and my format file. Can someone help > me figure out my dumb mistake on a bulk insert using a format file? > Don't worry about data types, I'll handle that later. Thanks a lot!!! > > CSV File: > > ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,ADDRESS1,CITY, STATE,ZIP,PHONE,COMPET > ITOR,PLAN,HARVESTDATE,EIDs,EIDCount,sourceIds,minS core,maxScore,hasContr > act,matchFilter,matchFilterBGR,matchFilterCAS,matc hFilterMTV,forceMatch, > forceMatchUserId,paymentPlatforms,matchStatusColor ,recordId,dupId,Networ > kComparedTo > 1,name1,Family Practice General Practice,FP,PCP,address1,Deerfield > Beach,FL,33442,phone1,C1,PPO,5/15/2004,1000067851,2,BADG_0874992,9,14.4, > Y,Y,Y,Y,Y,0,,3,G,1, ,netcomp1 > 2,name2,Family Practice General > Practice,FP,PCP,address2,Margate,FL,33063,phone2,C 2,PPO,5/15/2004,100006 > 7851,2,BADG_0874992,9,10,Y,Y,Y,Y,Y,0,,3,G,2, ,netcomp2 > 3,name3,General Practice,GP,PCP,address3,Deerfield > Beach,FL,33442,phone3,C3,PPO,5/15/2004,1000067851,2,BADG_0874992(14.4 > Y)|BADG_1901859,9,14.4,Y,Y,Y,Y,Y,0,,3,G,3, ,netcomp3 > 4,name4,Reproductive > Endocrinology,OBEN,OB,address4,Davie,FL,33328,phon e4,C4,PPO,5/15/2004,10 > 00083687,1,CAS_650410436,10.3,10.3,Y,N,N,N,N,0,,0, Y,4, ,netcomp4 > > > Format File: > 8.0 > 31 > 1 SQLCHAR 0 1 "\r\n" 0 quote Latin1_General_CI_AS > 2 SQLCHAR 0 3000 "," 1 Provider_Raw_ID Latin1_General_CI_AS > 3 SQLCHAR 0 3000 "," 0 none_name Latin1_General_CI_AS > 4 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS > 5 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS > 6 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS > 7 SQLCHAR 0 3000 "," 0 none_Address Latin1_General_CI_AS > 8 SQLCHAR 0 3000 "," 0 none_City Latin1_General_CI_AS > 9 SQLCHAR 0 3000 "," 0 none_State Latin1_General_CI_AS > 10 SQLCHAR 0 3000 "," 0 none_Zip Latin1_General_CI_AS > 11 SQLCHAR 0 3000 "," 0 none_Phone Latin1_General_CI_AS > 12 SQLCHAR 0 3000 "," 0 none_Competitor Latin1_General_CI_AS > 13 SQLCHAR 0 3000 "," 0 none_Plan Latin1_General_CI_AS > 14 SQLCHAR 0 3000 "," 0 none_HarvestDate Latin1_General_CI_AS > 15 SQLCHAR 0 3000 "," 0 none_EIDs Latin1_General_CI_AS > 16 SQLCHAR 0 3000 "," 5 EIDCount Latin1_General_CI_AS > 17 SQLCHAR 0 3000 "," 0 SourceIds Latin1_General_CI_AS > 18 SQLCHAR 0 3000 "," 6 minScore Latin1_General_CI_AS > 19 SQLCHAR 0 3000 "," 7 maxScore Latin1_General_CI_AS > 20 SQLCHAR 0 3000 "," 8 hasContract Latin1_General_CI_AS > 21 SQLCHAR 0 3000 "," 9 matchFilter Latin1_General_CI_AS > 22 SQLCHAR 0 3000 "," 10 matchFilterBGR Latin1_General_CI_AS > 23 SQLCHAR 0 3000 "," 11 matchFilterCAS Latin1_General_CI_AS > 24 SQLCHAR 0 3000 "," 12 matchFilterMTV Latin1_General_CI_AS > 25 SQLCHAR 0 3000 "," 13 forceMatch Latin1_General_CI_AS > 26 SQLCHAR 0 3000 "," 14 forceMatchUserId Latin1_General_CI_AS > 27 SQLCHAR 0 3000 "," 15 paymentPlatforms Latin1_General_CI_AS > 28 SQLCHAR 0 3000 "," 16 matchStatusColor Latin1_General_CI_AS > 29 SQLCHAR 0 3000 "," 17 recordId Latin1_General_CI_AS > 30 SQLCHAR 0 3000 "," 18 dupId Latin1_General_CI_AS > 31 SQLCHAR 0 3000 "," 4 NetworkComparedTo Latin1_General_CI_AS > > Here's a script for the table! > > if exists (select * from dbo.sysobjects where id = > object_id(N'[dbo].[Provider_Results]') and OBJECTPROPERTY(id, > N'IsUserTable') = 1) > drop table [dbo].[Provider_Results] > GO > > CREATE TABLE [dbo].[Provider_Results] ( > [Provider_Raw_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [Category] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [SpecialtyCode] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [NetworkComparedTo] [varchar] (3000) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [EIDCount] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [minScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [maxScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [hasContract] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [matchFilter] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [matchFilterBGR] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [matchFilterCAS] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [matchFilterMTV] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [forceMatch] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > [forceMatchUserId] [varchar] (3000) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [paymentPlatforms] [varchar] (3000) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [matchStatusColor] [varchar] (3000) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [recordId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [dupId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > ) ON [PRIMARY] > GO > > Thanks again. I am dying on this one. With everything included > hopefully someone (smarter than me) can figure it out. > > Thanks, > > Andrew > > > > *** Sent via Devdex http://www.devdex.com *** > Don't just participate in USENET...get rewarded for it! |
| ||||
| Might look at the solution Erland gave me... see subject "Bulk Insert Problems" HTH CBL "Andrew Slentz" <ajslentz@yahoo.com> wrote in message news:40cf845e$0$25534$c397aba@news.newsgroups.ws.. . > I have tried and tried to get this to work and I'm doing something dumb. > I'm going to include sample data and my format file. Can someone help > me figure out my dumb mistake on a bulk insert using a format file? > Don't worry about data types, I'll handle that later. Thanks a lot!!! > > CSV File: > > ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,ADDRESS1,CITY, STATE,ZIP,PHONE,COMPET > ITOR,PLAN,HARVESTDATE,EIDs,EIDCount,sourceIds,minS core,maxScore,hasContr > act,matchFilter,matchFilterBGR,matchFilterCAS,matc hFilterMTV,forceMatch, > forceMatchUserId,paymentPlatforms,matchStatusColor ,recordId,dupId,Networ > kComparedTo > 1,name1,Family Practice General Practice,FP,PCP,address1,Deerfield > Beach,FL,33442,phone1,C1,PPO,5/15/2004,1000067851,2,BADG_0874992,9,14.4, > Y,Y,Y,Y,Y,0,,3,G,1, ,netcomp1 > 2,name2,Family Practice General > Practice,FP,PCP,address2,Margate,FL,33063,phone2,C 2,PPO,5/15/2004,100006 > 7851,2,BADG_0874992,9,10,Y,Y,Y,Y,Y,0,,3,G,2, ,netcomp2 > 3,name3,General Practice,GP,PCP,address3,Deerfield > Beach,FL,33442,phone3,C3,PPO,5/15/2004,1000067851,2,BADG_0874992(14.4 > Y)|BADG_1901859,9,14.4,Y,Y,Y,Y,Y,0,,3,G,3, ,netcomp3 > 4,name4,Reproductive > Endocrinology,OBEN,OB,address4,Davie,FL,33328,phon e4,C4,PPO,5/15/2004,10 > 00083687,1,CAS_650410436,10.3,10.3,Y,N,N,N,N,0,,0, Y,4, ,netcomp4 > > > Format File: > 8.0 > 31 > 1 SQLCHAR 0 1 "\r\n" 0 quote Latin1_General_CI_AS > 2 SQLCHAR 0 3000 "," 1 Provider_Raw_ID Latin1_General_CI_AS > 3 SQLCHAR 0 3000 "," 0 none_name Latin1_General_CI_AS > 4 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS > 5 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS > 6 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS > 7 SQLCHAR 0 3000 "," 0 none_Address Latin1_General_CI_AS > 8 SQLCHAR 0 3000 "," 0 none_City Latin1_General_CI_AS > 9 SQLCHAR 0 3000 "," 0 none_State Latin1_General_CI_AS > 10 SQLCHAR 0 3000 "," 0 none_Zip Latin1_General_CI_AS > 11 SQLCHAR 0 3000 "," 0 none_Phone Latin1_General_CI_AS > 12 SQLCHAR 0 3000 "," 0 none_Competitor Latin1_General_CI_AS > 13 SQLCHAR 0 3000 "," 0 none_Plan Latin1_General_CI_AS > 14 SQLCHAR 0 3000 "," 0 none_HarvestDate Latin1_General_CI_AS > 15 SQLCHAR 0 3000 "," 0 none_EIDs Latin1_General_CI_AS > 16 SQLCHAR 0 3000 "," 5 EIDCount Latin1_General_CI_AS > 17 SQLCHAR 0 3000 "," 0 SourceIds Latin1_General_CI_AS > 18 SQLCHAR 0 3000 "," 6 minScore Latin1_General_CI_AS > 19 SQLCHAR 0 3000 "," 7 maxScore Latin1_General_CI_AS > 20 SQLCHAR 0 3000 "," 8 hasContract Latin1_General_CI_AS > 21 SQLCHAR 0 3000 "," 9 matchFilter Latin1_General_CI_AS > 22 SQLCHAR 0 3000 "," 10 matchFilterBGR Latin1_General_CI_AS > 23 SQLCHAR 0 3000 "," 11 matchFilterCAS Latin1_General_CI_AS > 24 SQLCHAR 0 3000 "," 12 matchFilterMTV Latin1_General_CI_AS > 25 SQLCHAR 0 3000 "," 13 forceMatch Latin1_General_CI_AS > 26 SQLCHAR 0 3000 "," 14 forceMatchUserId Latin1_General_CI_AS > 27 SQLCHAR 0 3000 "," 15 paymentPlatforms Latin1_General_CI_AS > 28 SQLCHAR 0 3000 "," 16 matchStatusColor Latin1_General_CI_AS > 29 SQLCHAR 0 3000 "," 17 recordId Latin1_General_CI_AS > 30 SQLCHAR 0 3000 "," 18 dupId Latin1_General_CI_AS > 31 SQLCHAR 0 3000 "," 4 NetworkComparedTo Latin1_General_CI_AS > > Here's a script for the table! > > if exists (select * from dbo.sysobjects where id = > object_id(N'[dbo].[Provider_Results]') and OBJECTPROPERTY(id, > N'IsUserTable') = 1) > drop table [dbo].[Provider_Results] > GO > > CREATE TABLE [dbo].[Provider_Results] ( > [Provider_Raw_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [Category] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [SpecialtyCode] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [NetworkComparedTo] [varchar] (3000) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [EIDCount] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [minScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [maxScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [hasContract] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [matchFilter] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [matchFilterBGR] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [matchFilterCAS] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [matchFilterMTV] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [forceMatch] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > [forceMatchUserId] [varchar] (3000) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [paymentPlatforms] [varchar] (3000) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [matchStatusColor] [varchar] (3000) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [recordId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [dupId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > ) ON [PRIMARY] > GO > > Thanks again. I am dying on this one. With everything included > hopefully someone (smarter than me) can figure it out. > > Thanks, > > Andrew > > > > *** Sent via Devdex http://www.devdex.com *** > Don't just participate in USENET...get rewarded for it! |