Any insight as to how I can set up the monthly bulk inserts of text files whereby I do not have to keep modifying the as displayed below?
The code below is used to import the October 2017 text files. For the November 2017 monthly data, I would just copy the script below and paste at the end of my sql script, modify a few fields so that the November 2017 data is validated and imported into the database table. Then, I repeat the same process each time for each succeeding month.
Currently reading about the use of a stored procedure and SSIS. However, I would like to first attempt the use of a stored procedure then proceed to the use of SSIS only if necessary.
Based on the reading so far, it appears that I will need parameters for the stored procedure such as
@FilePath
@FileNameMask
As I am not familiar at all with stored procedures, I would appreciate any insight concerning best practices regarding the use of a stored procedure with several parameters. Are there any disadvantages or nuances/intricacies with the use of stored procedures that I should be aware of?
CODE
USE EquipmentDatabase
If object_id ('tempdb..#EquipmentData') is not null
BEGIN
DROP Table #EquipmentData
END
GO
Create Table #EquipmentData
.
.
.
GO
Bulk Insert #EquipmentData
From 'C:\Data\TextFilesToLoad\Equipmentdata\EquipmentData_201710.txt'
With
(
Fieldterminator = '\|',
Rowterminator = '\n'
Firstrow = 2
);
Go
if object_id('tempdb..#validate_EquipmentData' is not null
BEGIN
DROP table #validate_EquipmentData;
END
GO
Create Table #validate_EquipmentData
.
.
.
[Count_Dups] int
GO
Insert into #validate_EquipmentData
select rtrim(ltrim(ed1.JurisdictionCode))
, rtrim(ltrim(ed1.AccountNo))
, rtrim(ltrim(ed1.SequenceNumber))
...
...
...
, case
when len(ed1.SaleDate) = 8
and isdate(ed1.SaleDate) = 1
and rtrim(ltrim(ed1.SaleDate)) like '[0-9][0-9][0-9][0-9][0-1][0-2][0-3][0-9]'
then convert(date, ed1.SaleDate, 112)
else cast(null as date)
end as SaleDate
-- fields related to validation
, ed1.SaleDate as Input_SaleDate
, dup.count_dups
from #EquipmentData ed1
left outer join (select JurisdictionCode
, AccountNo
, SequenceNumber
, SaleDate
, count(*) as count_dups
from #EquipmentData
group by JurisdictionCode
, AccountNo
, SequenceNumber
, SaleDate
) dup
on dup.JurisdictionCode = ed1.JurisdictionCode
and dup.AccountNo = ed1.AccountNo
and dup.SequenceNumber = ed1.SequenceNumber
and dup.SaleDate = ed1.SaleDate
Insert into Equipment_2017
select
ve.JurisdictionCode,
ve.AccountNo,
ve.SequenceNumber,
.
.
.
from #validate_Equipment ve
where ve.SaleDate between convert(date, '20170101') and convert(date, '20171231')
and ve.JurisdictionCode is not null
and ve.AccountNo is not null
and ve.SequenceNumber is not null
and ve.count_dups = 0
if object_id('Equipment..Equipment_201710_Errors') is not null
BEGIN
DROP table Equipment_201710_Errors;
END
GO
Create Table Equipment_201710_Errors (
JurisdictionCode varchar (5) NULL,
AccountNo varchar (45) NULL,
SequenceNo int NOT NULL,
.
.
.
SaleDate date NULL,
SaleAmount int NULL
)
GO
insert into Equipment_201710_Errors
select ve.JurisdictionCode
, ve.AccountNo
, ve.SequenceNumber
...
...
...
, ve.SaleDate
from #validate_Equipment ve
where not (ve.SaleDate between convert(date, '20170101') and convert(date, '20171231')
and ve.JurisdictionCode is not null
and ve.AccountNo is not null
and ve.SequenceNumber is not null
and ve.count_dups = 0
)
[/Code]
