I'm running SQL2K std and Win2k
I'm trying to write a generic procedure for a data checking report.
The basic requirements is to validate certain columns of a table with another table thats has the correct value.
The output is to show only incorrect values eg. the key , the fieldname, the current value and the correct value.
This will be used as a task list for someone to update the data manually.(I don't want it done automatically)
The tricky bit is to make it generic as many tables can be checked and they have different column names.
The number of colmuns to check exceeds 100 per table, so hard coding the column names is not a prefered option.
Not every column will be checked, only the columns in tblValidator.
e.g
The master table may be for a Part Number table called tblItem
Some of the Column headings are
PartNumber, ItemType, PlanCode, Leadtime, Status, Description, etc
A, 010 5 6 20 blah A
B 010 6 6 20 blah B
C 020 6 7 10 blah C
D 020 6 7 20 blah D
E 020 6 8 20 blah E
The field validator table tblValidator
TableName, ItemType, ColumnName, Correct Value
tblItem 010 PlanCode 5
tblItem 010 Leadtime 6
tblItem 010 Status 20
tblItem 020 PlanCode 6
tblItem 020 LeadTime 7
tblItem 020 Status 10
ItemType is used to link the 2 tables togther and a filter on TableName is also used.
The above example will have 2 invalid records with the following output
Partnumber, ItemType, ColumnName, CurrentValue, CorrectValue
A 010 PlanCode 6 5
B 020 Status 20 10
E 020 LeadTime 6 7
I'm struggling to get started on this with the looping required.
Can someone give me a hand
Thanks
P.S.
Ive thought of using a number of Select statements with unions but need some help with the stored proc.
e.g.
select Partnumber, ItemType, PlanCode as ColumnName, PlanCode as CurrentValue
from tblItem
Union All
select Partnumber, ItemType, LeadTime as ColumnName, PlanCode as CurrentValue
from tblItem
Union All
select Partnumber, ItemType, Status as ColumnName, PlanCode as CurrentValue
from tblItem
This coverts the tblItem to a table similar in layout to the tblValidator,
which will make it easy to do a join, but with over 100 columns and overy 50,000 records for this table,
I'll end up with 5M records before i do a compare for incorrect values.
Is this more efficient than cursors or something else?
I'm trying to write a generic procedure for a data checking report.
The basic requirements is to validate certain columns of a table with another table thats has the correct value.
The output is to show only incorrect values eg. the key , the fieldname, the current value and the correct value.
This will be used as a task list for someone to update the data manually.(I don't want it done automatically)
The tricky bit is to make it generic as many tables can be checked and they have different column names.
The number of colmuns to check exceeds 100 per table, so hard coding the column names is not a prefered option.
Not every column will be checked, only the columns in tblValidator.
e.g
The master table may be for a Part Number table called tblItem
Some of the Column headings are
PartNumber, ItemType, PlanCode, Leadtime, Status, Description, etc
A, 010 5 6 20 blah A
B 010 6 6 20 blah B
C 020 6 7 10 blah C
D 020 6 7 20 blah D
E 020 6 8 20 blah E
The field validator table tblValidator
TableName, ItemType, ColumnName, Correct Value
tblItem 010 PlanCode 5
tblItem 010 Leadtime 6
tblItem 010 Status 20
tblItem 020 PlanCode 6
tblItem 020 LeadTime 7
tblItem 020 Status 10
ItemType is used to link the 2 tables togther and a filter on TableName is also used.
The above example will have 2 invalid records with the following output
Partnumber, ItemType, ColumnName, CurrentValue, CorrectValue
A 010 PlanCode 6 5
B 020 Status 20 10
E 020 LeadTime 6 7
I'm struggling to get started on this with the looping required.
Can someone give me a hand
Thanks
P.S.
Ive thought of using a number of Select statements with unions but need some help with the stored proc.
e.g.
select Partnumber, ItemType, PlanCode as ColumnName, PlanCode as CurrentValue
from tblItem
Union All
select Partnumber, ItemType, LeadTime as ColumnName, PlanCode as CurrentValue
from tblItem
Union All
select Partnumber, ItemType, Status as ColumnName, PlanCode as CurrentValue
from tblItem
This coverts the tblItem to a table similar in layout to the tblValidator,
which will make it easy to do a join, but with over 100 columns and overy 50,000 records for this table,
I'll end up with 5M records before i do a compare for incorrect values.
Is this more efficient than cursors or something else?