Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk Insert with Dates


Bulk Insert with Dates

Author
Message
Rixxe
Rixxe
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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





Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38950
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rixxe
Rixxe
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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
Rixxe
Rixxe
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38950
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rixxe
Rixxe
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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


Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38950
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rixxe
Rixxe
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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


Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38950
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rixxe
Rixxe
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 75
Lowell

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

Many thanks

R
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search