August 23, 2011 at 4:52 am
Hi All,
I have a bulk insert procedure which i use a fair amount. It does the job well. (below) However it does mean for fields such as 'YOB' and 'Regdate' that i need to convert the fields to dates afterwards which is a bit of a pain.
As you may have guessed, all the text files are Fixed width. I'm not able to get anything changed at source due to other programs using the data.
Question: Is there a way to use Bulk insert and insert directly as a date? Rather than as nvarchar?
I believe the date format i need is (113).
If not, is there a way to put the date conversions within the stored procedure?
Sorry if i didn't explain everything fully. Please ask for any clarification.
Many thanks
R
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[importpat] Script Date: 08/23/2011 11:33:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[importpat]
@PathFileName nvarchar(2000),
@tblname nvarchar(50)
AS
BEGIN
DECLARE @sql nvarchar(1000)
-- create the temp table
CREATE TABLE #stage(BulkColumn nvarchar(255));
-- Bulk Insert the data
set @sql = N'Bulk Insert #stage From ' + Char(39) + @PathFileName + Char(39)
exec TestDB..sp_executesql @sql
-- Substring input patient
set @sql = N'SELECT
SUBSTRING(BulkColumn, 1, 4) AS patid
,SUBSTRING(BulkColumn, 5, 1) AS patflag
,SUBSTRING(BulkColumn, 6, 8) AS yob
,SUBSTRING(BulkColumn, 14, 6) AS famnum
,SUBSTRING(BulkColumn, 20, 1) AS sex
,SUBSTRING(BulkColumn, 21, 8) AS regdate
,SUBSTRING(BulkColumn, 29, 2) AS regstat
,SUBSTRING(BulkColumn, 31, 8) AS xferdate
,SUBSTRING(BulkColumn, 39, 2) AS regrea
,SUBSTRING(BulkColumn, 41, 8) AS deathdate
,SUBSTRING(BulkColumn, 49, 1) AS deathinfo
,SUBSTRING(BulkColumn, 50, 1) AS accept
,SUBSTRING(BulkColumn, 51, 1) AS institute
,SUBSTRING(BulkColumn, 52, 2) AS marital
,SUBSTRING(BulkColumn, 54, 1) AS dispensing
,SUBSTRING(BulkColumn, 55, 2) AS prscexempt
,SUBSTRING(BulkColumn, 57, 8) AS sysdate
into ' + @tblname + ' FROM #stage'
exec TestDB..sp_executesql @sql
-- drop the temp table
DROP TABLE #stage
END
August 23, 2011 at 5:51 am
since you have everything in a staging table, shouldn't you just convert the columns explicitly?
-- Substring input patient
set @sql = N'SELECT
CONVERT(int,SUBSTRING(BulkColumn, 1, 4)) AS patid
,SUBSTRING(BulkColumn, 5, 1) AS patflag
,CONVERT(datetime,SUBSTRING(BulkColumn, 6, 8)) AS yob
,SUBSTRING(BulkColumn, 14, 6) AS famnum
,SUBSTRING(BulkColumn, 20, 1) AS sex
,CONVERT(datetime,SUBSTRING(BulkColumn, 21, 8)) AS regdate
,SUBSTRING(BulkColumn, 29, 2) AS regstat
,CONVERT(datetime,SUBSTRING(BulkColumn, 31, 8)) AS xferdate
,SUBSTRING(BulkColumn, 39, 2) AS regrea
,CONVERT(datetime,SUBSTRING(BulkColumn, 41, 8)) AS deathdate
,SUBSTRING(BulkColumn, 49, 1) AS deathinfo
,SUBSTRING(BulkColumn, 50, 1) AS accept
,SUBSTRING(BulkColumn, 51, 1) AS institute
,SUBSTRING(BulkColumn, 52, 2) AS marital
,SUBSTRING(BulkColumn, 54, 1) AS dispensing
,SUBSTRING(BulkColumn, 55, 2) AS prscexempt
,CONVERT(datetime,SUBSTRING(BulkColumn, 57, 8)) AS sysdate
into ' + @tblname + ' FROM #stage'
exec TestDB..sp_executesql @sql
Lowell
August 23, 2011 at 5:54 am
I was unaware you could do that, i'll give it a go.
Also do you have to specify the type of DateTime? e.g. yy-mm-dd/dd-mm-yy etc?
Many thanks
R
August 23, 2011 at 6:01 am
So i ran it with those changes suggested, and i get the following error:
(12100 row(s) affected)
Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Any ideas how to fix this? Or what else needs changing?
Many thanks
R
August 23, 2011 at 6:04 am
some of your dates are not dates...probably blank strings for death date(a reasonable assumption would be not all patients die or get transferred)
something like this:
...
CASE
WHEN ISDATE(SUBSTRING(BulkColumn, 41, 8)) =1
THEN CONVERT(datetime,SUBSTRING(BulkColumn, 41, 8)) ELSE NULL
END AS deathdate,
...
you'll need to do that for any of the columns that might be empty strings i think.
Lowell
August 23, 2011 at 6:14 am
Thank you for the help.
Currently I have this:
Still getting an error which has now become:
(12100 row(s) affected)
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near '('.
Trying to find what the issue is, Sorry if i'm way off in how i should be going about this.
Many thanks
R
set @sql = N'SELECT
SUBSTRING(BulkColumn, 1, 4) AS patid
,SUBSTRING(BulkColumn, 5, 1) AS patflag
,CASE
WHEN ISDATE(SUBSTRING(BulkColumn, 6, 8)) = 1
THEN CONVERT(datetime,SUBSTRING(BulkColumn, 31, 8)) ELSE NULL
END AS yob
,SUBSTRING(BulkColumn, 14, 6) AS famnum
,SUBSTRING(BulkColumn, 20, 1) AS sex
,CONVERT(datetime,SUBSTRING(BulkColumn, 21, 8)) AS regdate
,SUBSTRING(BulkColumn, 29, 2) AS regstat
,CASE
WHEN ISDATE(SUBSTRING(BulkColumn, 31, 8)) = 1
THEN CONVERT(datetime,SUBSTRING(BulkColumn, 31, 8)) ELSE NULL
END AS xferdate
,SUBSTRING(BulkColumn, 39, 2) AS regrea
,CASE
WHEN ISDATE(SUBSTRING(BulkColumn, 41, 8)) = 1
THEN CONVERT(datetime,SUBSTRING(BulkColumn, 41, 8)) ELSE NULL
END AS deathdate
,SUBSTRING(BulkColumn, 49, 1) AS deathinfo
,SUBSTRING(BulkColumn, 50, 1) AS accept
,SUBSTRING(BulkColumn, 51, 1) AS institute
,SUBSTRING(BulkColumn, 52, 2) AS marital
,SUBSTRING(BulkColumn, 54, 1) AS dispensing
,SUBSTRING(BulkColumn, 55, 2) AS prscexempt
,CONVERT(datetime,SUBSTRING(BulkColumn, 57, 8)) AS sysdate
into ' + @tblname + ' FROM #stage'
exec TestDB..sp_executesql @sql
August 23, 2011 at 6:19 am
your variable is too small
DECLARE @sql nvarchar(1000)
but the code you pasted is at least 1065 chars, depending on t table name.
change it to nvarchar(max) to be safe and try again.
Lowell
August 23, 2011 at 6:50 am
Thankyou very much for your help, that works now.
One last thing if i may:
How would i add this function into the procedure?
It changes the yob to always have a day, or month and day when used. I usually call it once i've imported the table. However now we have this statement to check if null, id like to add this in too.
Many thanks again!
R
USE [TestDB]
GO
/****** Object: UserDefinedFunction [dbo].[fixDate] Script Date: 08/23/2011 13:50:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fixDate](@dateIn char(8))
returns char(8)
as
begin
declare @out char(8)
if substring(@dateIn, 7,2) = '00'
if substring(@dateIn, 5,2) = '00'
set @out = left(@dateIn,4) + '0701'
else
set @out = left(@dateIn,6) + '15'
else set @out = @dateIn
return @out
end
August 23, 2011 at 7:05 am
shouldn't your function return a datetime instead of a char(8)?
anyway, you just wrap it with the function you wrote:
here's two ways, depending on if you change the function to return a datetime, or still a char:
...
,CASE
WHEN ISDATE([dbo].[fixDate](SUBSTRING(BulkColumn, 6, 8))) = 1
THEN CONVERT(datetime,[dbo].[fixDate](SUBSTRING(BulkColumn, 31, 8))) ELSE NULL
END AS yob
...
--or
--returns a datetime?
...
[dbo].[fixDate](SUBSTRING(BulkColumn, 31, 8)) AS yob
...
Lowell
August 23, 2011 at 7:22 am
Lowell
Thankyou very much for your help. This is saved me a stupendous amount of time.
Many thanks
R
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply