Resulting message whit Bulk Insert

Question

Resulting message whit Bulk Insert

EIAnd

Right there with Babe

Points: 743
More actions
December 9, 2009 at 8:40 am

#135811

im executing the following query:
BULK INSERT CallList_CONTACT_prueba_dic02
FROM 'D:\Camp_Emtelco_Prueba_SinEspacios.csv'
WITH ( CHECK_CONSTRAINTS, FIRSTROW = 2, FIELDTERMINATOR = ';', ROWTERMINATOR = '', FIRE_TRIGGERS )
I am executing in mi local data base server in the following table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CallList_CONTACT_prueba_dic02](
[I3_RowID] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PhoneNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Zone] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_CallList_CONTACT_prueba_dic02_Zone] DEFAULT ('PCF'),
[Status] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_CallList_CONTACT_prueba_dic02_Status] DEFAULT (''),
[ICValue3] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_CallList_CONTACT_prueba_dic02_ICValue3] DEFAULT ('CAMP_CONTACT_PRUEBA'),
[Attempts] [int] NULL,
[Counter] [int] NULL,
[I3_AttemptsAbandoned] [int] NULL,
[I3_AttemptsMachine] [int] NULL,
[I3_AttemptsBusy] [int] NULL,
[I3_AttemptsFax] [int] NULL,
[I3_AttemptsNoAnswer] [int] NULL,
[UseDialPlan] [tinyint] NULL CONSTRAINT [DF_CallList_CONTACT_prueba_dic02_DialPlan] DEFAULT ('1'),
[MoneyCounter] [money] NULL,
[Fecha] [datetime] NULL CONSTRAINT [DF_CallList_CONTACT_prueba_dic02_Fecha] DEFAULT (getdate()),
[Tabla] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_CallList_CONTACT_prueba_dic02_Tabla] DEFAULT ('CallList_CONTACT_prueba_dic02'),
[Cliente] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_CallList_CONTACT_prueba_dic02_Cliente] DEFAULT ('Salida_CONTACT'),
[Sede] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_CallList_CONTACT_prueba_dic02_Sede] DEFAULT ('Poblado'),
CONSTRAINT [PK_CallList_CONTACT_prueba_dic02] PRIMARY KEY CLUSTERED
(
[I3_RowID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
the cvs file have 5 lines of insert:
I3_RowID;PHONENUMBER;Zone;Status;IC_Value3;Attempts;Counter;I3_AttemptsAbandoned;I3_AttemptsMachine;I3_AttemptsBusy;I3_AttemptsFax;I3_AttemptsNoAnswer;MoneyCounter;Fecha;Tabla;UseDialPlan;Cliente;Sede
223;2770055;;;;;;;;;;;;;;;;
224;2770064;;;;;;;;;;;;;;;;
225;2770111;;;;;;;;;;;;;;;;
226;2770143;;;;;;;;;;;;;;;;
227;2770500;;;;;;;;;;;;;;;;
the results Messages are:
(1 row(s) affected)
(5 row(s) affected)
but when i executes the same query in a diferent server (not the local one) shows diferent results:
the query is:
BULK INSERT CallList_CONTACT_Prueba_Aplicativo_Dic04
FROM '\\ip\Temporal\Camp_Emtelco_Prueba_SinEspacios.csv'
WITH ( CHECK_CONSTRAINTS, FIRSTROW = 2, FIELDTERMINATOR = ';', ROWTERMINATOR = '', FIRE_TRIGGERS )
(5 row(s) affected)
(5 row(s) affected)
why is this? thank you very mutch any help.

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

I'm pretty sure that it's not working correctly at all. The default delimiter for BULK INSERT is the tab character and you're using something else. You've also not told it to skip the header row using FIRST ROW. Lookup BULK INSERT in Books Online and checkout the parameters that can be used.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions