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.

• Jeff Moden

  SSC Guru

  Points: 1004414

  More actions

  December 10, 2009 at 12:21 am

  #1090373

  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)