April 14, 2010 at 4:23 pm
Ok,
here's part 1. It should cover everything except the handling of (@PermitType = 'M')
I expect you have a test environment so you can verify if it will return the same results for this part.
I didn't look into indexing yet since i'd like to see if we get the same results for this part and how long it takes compared to the current solution.
/* -- test setup to reset tables
UPDATE PWDPermitConversionData
SET conversionstatus=0
FROM PWDPermitConversionData
TRUNCATE TABLE PWDPermit
TRUNCATE TABLE PWDPermitConversionErrors
*/
DECLARE
@User VARCHAR(8),
@True BIT,
@False BIT,
@PermitType_Org INT,
@PermitType_Ind INT,
@PermitType_Temp INT,
@PermitType_Sticker INT,
@PermitStatus_RU INT,
@PermitDeviceType_Placard INT,
@PermitDeviceType_Sticker INT,
@PermitDeviceType_Unknown INT,
@Today DATETIME,
@MinDate DATETIME,
@MaxDate DATETIME,
@DateMinus1Year DATETIME
SET@User = 'PWDConv'
SET@True = 0x1
SET@False = 0x0
SET@Today = GETDATE()
SET@MinDate = CONVERT(DATETIME, '1901-01-01')
SET@MaxDate = CONVERT(DATETIME, '2100-01-01')
SET@DateMinus1Year = DATEADD(yy, -1, @Today)
SELECT@PermitType_Org =1-- PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Organization'
SELECT@PermitType_Ind =2-- PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Permanent'
SELECT@PermitType_Temp =3-- PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Temporary'
SELECT@PermitType_Sticker =4-- PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Sticker'
SELECT@PermitStatus_RU = 5--PWDPermitStatusID From IARTS..PWDPermitStatus Where LookupValue = 'RtrndUnDel'
SELECT@PermitDeviceType_Placard =10-- PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Placard'
SELECT@PermitDeviceType_Sticker =11-- PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Sticker'
SELECT@PermitDeviceType_Unknown =12-- PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Unknown'
-- build table variable to hold the values inserted with the batch statement
DECLARE @MyTableVar TABLE( [PermitNo] CHAR(6),
[CustNo] INT);
-- build intermeditate table to hold invalid values
IF OBJECT_ID('tempdb..#inv_PWDConvData') IS NOT NULL
DROP TABLE #inv_PWDConvData
SELECT
0 AS PWDPermitConversionDataID,
0 AS [RecordIndicator],
0 AS errorid,
CAST('' AS VARCHAR(MAX)) AS ErrorMessage
INTO #inv_PWDConvData
WHERE 1=2
-- populate intermeditate table
INSERT INTO #inv_PWDConvData
SELECT
PWDPermitConversionDataID,
0 AS [RecordIndicator],
CASE
WHEN [PermitNumber] LIKE '%[^0-9]%' THEN 5000
WHEN [PermitPrefix] NOT IN ('D','M','V','S','','P', 'H') THEN 5001
WHEN ([Status] NOT IN('RP','') AND [Status] LIKE '%[^0-9]%') THEN 5002
WHEN (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0) THEN 5003
ELSE 5004 END AS errorid,
CASE
WHEN [PermitNumber] LIKE '%[^0-9]%' THEN 'non-numeric [PermitNumber]'
WHEN [PermitPrefix] NOT IN ('D','M','V','S','','P', 'H') THEN 'invalid [PermitPrefix]'
WHEN ([Status] NOT IN('RP','') AND [Status] LIKE '%[^0-9]%') THEN 'invalid [Status]'
WHEN (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0) THEN 'invalid [ExpirationDate]'
ELSE 'unknown error' END AS ErrorMessage
FROM PWDPermitConversionData
WHERE [PermitNumber] LIKE '%[^0-9]%' --select non-numeric values
OR [PermitPrefix] NOT IN ('D','M','V','S','','P', 'H') -- invalid prefix
OR ([Status] NOT IN('RP','') AND [Status] LIKE '%[^0-9]%') -- invalid status
OR (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0) -- invalid ExpirationDate
--------------------------------------------------------------------------------------------------------
-- Insert rows with failed test
--------------------------------------------------------------------------------------------------------
INSERT INTO PWDPermitConversionErrors
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
SELECT
PWDPermitConversionDataID,
0,
errorid,
ErrorMessage
FROM #inv_PWDConvData
--------------------------------------------------------------------------------------------------------
-- Insert the primary record data inside a try/catch block
--------------------------------------------------------------------------------------------------------
Begin Try
INSERT INTO PWDPermit
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate]
)
OUTPUT INSERTED.[PermitNo], INSERTED.[CustNo]
INTO @MyTableVar
SELECT
CASE WHEN PermitNumber = '' THEN NULL
ELSE CAST(PermitNumber AS INT)
END,
CustomerNumber,
CASE PermitPrefix
WHEN 'D' THEN @PermitType_Org
WHEN 'M' THEN @PermitType_Ind
WHEN 'V' THEN @PermitType_Temp
WHEN 'S' THEN @PermitType_Sticker
WHEN '' THEN NULL
ELSE PermitPrefix -- This will make it fail for sure
END,
CASE [Status] WHEN 'RP' THEN @PermitStatus_RU
WHEN '' THEN NULL
ELSE CAST([Status] AS INT)
END,
CASE WHEN PermitPrefix = 'S' THEN @PermitDeviceType_Sticker
WHEN PermitPrefix IN ('D', 'M', 'V') THEN @PermitDeviceType_Placard
WHEN PermitPrefix = '' THEN NULL
ELSE @PermitDeviceType_Unknown
END,
CASE WHEN Location = '' OR Location IS NULL THEN '999'
ELSE Location
END,
CASE WHEN HCExam = '' OR HCExam IS NULL THEN '999'
ELSE HCExam
END,
CASE WHEN HCStation = '' OR HCStation IS NULL THEN '99'
ELSE HCStation
END,
CAST(IssueDate AS DATETIME),
CASE WHEN ExpirationDate = 'NONE' OR ExpirationDate = '' OR ExpirationDate = 'NOE' THEN @MaxDate
ELSE CAST(ExpirationDate AS DATETIME)
END,
CASE WHEN InvalidDLNumber = 'Y' THEN @True
ELSE @False
END,
@False,
@User,
@Today
FROM PWDPermitConversionData
--From IARTS..PWDPermitConversionData
WHERE
NOT EXISTS
(
SELECT 1
FROM #inv_PWDConvData
WHERE #inv_PWDConvData.PWDPermitConversionDataID = PWDPermitConversionData.PWDPermitConversionDataID
)
AND CustomerNumber > 0 -- which also cover NOT NULL
AND ConversionStatus = 0
AND PermitPrefix NOT IN ('P', 'H')
AND [Status] <> '06'
AND
(
(
(
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END IS NULL
OR
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END > @DateMinus1Year
)
AND PermitPrefix <> 'M'
)
OR
(
PermitPrefix = 'M'
)
)
End Try
Begin Catch
Print 'Insert failed'
Print '----------------------------------------'
Print ''
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the primary record
--------------------------------------------------------------------------------------------------------
UPDATE PWDPermitConversionData
SET
conversionstatus= ISNULL(i.[PWDPermitConversionDataID],1),
ConversionErrorMessage =i.[ErrorMessage]
FROM PWDPermitConversionData p
LEFT OUTER JOIN #inv_PWDConvData i
ON p.[PWDPermitConversionDataID]=i.[PWDPermitConversionDataID]
AND p.conversionstatus=0
April 15, 2010 at 6:54 am
lmu92 (4/14/2010)
Ok,here's part 1. It should cover everything except the handling of (@PermitType = 'M')
I expect you have a test environment so you can verify if it will return the same results for this part.
I didn't look into indexing yet since i'd like to see if we get the same results for this part and how long it takes compared to the current solution.
/* -- test setup to reset tablesUPDATE PWDPermitConversionData
SET conversionstatus=0
FROM PWDPermitConversionData
TRUNCATE TABLE PWDPermit
TRUNCATE TABLE PWDPermitConversionErrors
*/
DECLARE
@User VARCHAR(8),
@True BIT,
@False BIT,
@PermitType_Org INT,
@PermitType_Ind INT,
@PermitType_Temp INT,
@PermitType_Sticker INT,
@PermitStatus_RU INT,
@PermitDeviceType_Placard INT,
@PermitDeviceType_Sticker INT,
@PermitDeviceType_Unknown INT,
@Today DATETIME,
@MinDate DATETIME,
@MaxDate DATETIME,
@DateMinus1Year DATETIME
SET@User = 'PWDConv'
SET@True = 0x1
SET@False = 0x0
SET@Today = GETDATE()
SET@MinDate = CONVERT(DATETIME, '1901-01-01')
SET@MaxDate = CONVERT(DATETIME, '2100-01-01')
SET@DateMinus1Year = DATEADD(yy, -1, @Today)
SELECT@PermitType_Org =1-- PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Organization'
SELECT@PermitType_Ind =2-- PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Permanent'
SELECT@PermitType_Temp =3-- PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Temporary'
SELECT@PermitType_Sticker =4-- PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Sticker'
SELECT@PermitStatus_RU = 5--PWDPermitStatusID From IARTS..PWDPermitStatus Where LookupValue = 'RtrndUnDel'
SELECT@PermitDeviceType_Placard =10-- PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Placard'
SELECT@PermitDeviceType_Sticker =11-- PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Sticker'
SELECT@PermitDeviceType_Unknown =12-- PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Unknown'
-- build table variable to hold the values inserted with the batch statement
DECLARE @MyTableVar TABLE( [PermitNo] CHAR(6),
[CustNo] INT);
-- build intermeditate table to hold invalid values
IF OBJECT_ID('tempdb..#inv_PWDConvData') IS NOT NULL
DROP TABLE #inv_PWDConvData
SELECT
0 AS PWDPermitConversionDataID,
0 AS [RecordIndicator],
0 AS errorid,
CAST('' AS VARCHAR(MAX)) AS ErrorMessage
INTO #inv_PWDConvData
WHERE 1=2
-- populate intermeditate table
INSERT INTO #inv_PWDConvData
SELECT
PWDPermitConversionDataID,
0 AS [RecordIndicator],
CASE
WHEN [PermitNumber] LIKE '%[^0-9]%' THEN 5000
WHEN [PermitPrefix] NOT IN ('D','M','V','S','','P', 'H') THEN 5001
WHEN ([Status] NOT IN('RP','') AND [Status] LIKE '%[^0-9]%') THEN 5002
WHEN (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0) THEN 5003
ELSE 5004 END AS errorid,
CASE
WHEN [PermitNumber] LIKE '%[^0-9]%' THEN 'non-numeric [PermitNumber]'
WHEN [PermitPrefix] NOT IN ('D','M','V','S','','P', 'H') THEN 'invalid [PermitPrefix]'
WHEN ([Status] NOT IN('RP','') AND [Status] LIKE '%[^0-9]%') THEN 'invalid [Status]'
WHEN (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0) THEN 'invalid [ExpirationDate]'
ELSE 'unknown error' END AS ErrorMessage
FROM PWDPermitConversionData
WHERE [PermitNumber] LIKE '%[^0-9]%' --select non-numeric values
OR [PermitPrefix] NOT IN ('D','M','V','S','','P', 'H') -- invalid prefix
OR ([Status] NOT IN('RP','') AND [Status] LIKE '%[^0-9]%') -- invalid status
OR (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0) -- invalid ExpirationDate
--------------------------------------------------------------------------------------------------------
-- Insert rows with failed test
--------------------------------------------------------------------------------------------------------
INSERT INTO PWDPermitConversionErrors
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
SELECT
PWDPermitConversionDataID,
0,
errorid,
ErrorMessage
FROM #inv_PWDConvData
--------------------------------------------------------------------------------------------------------
-- Insert the primary record data inside a try/catch block
--------------------------------------------------------------------------------------------------------
Begin Try
INSERT INTO PWDPermit
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate]
)
OUTPUT INSERTED.[PermitNo], INSERTED.[CustNo]
INTO @MyTableVar
SELECT
CASE WHEN PermitNumber = '' THEN NULL
ELSE CAST(PermitNumber AS INT)
END,
CustomerNumber,
CASE PermitPrefix
WHEN 'D' THEN @PermitType_Org
WHEN 'M' THEN @PermitType_Ind
WHEN 'V' THEN @PermitType_Temp
WHEN 'S' THEN @PermitType_Sticker
WHEN '' THEN NULL
ELSE PermitPrefix -- This will make it fail for sure
END,
CASE [Status] WHEN 'RP' THEN @PermitStatus_RU
WHEN '' THEN NULL
ELSE CAST([Status] AS INT)
END,
CASE WHEN PermitPrefix = 'S' THEN @PermitDeviceType_Sticker
WHEN PermitPrefix IN ('D', 'M', 'V') THEN @PermitDeviceType_Placard
WHEN PermitPrefix = '' THEN NULL
ELSE @PermitDeviceType_Unknown
END,
CASE WHEN Location = '' OR Location IS NULL THEN '999'
ELSE Location
END,
CASE WHEN HCExam = '' OR HCExam IS NULL THEN '999'
ELSE HCExam
END,
CASE WHEN HCStation = '' OR HCStation IS NULL THEN '99'
ELSE HCStation
END,
CAST(IssueDate AS DATETIME),
CASE WHEN ExpirationDate = 'NONE' OR ExpirationDate = '' OR ExpirationDate = 'NOE' THEN @MaxDate
ELSE CAST(ExpirationDate AS DATETIME)
END,
CASE WHEN InvalidDLNumber = 'Y' THEN @True
ELSE @False
END,
@False,
@User,
@Today
FROM PWDPermitConversionData
--From IARTS..PWDPermitConversionData
WHERE
NOT EXISTS
(
SELECT 1
FROM #inv_PWDConvData
WHERE #inv_PWDConvData.PWDPermitConversionDataID = PWDPermitConversionData.PWDPermitConversionDataID
)
AND CustomerNumber > 0 -- which also cover NOT NULL
AND ConversionStatus = 0
AND PermitPrefix NOT IN ('P', 'H')
AND [Status] <> '06'
AND
(
(
(
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END IS NULL
OR
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END > @DateMinus1Year
)
AND PermitPrefix <> 'M'
)
OR
(
PermitPrefix = 'M'
)
)
End Try
Begin Catch
Print 'Insert failed'
Print '----------------------------------------'
Print ''
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the primary record
--------------------------------------------------------------------------------------------------------
UPDATE PWDPermitConversionData
SET
conversionstatus= ISNULL(i.[PWDPermitConversionDataID],1),
ConversionErrorMessage =i.[ErrorMessage]
FROM PWDPermitConversionData p
LEFT OUTER JOIN #inv_PWDConvData i
ON p.[PWDPermitConversionDataID]=i.[PWDPermitConversionDataID]
AND p.conversionstatus=0
WOW! Talk about a completely different approach that looks 50 times more efficient. So glad I posted the question here and didn't just accept what I had.
I haven't run this yet, but I have a few questions, but before I ask, just want to say THANK YOU...
What is the purpose of this select, is this just to create the temp table since it will never work (because 1 does not equal 2)?
SELECT
0 AS PWDPermitConversionDataID,
0 AS [RecordIndicator],
0 AS errorid,
CAST('' AS VARCHAR(MAX)) AS ErrorMessage
INTO #inv_PWDConvData
WHERE 1=2
I had to pull out my trusty RegExBuilder program for this one, and I understand it, but don't understandy why (does that make sense). My cheat sheet says a carrot (^) means start of line, so why does this mean any non-numeric characters:
[PermitNumber] LIKE '%[^0-9]%'
Are these just so you have an integer value to represent which error was caught?
WHEN [PermitNumber] LIKE '%[^0-9]%' THEN 5000
I don't understand what the Output Inserted.PermitNo, Inserted.CustNo statement means; nowhere else is the @MyTableVar used so what is the purpose in that line, can it be run without?
INSERT INTO PWDPermit
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate]
)
OUTPUT INSERTED.[PermitNo], INSERTED.[CustNo]
INTO @MyTableVar
SELECT
CASE WHEN PermitNumber = '' THEN NULL
ELSE CAST(PermitNumber AS INT)
END,
How does this first Case statement every hold true, wouldn't it always have a value so [DateValue] will never be null?
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END IS NULL
OR
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END > @DateMinus1Year
I can't thank you enough, even though I haven't even run or tried this, you have opened my eyes to a whole different way of writing tsql.
April 15, 2010 at 7:37 am
gregory.anderson (4/15/2010)
What is the purpose of this select, is this just to create the temp table since it will never work (because 1 does not equal 2)?
SELECT
0 AS PWDPermitConversionDataID,
0 AS [RecordIndicator],
0 AS errorid,
CAST('' AS VARCHAR(MAX)) AS ErrorMessage
INTO #inv_PWDConvData
WHERE 1=2
Correct
I had to pull out my trusty RegExBuilder program for this one, and I understand it, but don't understandy why (does that make sense). My cheat sheet says a carrot (^) means start of line, so why does this mean any non-numeric characters:
[PermitNumber] LIKE '%[^0-9]%'
This is NOT RegEx. Look up LIKE in BOL.
I don't understand what the Output Inserted.PermitNo, Inserted.CustNo statement means; nowhere else is the @MyTableVar used so what is the purpose in that line, can it be run without?
INSERT INTO PWDPermit
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate]
)
OUTPUT INSERTED.[PermitNo], INSERTED.[CustNo]
INTO @MyTableVar
SELECT
CASE WHEN PermitNumber = '' THEN NULL
ELSE CAST(PermitNumber AS INT)
END,
See the OUTPUT clause in BOL.
Ask away if you have any other questions.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 15, 2010 at 9:05 am
Side-tracked by production issues right now, I'll let you guys know when I get back to this....
April 15, 2010 at 9:49 am
Thanks WayneS for the links...pretty straight-forward once I know what they do...
I just created duplicate PWDPermit, PWDPermitConversionData, and PWDPermitConversionErrors tables so that I could compare the results. Just ran the proc and it is INSANELY fast compared to that crap I wrote.
Even though the script failed on the first try, it ran in 24 seconds on 685861 rows in the PWDPermitConversionData table.
It came back saying the insert failed and updated all of the conversion status's (except for 2 failures from the first error-checking part) to a 1 even though it didn't insert any records into the PWDPermit table. Some tweaking that I need to do but I can't even calculate how much faster this is right now.
Once I get the syntax fixed, I'll re-post the resulting proc, and maybe add in the cursor section for the "previous" permits if anyone is interested.
Again, THANKS TO EVERYONE...this has been fun!
April 15, 2010 at 10:03 am
Yep, good set-based code has this weird ability to usually run insanely fast. Glad you're learning (which is why we throw links at you instead of just telling you ... so you can teach yourself). If you have questions, ask away.
And yes, please do let us know how it works out.
Stick around on this web site, and you'll learn even more. To the point where you'll start answering other posts!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 15, 2010 at 10:25 am
Again, WOW!
When I got to work this morning, MY version was still running and SSMS said the run time was at 18 hours, and that started at half-way through the conversion table, so it was more like 48 hours to run through all of the data.
I just got done fixing the script (and I haven't done the "previous" permits yet), but it took 26 seconds to insert 557554 permits out of the 685861 records in the conversion table. THAT IS NUTS!
I need to run SQL Data Compare now to see how the results look, but wow.
One thing I want to point out about the script before I paste it, for some reason, this line:
WHEN ([Status] NOT IN('RP','', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '99', NULL) AND [Status] LIKE '%[^0-9]%') THEN 5002
was not catching the '' value, and was still trying to insert that into the final table which was the reason why it was failing. So I just added the comment to this section of the insert statement:
CASE [Status] WHEN 'RP' THEN @PermitStatus_RU
--WHEN '' THEN NULL
ELSE CAST([Status] AS INT)
END,
Anyway, here is my final product for the "primary" permit section. I added the error output in the catch statement so I could find out where it was actually failing...
Use IARTS
Go
/* -- test setup to reset tables
UPDATE PWDPermitConversionData2
SET conversionstatus=0
FROM PWDPermitConversionData2
TRUNCATE TABLE PWDPermit2
TRUNCATE TABLE PWDPermitConversionErrors2
*/
DECLARE
@User VARCHAR(8),
@True BIT,
@False BIT,
@PermitType_Org INT,
@PermitType_Ind INT,
@PermitType_Temp INT,
@PermitType_Sticker INT,
@PermitStatus_RU INT,
@PermitDeviceType_Placard INT,
@PermitDeviceType_Sticker INT,
@PermitDeviceType_Unknown INT,
@Today DATETIME,
@MinDate DATETIME,
@MaxDate DATETIME,
@DateMinus1Year DATETIME
SET @User = 'PWDConv'
SET @True = 0x1
SET @False = 0x0
SET @Today = GETDATE()
SET @MinDate = CONVERT(DATETIME, '1901-01-01')
SET @MaxDate = CONVERT(DATETIME, '2100-01-01')
SET @DateMinus1Year = DATEADD(yy, -1, @Today)
SELECT @PermitType_Org = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Organization'
SELECT @PermitType_Ind = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Permanent'
SELECT @PermitType_Temp = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Temporary'
SELECT @PermitType_Sticker = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Sticker'
SELECT @PermitStatus_RU = PWDPermitStatusID From IARTS..PWDPermitStatus Where LookupValue = 'RtrndUnDel'
SELECT @PermitDeviceType_Placard = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Placard'
SELECT @PermitDeviceType_Sticker = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Sticker'
SELECT @PermitDeviceType_Unknown = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Unknown'
-- build table variable to hold the values inserted with the batch statement
DECLARE @MyTableVar TABLE( [PermitNo] CHAR(6),
[CustNo] INT);
-- build intermeditate table to hold invalid values
IF OBJECT_ID('tempdb..#inv_PWDConvData') IS NOT NULL
DROP TABLE #inv_PWDConvData
SELECT
0 AS PWDPermitConversionDataID,
0 AS [RecordIndicator],
0 AS errorid,
CAST('' AS VARCHAR(MAX)) AS ErrorMessage
INTO #inv_PWDConvData
WHERE 1=2
-- populate intermeditate table
INSERT INTO #inv_PWDConvData
SELECT
PWDPermitConversionDataID,
0 AS [RecordIndicator],
CASE
WHEN [PermitNumber] LIKE '%[^0-9]%' THEN 5000
WHEN [PermitPrefix] NOT IN ('D','M','V','S') THEN 5001--'','P', 'H') THEN 5001
WHEN ([Status] NOT IN('RP','', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '99', NULL) AND [Status] LIKE '%[^0-9]%') THEN 5002
WHEN (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0) THEN 5003
ELSE 5004 END AS errorid,
CASE
WHEN [PermitNumber] LIKE '%[^0-9]%' THEN 'non-numeric [PermitNumber]'
WHEN [PermitPrefix] NOT IN ('D','M','V','S','','P', 'H') THEN 'invalid [PermitPrefix]'
WHEN ([Status] NOT IN('RP','') AND [Status] LIKE '%[^0-9]%') THEN 'invalid [Status]'
WHEN (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0) THEN 'invalid [ExpirationDate]'
ELSE 'unknown error' END AS ErrorMessage
FROM PWDPermitConversionData2
WHERE [PermitNumber] LIKE '%[^0-9]%' --select non-numeric values
OR [PermitPrefix] NOT IN ('D','M','V','S')--,'','P', 'H') -- invalid prefix
OR ([Status] NOT IN('RP','', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '99', NULL) AND [Status] LIKE '%[^0-9]%') -- invalid status
OR (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0) -- invalid ExpirationDate
--------------------------------------------------------------------------------------------------------
-- Insert rows with failed test
--------------------------------------------------------------------------------------------------------
INSERT INTO PWDPermitConversionErrors2
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
SELECT
PWDPermitConversionDataID,
0,
errorid,
ErrorMessage
FROM #inv_PWDConvData
--------------------------------------------------------------------------------------------------------
-- Insert the primary record data inside a try/catch block
--------------------------------------------------------------------------------------------------------
Begin Try
INSERT INTO PWDPermit2
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate]
)
OUTPUT INSERTED.[PermitNo], INSERTED.[CustNo]
INTO @MyTableVar
SELECT
CASE WHEN PermitNumber = '' THEN NULL
ELSE CAST(PermitNumber AS INT)
END,
CustomerNumber,
CASE PermitPrefix
WHEN 'D' THEN @PermitType_Org
WHEN 'M' THEN @PermitType_Ind
WHEN 'V' THEN @PermitType_Temp
WHEN 'S' THEN @PermitType_Sticker
--WHEN '' THEN NULL
ELSE PermitPrefix -- This will make it fail for sure
END,
CASE [Status] WHEN 'RP' THEN @PermitStatus_RU
--WHEN '' THEN NULL
ELSE CAST([Status] AS INT)
END,
CASE WHEN PermitPrefix = 'S' THEN @PermitDeviceType_Sticker
WHEN PermitPrefix IN ('D', 'M', 'V') THEN @PermitDeviceType_Placard
--WHEN PermitPrefix = '' THEN NULL
ELSE @PermitDeviceType_Unknown
END,
CASE WHEN Location = '' OR Location IS NULL THEN '999'
ELSE Location
END,
CASE WHEN HCExam = '' OR HCExam IS NULL THEN '999'
ELSE HCExam
END,
CASE WHEN HCStation = '' OR HCStation IS NULL THEN '99'
ELSE HCStation
END,
CAST(IssueDate AS DATETIME),
CASE WHEN ExpirationDate = 'NONE' OR ExpirationDate = '' OR ExpirationDate = 'NOE' THEN @MaxDate
ELSE CAST(ExpirationDate AS DATETIME)
END,
CASE WHEN InvalidDLNumber = 'Y' THEN @True
ELSE @False
END,
@False,
@User,
@Today
FROM PWDPermitConversionData2
WHERE
NOT EXISTS
(
SELECT 1
FROM #inv_PWDConvData
WHERE #inv_PWDConvData.PWDPermitConversionDataID = PWDPermitConversionData2.PWDPermitConversionDataID
)
AND CustomerNumber > 0 -- which also cover NOT NULL
AND ConversionStatus = 0
AND PermitPrefix NOT IN ('P', 'H')
AND [Status] <> '06'
AND
(
(
(
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END IS NULL
OR
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END > @DateMinus1Year
)
AND PermitPrefix <> 'M'
)
OR
(
PermitPrefix = 'M'
)
)
End Try
Begin Catch
Print ''
Print 'Insert failed'
Print '----------------------------------------'
Print Cast(Error_Message() as varchar(max))
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the primary record
--------------------------------------------------------------------------------------------------------
UPDATE PWDPermitConversionData2
SET
conversionstatus = ISNULL(i.[PWDPermitConversionDataID],1),
ConversionErrorMessage = i.[ErrorMessage]
FROM PWDPermitConversionData2 p
LEFT OUTER JOIN #inv_PWDConvData i
ON p.[PWDPermitConversionDataID] = i.[PWDPermitConversionDataID]
AND p.conversionstatus = 0
April 15, 2010 at 11:31 am
First: Glad you like the solution (so far) 😀
Second: Sorry that I left yesterday without any further motice... It was 1am over here so I decided to take a nap and just got back from work...
I'm going to work on the 2nd part in just a few minutes. Keep you informed. 😉
April 15, 2010 at 12:39 pm
The update of the conversionstatus statement isn't working how I want because it updates ALL of the records even though only 557000 of the 680000 are getting inserted (because of stuff like Status <> '06').
Because of this, I have tweaked it a little, but I was wondering how flexible the Output statment is. I tried this:
OUTPUT INSERTED.[PermitNo], INSERTED.[CustNo], PWDPermitConversionData2.[PWDPermitConversionDataID]
INTO @MyTableVar
I'm assuming since it says "multi-part identifier could not be bound" that I'm not able to retian the values from the source table?
I'm trying to update the PWDPermitConversionData table based on the values that go into that @MyTableVar table by the PWDPermitConversionData tables primary key.
Edit: Would the only way to accomplish this be by adding the PWDPermitConversionDataID field in the final (PWDPermit) table? Then, once conversion is over, I could just go through and delete that column from the final table since it wouldn't be needed anymore.
April 15, 2010 at 1:17 pm
gregory.anderson (4/15/2010)
The update of the conversionstatus statement isn't working how I want because it updates ALL of the records even though only 557000 of the 680000 are getting inserted (because of stuff like Status <> '06').Because of this, I have tweaked it a little, but I was wondering how flexible the Output statement is. I tried this:
OUTPUT INSERTED.[PermitNo], INSERTED.[CustNo], PWDPermitConversionData2.[PWDPermitConversionDataID]
INTO @MyTableVar
I'm assuming since it says "multi-part identifier could not be bound" that I'm not able to retian the values from the source table?
I'm trying to update the PWDPermitConversionData table based on the values that go into that @MyTableVar table by the PWDPermitConversionData tables primary key.
Edit: Would the only way to accomplish this be by adding the PWDPermitConversionDataID field in the final (PWDPermit) table? Then, once conversion is over, I could just go through and delete that column from the final table since it wouldn't be needed anymore.
Yes, you can only use column in the OUTPUT clause that are part of the INSDERT/UPDATE/DELETE.
Your approach of using @MyTableVar goes into the right direction. However, we might need to insert those values into a temp table after the update statement, so we can benefit from statistics (a table variable is considered by the query optimizer to have only one row, so we might end up with a bad query plan). But we'll get there once we're in the "tuning section". Right now it's still "loop replacement" 🙂
To add the PWDPermitConversionDataID column to the final table would help a lot, since after that you'd have an ID value to join to. I wasn't sure if the columns I used would make the rows unique... Obviously, they didn't.
I wouldn't remove that column afterwards or update it. I'd just leave it in there. Otherwise you'd have to invest additional time. You'd invest 4 bytes which can easily be gathered by changing the VARCHAR() data types to CHAR() - as mentioned before.
Side note:
I really enjoy working on this "challenge" for several reasons:
#1 (and most important): You take the code provided as something to start with and modify/improve it to meet your requirements. So it's not "spoon-feeding", it's team work!
#2: If there's something you struggle with, you just ask. And if we provide a link, you just study it. Great!!
#3: It seems like you're putting some real effort in trying to understand why this set based solution is much more efficient than the RBAR approach(row-by-agonizing-row, a Modenism, for details please have a look at the signature of Jeff Moden on this site). And that's one of the very best things that can ever happen: if we can convince somebody to deal with sets instead of rows.
and #4: it seems like we can make some "minor improvement" in terms of processing time, which usually isn't a bad thing. But that's just a "side effect" or a logical consequence of the aforementioned points. 😉
The next code snippet will follow right away... Stay tuned 😀
April 15, 2010 at 1:32 pm
and here we go:
Short explanation:
a CTE (aka CommonTableExpression) is similar to a subquery, but just easier to read.
It can also be used to do some recursive joins. For details please see common_table_expression.
The CROSS APPLY is similar to a CROSS JOIN in this special case. For details please have a look at Pauls excellent article (just a few days ago)[/url].
Furthermore, I use some kind of a Tally table on the fly. For details on the Tally table concept please have a look at the first link in my signature.
As you can see, I replaced the dynamic SQL with a bunch of CASE statements. Not really compressed code, but readable. If required we might need to change that section. But I'm sure once we have a set based base line there will be a few more people jumping in to make the code even faster. Right, Wayne?
And here's the code:
-- We only want to do previous permits that are for individuals
;WITH cte AS
(
SELECT
PWDPermitConversionDataID,
CustomerNumber,
PermitNumber,
PermitPrefix,
[Status],
ExpirationDate,
IssueDate,
n,
CASE n
WHEN 1 THEN PrevPermPref1
WHEN 2 THEN PrevPermPref2
WHEN 3 THEN PrevPermPref3
WHEN 4 THEN PrevPermPref4
WHEN 5 THEN PrevPermPref5
WHEN 6 THEN PrevPermPref6
WHEN 7 THEN PrevPermPref7
WHEN 8 THEN PrevPermPref8
WHEN 9 THEN PrevPermPref9
WHEN 10 THEN PrevPermPref10
END AS [PrevPermPref],
CASE n
WHEN 1 THEN PrevPermNum1
WHEN 2 THEN PrevPermNum2
WHEN 3 THEN PrevPermNum3
WHEN 4 THEN PrevPermNum4
WHEN 5 THEN PrevPermNum5
WHEN 6 THEN PrevPermNum6
WHEN 7 THEN PrevPermNum7
WHEN 8 THEN PrevPermNum8
WHEN 9 THEN PrevPermNum9
WHEN 10 THEN PrevPermNum10
END AS [PrevPermNum],
CASE n
WHEN 1 THEN PrevPermStatus1
WHEN 2 THEN PrevPermStatus2
WHEN 3 THEN PrevPermStatus3
WHEN 4 THEN PrevPermStatus4
WHEN 5 THEN PrevPermStatus5
WHEN 6 THEN PrevPermStatus6
WHEN 7 THEN PrevPermStatus7
WHEN 8 THEN PrevPermStatus8
WHEN 9 THEN PrevPermStatus9
WHEN 10 THEN PrevPermStatus10
END AS [PrevPermStatus],
CASE n
WHEN 1 THEN PrevPermReturn1
WHEN 2 THEN PrevPermReturn2
WHEN 3 THEN PrevPermReturn3
WHEN 4 THEN PrevPermReturn4
WHEN 5 THEN PrevPermReturn5
WHEN 6 THEN PrevPermReturn6
WHEN 7 THEN PrevPermReturn7
WHEN 8 THEN PrevPermReturn8
WHEN 9 THEN PrevPermReturn9
WHEN 10 THEN PrevPermReturn10
END AS [PrevPermReturn],
CASE n
WHEN 1 THEN PrevPermExpiration1
WHEN 2 THEN PrevPermExpiration2
WHEN 3 THEN PrevPermExpiration3
WHEN 4 THEN PrevPermExpiration4
WHEN 5 THEN PrevPermExpiration5
WHEN 6 THEN PrevPermExpiration6
WHEN 7 THEN PrevPermExpiration7
WHEN 8 THEN PrevPermExpiration8
WHEN 9 THEN PrevPermExpiration9
WHEN 10 THEN PrevPermExpiration10
END AS [PrevPermExpiration],
CASE n
WHEN 1 THEN ConversionStatusPrev1
WHEN 2 THEN ConversionStatusPrev2
WHEN 3 THEN ConversionStatusPrev3
WHEN 4 THEN ConversionStatusPrev4
WHEN 5 THEN ConversionStatusPrev5
WHEN 6 THEN ConversionStatusPrev6
WHEN 7 THEN ConversionStatusPrev7
WHEN 8 THEN ConversionStatusPrev8
WHEN 9 THEN ConversionStatusPrev9
WHEN 10 THEN ConversionStatusPrev10
END AS [ConversionStatusPrev]
FROM PWDPermitConversionData
CROSS APPLY
(
SELECT number AS n FROM master..spt_values
WHERE TYPE ='P'
AND number >0
AND number<11
) tally
WHERE PermitPrefix = 'M'
)
INSERT INTO PWDPermit
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate]
)
SELECT
CASE WHEN PermitNumber = '' THEN NULL
ELSE CAST(PermitNumber AS INT)
END,
CustomerNumber,
CASE WHEN PermitPrefix = 'D' THEN @PermitType_Org
WHEN PermitPrefix = 'M' THEN @PermitType_Ind
WHEN PermitPrefix = 'V' THEN @PermitType_Temp
WHEN PermitPrefix = 'S' THEN @PermitType_Sticker
WHEN PermitPrefix = '' THEN NULL
ELSE PermitPrefix
END,
CASE WHEN [Status] = 'RP' THEN @PermitStatus_RU
WHEN [Status] = '' THEN NULL
ELSE CAST([Status] AS INT)
END,
CASE WHEN PermitPrefix = 'S' THEN @PermitDeviceType_Sticker
WHEN PermitPrefix IN ('D', 'M', 'V') THEN @PermitDeviceType_Placard
WHEN PermitPrefix = '' THEN NULL
ELSE @PermitDeviceType_Unknown
END,
'999', -- This is unknown
'999', -- This is unknown
'99', -- This is unknown
CASE WHEN ExpirationDate = 'NONE' OR ExpirationDate = '' OR ExpirationDate = 'NOE' THEN @MinDate
WHEN CONVERT(DATETIME, ExpirationDate) > @Today THEN DATEADD(mm, -1, CONVERT(DATETIME, IssueDate))
ELSE CAST(ExpirationDate AS DATETIME)
END, -- This is unknown
CASE WHEN ExpirationDate = 'NONE' OR ExpirationDate = '' OR ExpirationDate = 'NOE' THEN @MaxDate
ELSE CAST(ExpirationDate AS DATETIME)
END,
@False, -- This is unknown
CASE WHEN PrevPermReturn = 'Y' THEN @True
ELSE @False
END,
@User,
@Today
FROM cte
WHERE PrevPermPref NOT IN ('P', 'H','')
AND ConversionStatusPrev IS NULL
ORDER BY PWDPermitConversionDataID,n
April 15, 2010 at 2:05 pm
lmu92 (4/15/2010)
blah blah blah.... Right, Wayne?
uh, sure, jeff :w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 15, 2010 at 2:12 pm
gregory.anderson (4/15/2010)
The update of the conversionstatus statement isn't working how I want because it updates ALL of the records even though only 557000 of the 680000 are getting inserted (because of stuff like Status <> '06').Because of this, I have tweaked it a little, but I was wondering how flexible the Output statment is. I tried this:
OUTPUT INSERTED.[PermitNo], INSERTED.[CustNo], PWDPermitConversionData2.[PWDPermitConversionDataID]
INTO @MyTableVar
I'm assuming since it says "multi-part identifier could not be bound" that I'm not able to retian the values from the source table?
I'm trying to update the PWDPermitConversionData table based on the values that go into that @MyTableVar table by the PWDPermitConversionData tables primary key.
Edit: Would the only way to accomplish this be by adding the PWDPermitConversionDataID field in the final (PWDPermit) table? Then, once conversion is over, I could just go through and delete that column from the final table since it wouldn't be needed anymore.
You would need to get that from the deleted virtual table. See http://msdn.microsoft.com/en-us/library/ms191300.aspx for more about working with the inserted and deleted virtual tables.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 15, 2010 at 2:16 pm
WayneS (4/15/2010)
lmu92 (4/15/2010)
blah blah blah.... Right, Wayne?uh, sure, jeff :w00t:
You consider my statement as "blah blah blah"? I'm depressed now. :crying:
April 15, 2010 at 2:21 pm
lmu92 (4/15/2010)
Side note:I really enjoy working on this "challenge" for several reasons:
#1 (and most important): You take the code provided as something to start with and modify/improve it to meet your requirements. So it's not "spoon-feeding", it's team work!
#2: If there's something you struggle with, you just ask. And if we provide a link, you just study it. Great!!
#3: It seems like you're putting some real effort in trying to understand why this set based solution is much more efficient than the RBAR approach(row-by-agonizing-row, a Modenism, for details please have a look at the signature of Jeff Moden on this site). And that's one of the very best things that can ever happen: if we can convince somebody to deal with sets instead of rows.
and #4: it seems like we can make some "minor improvement" in terms of processing time, which usually isn't a bad thing. But that's just a "side effect" or a logical consequence of the aforementioned points. 😉
Lutz, I agree 100% with you. I've been pretty impressed with how much effort Gregory is also putting into this to not only get it to work, but in understanding it.
And Gregory... I would venture to say that if you weren't doing all of this, you would have been dropped. People wanting to be spoon-fed around here just don't get a lot of in-depth help. It's been stated many times: "help us help you". Well, you are definitely doing that, and when you are this eager to not only help, but learn, a lot of people will bend over backwards to help you out.
Another point... re-working this code has been a lot of WORK for Lutz. Usually when things start getting anywhere near this level, you will see a recommendation to have a consultant come in to help you out. I think it's because of all the WORK that YOU are doing that is why Lutz is still in here. (I hope that you realize that this is all volunteer help going on here.)
I've been impressed with both of you! This is what this community is all about.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 31 through 45 (of 112 total)
You must be logged in to reply to this topic. Login to reply