Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert with Dates Expand / Collapse
Author
Message
Posted Tuesday, August 23, 2011 4:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 5:12 AM
Points: 26, Visits: 75
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




Post #1163848
Posted Tuesday, August 23, 2011 5:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,899, Visits: 32,113
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1163880
Posted Tuesday, August 23, 2011 5:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 5:12 AM
Points: 26, Visits: 75
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
Post #1163883
Posted Tuesday, August 23, 2011 6:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 5:12 AM
Points: 26, Visits: 75
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
Post #1163889
Posted Tuesday, August 23, 2011 6:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,899, Visits: 32,113
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1163895
Posted Tuesday, August 23, 2011 6:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 5:12 AM
Points: 26, Visits: 75
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

Post #1163905
Posted Tuesday, August 23, 2011 6:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,899, Visits: 32,113
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1163908
Posted Tuesday, August 23, 2011 6:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 5:12 AM
Points: 26, Visits: 75
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

Post #1163935
Posted Tuesday, August 23, 2011 7:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,899, Visits: 32,113
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1163950
Posted Tuesday, August 23, 2011 7:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 5:12 AM
Points: 26, Visits: 75
Lowell

Thankyou very much for your help. This is saved me a stupendous amount of time.

Many thanks

R
Post #1163972
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse