Sql Server 2012 Bulk Insert Large File - Errors related to converting datetime to date 1

[BxWill]

Using Sql Server 2012 and attempting to load a CSV file with over 1,000,000 records.

Within the file, the TransactionDate field is displayed as "YYYY-MM-DD 00:00:00"

I am interested in storing the data in the database within the respective yearly tables with a date format like "YYYY-MM-DD."

Encountering two errors

Error 1 occurs when I attempt to load the data from the staging table, #EquipmentData1, into table "EquipmentData_2013.

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.
The statement has been terminated.

Currently troubleshooting but have not quite been able to resolve.

Focusing on the WHERE clause of the sql statement, as displayed below.

One thought is that I maybe need to incorporate "CAST" with "CONVERT."
However, the WHERE section is only filtering the data within the staging table, not converting the TransactionDate field
from datetime to date format. Right?

Carrying this thought further, maybe I need to change the datatype for the TransactionDate field on table "EquipmentData_2013" from "datetime" to "date"...

where [ZipCode] is not null
AND [TransactionDate] is not null 
AND TransactionDate between convert(date, '20130101',112) and convert(date, '20131231',112)

Prior to loading the data from the staging table, #EquipmentData1, into table "EquipmentData_2013. I query
the staging table to obtain a sense of the data that will be loaded.

For example, I run the following - Select * from #EquipmentData1

I receive the following error

"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."

Based on my initial research, it appears that this error occurs
because SSMS has insufficient memory to allocate for large results.

It appears that SSMS is a 32-bit process and is limited to 2 GB of memory.

"SSMS imposes an artificial limit on how much text that can be displayed per database field in the results window. This limit is 64 KB in "Grid" mode and 8 KB in "Text" mode. If the result set is too large, the memory that is required to display the query results may surpass the 2 GB limit of the SSMS process. Therefore, a large result set can cause the error that is mentioned in the "Symptoms" section."

Consequently, it appears that this error may just be a "cosmetic" issue and maybe I should not worry about resolving.

Displayed below is a snippet of the code.

[Bold]
Any insight as to a resolution for the errors?
[/Bold]

Use Equipment

IF OBJECT_ID('tempdb..#EquipmentData1') IS NOT NULL
BEGIN
	DROP TABLE #EquipmentData1
END
GO


CREATE TABLE [#EquipmentData1](
		[TransactionDate]					[varchar] (150) NOT NULL,
		[ZipCode]						[varchar] (15)  NOT NULL,
		[Cost]							[varchar] (40)  NULL,
		--    CreatedBy  					varchar (255)
		--    CreatedAt 					date
)	
GO


IF OBJECT_ID('Equipment..EquipmentData_2013') IS NOT NULL
BEGIN
	DROP TABLE EquipmentData_2013
END
GO

CREATE TABLE [EquipmentData_2013](		
		[TransactionDate]					[datetime] NOT NULL,
		[ZipCode]						[varchar] (5)  NOT NULL,
		[Cost]							[int] NULL,
		Constraint PK_EquipmentData_2013 Primary Key ([TransactionDate],[ZipCode])
		--CreatedBy  						varchar (255) default system_user
		--CreatedAt 						date NULL DEFAULT GETDATE()
)
GO


BULK INSERT #EquipmentData1
	FROM 'C:\Test\EquipmentData\Test\EquipmentData_1.txt' 
	
   WITH
      (
		 FIELDTERMINATOR = ',',  
		 ROWTERMINATOR = '0x0a'    
		
      );
GO

--Select * from #EquipmentData1
--Error Received:  
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.



[Code]
insert into EquipmentData_2013
select 
		TransactionDate,
		CASE												
		   When RTRIM(LTRIM([ZipCode])) LIKE '[0-9][0-9][0-9][0-9][0-9]' then RTRIM(LTRIM([ZipCode]))
		   When RTRIM(LTRIM([ZipCode])) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' then substring(RTRIM(LTRIM([ZipCode])),1 , 5)
		   --else [ZipCode]
		   else null
		end as [ZipCode],	
		[Cost],


from #EquipmentData1
where [ZipCode] is not null
AND [TransactionDate] is not null 
AND TransactionDate between convert(date, '20130101',112) and convert(date, '20131231',112)  


GO

 

[fredericofonseca]

the error is referring to a conversion to a number, not a date.

as the only number you have shown is COST try the following - without the insert so you can check the data

-- try_convert will return null if the conversion fails
-- although not a catch all it is a good indication for when 
-- data is really bad
select  top 10 *
		TransactionDate,
		CASE												
		   When RTRIM(LTRIM([ZipCode])) LIKE '[0-9][0-9][0-9][0-9][0-9]' then RTRIM(LTRIM([ZipCode]))
		   When RTRIM(LTRIM([ZipCode])) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' then substring(RTRIM(LTRIM([ZipCode])),1 , 5)
		   --else [ZipCode]
		   else null
		end as [ZipCode],	
		[Cost],


from #EquipmentData1
where [ZipCode] is not null
AND [TransactionDate] is not null 
AND TransactionDate between convert(date, '20130101',112) and convert(date, '20131231',112)  

and try_convert(int, cost) is null

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[BxWill]

Error received upon attempting to load the table, EquipmentData_2013 is:

Conversion failed when converting date and/or time from character string.

It appears that I am very close to resolving...

If the CSV data has a datetime format and I want to display the data in the database with a date format, my thought is such that
the staging table has VARCHAR format for the field "TransactionDate."

Then, if I have a date format on the table "EquipmentData_2013", I need to convert from the datetime format to the date format.

In the WHERE section, I will filter the data. But, it appears that I have to perform another CAST or CONVERT - maybe on the field itself ("TransactionDate") within the INSERT INTO statement?

Or, is this simply resolved by changing the WHERE section?

What am I missing here?

 

[BxWill]

Resolved.

Thanks for the insight!