Bulk Insert

• scott.barnett

  SSC Enthusiast

  Points: 119

  More actions

  June 10, 2014 at 7:12 am

  #396889

  Hi All -

  What I am looking to do is use a Bulk Insert to import a cvs file to a table. This table is for zip code rates (tax's based on zip codes). When do so using this code:

  Bulk insert ziptaxrate

  from 'C:\ziptest.csv'

  with

  (fieldterminator = ',',

  rowterminator = '')

  GO

  I then get the following error:

  Msg 4864, Level 16, State 1, Line 1

  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (TaxClassID).

  This is the table build (also attached) :

  USE [Store1]

  GO

  /****** Object: Table [dbo].[ZipTaxRate] Script Date: 06/09/2014 09:39:21 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[ZipTaxRate](

  [ZipTaxID] [int] IDENTITY(1,1) NOT NULL,

  [ZipCode] [nvarchar](10) NOT NULL,

  [TaxClassID] [int] NOT NULL CONSTRAINT [DF_ZipTaxRate_TaxClassID] DEFAULT ((1)),

  [TaxRate] [money] NULL,

  [CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_ZipTaxRate_CreatedOn] DEFAULT (getdate()),

  CONSTRAINT [PK_ZipTaxRate] PRIMARY KEY CLUSTERED

  (

  [ZipTaxID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  Attached are screen shots also and the csv test file.

  Any help would be great.

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  June 10, 2014 at 7:16 am

  #1720379

  It isn't necessary to post in more than one forum. It just duplicates effort for those helping and fragments any answers you get.

  Direct replies to http://www.sqlservercentral.com/Forums/Topic1579175-148-1.aspx

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions