BULK INSERT with CONVERT or CAST?
-
While i am use bulk insert to import the csv file into my SQL SERVER through my system develop using ASP.NET by VB script, it able to import all csv data into my database. Unfortunately, some data such as those decimal or money data type already change to vchar for all the data type after import in my database and cause them to be close by double quote ("1,111.00"). This unable let me to total up those data while i do it with my system. As i research, there have CONVERT and CAST function to convert the data type, can i use them while i import the csv file into my database through the BULK INSERT? Thanks.
-
While i am use bulk insert to import the csv file into my SQL SERVER through my system develop using ASP.NET by VB script, it able to import all csv data into my database. Unfortunately, some data such as those decimal or money data type already change to vchar for all the data type after import in my database and cause them to be close by double quote ("1,111.00"). This unable let me to total up those data while i do it with my system. As i research, there have CONVERT and CAST function to convert the data type, can i use them while i import the csv file into my database through the BULK INSERT? Thanks.
Bulk load to a staging table, then clean the data when transferring to your production tables. This will allow you to leave the BulkInsert task alone, and expand the tranfer task as changes to the underlying data or destination tables changes. This is why we call it ETL. Extract the data from the source, transform it match your systems and the Load it into your systems. - Sage
-
Bulk load to a staging table, then clean the data when transferring to your production tables. This will allow you to leave the BulkInsert task alone, and expand the tranfer task as changes to the underlying data or destination tables changes. This is why we call it ETL. Extract the data from the source, transform it match your systems and the Load it into your systems. - Sage
-
Excuse me, can you mention more detail and how it to be done such as give some simple example? Thank you for you response.
This was thrown together, but should work if you replace the tablenames, columnnames and filenames with values that apply to your application BULK INSERT DBName.dbo.StagingTable -- StagingTable can be a copy of your destination table From 'C:\YourFile.txt' -- actual path to your file With ( FieldTerminator = ',', RowTerminator = '\n' -- for new line ) Alter Table StagingTable ADD SafeToTransfer BIT NOT NULL Default(1) -- Run some integrity checks prior to attempting to tranfer if ( select Count(*) From DBName.dbo.StagingTable Where IsNumeric( ColumnA )=0 ) >0 BEGIN Update DBName.dbo.StagingTable Set SafeToTransfer = 0 Where IsNumeric( ColumnA )=0 END if (select Count(*) From DBName.dbo.StagingTable Where isDate( ColumnB )=0 ) >0 BEGIN Update DBName.dbo.StagingTable Set SafeToTransfer = 0 Where isDate( ColumnB )=0 END if ( select Count(*) From DBName.dbo.StagingTable Where isNumeric( ColumnD )=0 ) >0 BEGIN Update DBName.dbo.StagingTable Set SafeToTransfer = 0 Where isNumeric( ColumnD )=0 END Insert Into DBName.dbo.DestinationTable ( ColumnA, -- an INT Column ColumnB, -- a datetime column ColumnC, -- a varchar(100) column ColumnD, -- a numeric(9,5) column ColumnE -- another varchar(100) column ) SELECT CAST( ColumnA as int), -- an INT Column CAST( ColumnB as datetime), -- a datetime column LEFT( ColumnC, 100), -- a varchar(100) column CONVERT( numeric(9,5), ColumnD), -- a numeric(9,5) column LEFT( ColumnE, 100) -- another varchar(100) column From DBName.dbo.StagingTable Where SafeToTransfer = 1 Alter Table StagingTable DROP COLUMN SafeToTransfer