0

Possible Duplicate:
bulk insert a date in YYYYMM format to date field in MS SQL table

I am using SQL Server 2012 Express.

I have a problem with using BULK INSERT from file that contains dates in format YYYYMMDD (can't change the format, it's an output from different software). I use a format file, as I have an identity column that should be skipped when inserting values. Table structure and BULK INSERT command are here:

CREATE TABLE [dbo].[daily](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[ticker] [varchar](15) NOT NULL,
[ddate] [date] NOT NULL,
[dopen] [decimal](16, 8) NOT NULL,
[dhigh] [decimal](16, 8) NOT NULL,
[dlow] [decimal](16, 8) NOT NULL,
[dclose] [decimal](16, 8) NOT NULL,
[dvol] [int] NOT NULL,
[dopenint] [int] NOT NULL
);


BULK INSERT daily
FROM 'C:\IBM.TXT'
WITH (
FORMATFILE = 'C:\dailyformat.xml',
TABLOCK,
FIRSTROW = 2);

Tab-delimited data file (IBM.TXT) is here:

Symbol  Date     Open    High    Low     Close   Total Volume    Total Open Interest
IBM 19620102     2.57000000  2.57000000  2.54000000  2.54000000    11704           0
IBM 19620103     2.54000000  2.56000000  2.54000000  2.56000000     8778           0
IBM 19620104     2.56000000  2.56000000  2.54000000  2.54000000     7878           0
IBM 19620105     2.53000000  2.53000000  2.48000000  2.49000000    11029           0
IBM 19620108     2.49000000  2.49000000  2.42000000  2.44000000    16431           0
IBM 19620109     2.45000000  2.50000000  2.45000000  2.47000000    14855           0

Format file (dailyformat.xml), generated by bcp, is here:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="15"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="11"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="41"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="41"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="41"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="41"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="ticker" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="ddate" xsi:type="SQLDATE"/>
  <COLUMN SOURCE="3" NAME="dopen" xsi:type="SQLDECIMAL" PRECISION="16" SCALE="8"/>
  <COLUMN SOURCE="4" NAME="dhigh" xsi:type="SQLDECIMAL" PRECISION="16" SCALE="8"/>
  <COLUMN SOURCE="5" NAME="dlow" xsi:type="SQLDECIMAL" PRECISION="16" SCALE="8"/>
  <COLUMN SOURCE="6" NAME="dclose" xsi:type="SQLDECIMAL" PRECISION="16" SCALE="8"/>
  <COLUMN SOURCE="7" NAME="dvol" xsi:type="SQLINT"/>
  <COLUMN SOURCE="8" NAME="dopenint" xsi:type="SQLINT"/>
 </ROW>
</BCPFORMAT>

I get the following error:

Msg 206, Level 16, State 2, Line 1
Operand type clash: numeric is incompatible with date.

Please help to fix it without inserting date as number to a temporary table and then converting it to date by selecting it into other table. There should be some fix to my code.

Thanks!

Community
  • 1
  • 1
user1603038
  • 2,103
  • 3
  • 19
  • 29

2 Answers2

3

Problem solved. I used

INSERT INTO daily 
SELECT a.* FROM OPENROWSET(
BULK 'C:\IBM.TXT',
FORMATFILE = 'C:\dailyformat.xml',
FIRSTROW = 2
) as a
user1603038
  • 2,103
  • 3
  • 19
  • 29
  • Do not forget to accept your own answer then. – j0k Aug 20 '12 at 11:27
  • This approach is robust for both date and numeric processing. The built in data conversion with the bulk import facilities seems very rough, whereas ordinary DML is very good at type conversion from strings. Also this approach lets you separate the file conversion from the insert so it is easier to see what is going on. – Ross Attrill Aug 25 '14 at 06:22
2

Try calling SET DATEFORMAT ymd before your 'BULK INSERT'

Tim Rogers
  • 21,297
  • 6
  • 52
  • 68