Bulk Insert issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Bulk Insert issues

Author

Topic

Ash1807
Starting Member

10 Posts

Posted - 2015-03-30 : 05:49:54

I am working on a project where I am importing a number of CSV flat files into existing, but empty SQL tables.

I have managed a few without any problems, but now there is a table where a lot of the fields are coming back with "invalid codepage type" errors

TThe problems seem to be where theinput is defintely numeric, but none of the numberic SQL field types are working. Soem of these only work if I make the SQL field to the varchar(50). This then makes my SQL database unworkable.

I am having the same problem with date field (input is dd/mm/yy), neither the DATE or DATETIME SQL field types are working.

Having done a number of tables without many issues, this one is now very frustrating. I am worndering if I may have slipped on something fundamental. I have revisited the input file creation a number of times, and see noewthing different from others

Kind regards

Ash Gajjar

Kristen
Test

22859 Posts

Posted - 2015-03-30 : 07:07:09

When I do this my preference is to import into a Staging Table where all columns are (excessively big) VARCHAR.

I then run a routine that checks that no column LEN is longer than expected, and that all Numbers and Dates are indeed Numeric / Valid dates. I have an extra column on the end for "Errors" to which I concatenate any suitable messages e.g.:


SET DATEFORMAT dmy

UPDATE U
SET Errors = COALESCE(Errors+'. ', '') + 'Invalid date in COLx'
FROM MyStagingTable AS U
WHERE IsDate(MyColX) = 0

I then "Upsert" into the live tables - in doing that I would FORCE the parsing of a "dd/mm/yy" to be that format as the only unambiguous format that SQL will naturally parse is "yyyymmdd" (or the ISO format "yyyy-mm-ddThh:mm:ss")

Ash1807
Starting Member

10 Posts

Posted - 2015-03-31 : 04:12:59

Thank you Kristen

I will create a staging table with everything as varchar, but will 'float' and 'datetime' fields not cause an issue when carrying out insert to the final table?

Kind regards

Ash Gajjar

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 04:08:50

When you UpSert the final table you may well need to CONVERT the strings to the appropriate type. Some data types will make an implicit data type conversion though.

We mechanically generate the UpSert statements using some SQL, so it is easy for us to have that include all the necessary CONVERT statements so that we explicitly control data type conversion.

All these will make an implicit data type conversion from String:


DECLARE @SomeTypes TABLE
(
	T_String varchar(20),
	T_int int,
	T_float float,
	T_bit bit,
	T_date datetime,
	T_GUID uniqueidentifier
)

SET DATEFORMAT dmy

INSERT INTO @SomeTypes
SELECT	[T_String] = 'Some String',
	[T_int] = '1234',
	[T_float] = '123.456',
	[T_bit] = '1',
	[T_date] = '01/02/2000',
	[T_GUID] = '00000000-0000-0000-0000-000000000000'

SELECT * FROM @SomeTypes

NOTE the SET DATEFORMAT to ensure that a string date like 'dd/mm/yyyy' is converted unambiguously (if you don't explicitly use CONVERT)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources