I need to write a stored procedure that will run every morning or
night.
Basically I need to keep my PRODUCT table in SQL 2000 with up-to-update
information
from the master PRODUCTION table on the mainframe (AS400).
Let's say I have the following for simplicity:
On mainframe:
--
Table name: MASTER_PRODUCT
PRODUCT_ID PROD_DESC
--
1001 KEYBOARD
1002 MOUSE
--
and on the SQL side, I have the same table with the same columns. When
the job
runs and if there is a change in the PROD_DESC in any of the PRODUCT_ID
on the
mainframe, I want to update that PROD_DESC on the SQL side.
I was wondering what would be the appropriate and efficient way to
handle this type
of action/processing using T-SQL in the stored procedure? Is it
possible to write
it without using cursor and looping through and findind out which
record got changed.
Any help would be greatly appreciated.
Thanks in advance!
Sydney Luusydney.luu@.gmail.com wrote:
> Hello,
> I need to write a stored procedure that will run every morning or
> night.
> Basically I need to keep my PRODUCT table in SQL 2000 with
> up-to-update information
> from the master PRODUCTION table on the mainframe (AS400).
> Let's say I have the following for simplicity:
> On mainframe:
> --
> Table name: MASTER_PRODUCT
> --
> PRODUCT_ID PROD_DESC
> --
> 1001 KEYBOARD
> 1002 MOUSE
> --
> and on the SQL side, I have the same table with the same columns.
> When the job
> runs and if there is a change in the PROD_DESC in any of the
> PRODUCT_ID on the
> mainframe, I want to update that PROD_DESC on the SQL side.
> I was wondering what would be the appropriate and efficient way to
> handle this type
> of action/processing using T-SQL in the stored procedure? Is it
> possible to write
> it without using cursor and looping through and findind out which
> record got changed.
> Any help would be greatly appreciated.
> Thanks in advance!
> Sydney Luu
You can use the BINARY_CHECKSUM function to compute a checksum value for
the entire row in the table. You can compare the checksum values of the
SQL Server table to that of the imported mainframe table and update
those rows where the checksum is different.
David Gugick
Imceda Software
www.imceda.com|||How perfect is this method? I have seen it used but had never really used
it. I would have just compared it via a where clause. Also, shouldn't you
use CHECKSUM only in this case, since hopefully you will have proper cased
the values in the transform.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e8Dp33wOFHA.1040@.TK2MSFTNGP12.phx.gbl...
> sydney.luu@.gmail.com wrote:
> You can use the BINARY_CHECKSUM function to compute a checksum value for
> the entire row in the table. You can compare the checksum values of the
> SQL Server table to that of the imported mainframe table and update those
> rows where the checksum is different.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||I've used it and it seems to "Sorta" work.
I'm with you though, I'm not sure I totally trust the checksum gig.
GAJ|||Louis Davidson wrote:
> How perfect is this method? I have seen it used but had never really
> used it. I would have just compared it via a where clause. Also,
> shouldn't you use CHECKSUM only in this case, since hopefully you
> will have proper cased the values in the transform.
>
Louis,
I think it's about as reliable as any checksum or hash. It's possible to
get the same value back after a change, but it's highly unlikely. BOL
recommends the binary version to detect changes in a row. I've only used
to the function as a proof of concept to do what the OP asked (a while
back). If I remember, I used another table that mapped the PK values to
the checksum like the BOL example.
Using the binary version allows you to pick up case changes to any text
(e.g. GReen vs. Green):
create table #emp (col1 int not null identity, col2 varchar(20) not
null)
create table #emp_bc (col1 int not null, checkval int not null)
insert into #emp values ('David GReen')
insert into #emp values ('David Smith')
insert into #emp values ('David Jones')
Insert into #emp_bc (col1, checkval)
Select col1, BINARY_CHECKSUM(*)
From #emp
Select * from #emp_bc
col1 checkval
-- --
1 -163446450
2 -162136984
3 -162735501
Update #emp
set col2 = 'David Green'
Where col1 = 1
Select a.col1, c.col2 as "NewVal"
from #emp_bc a
inner join #emp c
on a.col1 = c.col1
inner join (
Select col1, BINARY_CHECKSUM(*) as "checkval"
From #emp) b
on a.col1 = b.col1
Where a.checkval != b.checkval
col1 NewVal
-- --
1 David Green
OR
SELECT col1
FROM #emp_bc
WHERE EXISTS (
SELECT *
FROM #emp
WHERE #emp.col1 = #emp_bc.col1
AND BINARY_CHECKSUM(*) <> #emp_bc.checkval)
col1
--
1
Drop Table #emp_bc
Drop Table #emp
David Gugick
Imceda Software
www.imceda.com|||pdxJaxon wrote:
> I've used it and it seems to "Sorta" work.
> I'm with you though, I'm not sure I totally trust the checksum gig.
>
> GAJ
It's far from foolproof. These values produces the same checksum:
Select
BINARY_CHECKSUM('A'),
BINARY_CHECKSUM('AAAAAAAAAAAAAAAAA')
Thanks to Narayana over at http://vyaskn.tripod.com
David Gugick
Imceda Software
www.imceda.com|||David
I have used this one to detect the changes
if (select checksum_agg(checksum(*)) from t1)
<> (select checksum_agg(checksum(*)) from t2)
print 'different'
else
print 'probably the same'
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ObQjTgyOFHA.4000@.TK2MSFTNGP15.phx.gbl...
> Louis Davidson wrote:
> Louis,
> I think it's about as reliable as any checksum or hash. It's possible to
> get the same value back after a change, but it's highly unlikely. BOL
> recommends the binary version to detect changes in a row. I've only used
> to the function as a proof of concept to do what the OP asked (a while
> back). If I remember, I used another table that mapped the PK values to
> the checksum like the BOL example.
> Using the binary version allows you to pick up case changes to any text
> (e.g. GReen vs. Green):
> create table #emp (col1 int not null identity, col2 varchar(20) not
> null)
> create table #emp_bc (col1 int not null, checkval int not null)
> insert into #emp values ('David GReen')
> insert into #emp values ('David Smith')
> insert into #emp values ('David Jones')
> Insert into #emp_bc (col1, checkval)
> Select col1, BINARY_CHECKSUM(*)
> From #emp
> Select * from #emp_bc
> col1 checkval
> -- --
> 1 -163446450
> 2 -162136984
> 3 -162735501
> Update #emp
> set col2 = 'David Green'
> Where col1 = 1
> Select a.col1, c.col2 as "NewVal"
> from #emp_bc a
> inner join #emp c
> on a.col1 = c.col1
> inner join (
> Select col1, BINARY_CHECKSUM(*) as "checkval"
> From #emp) b
> on a.col1 = b.col1
> Where a.checkval != b.checkval
> col1 NewVal
> -- --
> 1 David Green
> OR
> SELECT col1
> FROM #emp_bc
> WHERE EXISTS (
> SELECT *
> FROM #emp
> WHERE #emp.col1 = #emp_bc.col1
> AND BINARY_CHECKSUM(*) <> #emp_bc.checkval)
> col1
> --
> 1
>
> Drop Table #emp_bc
> Drop Table #emp
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Uri Dimant wrote:
> David
> I have used this one to detect the changes
> if (select checksum_agg(checksum(*)) from t1)
> <> (select checksum_agg(checksum(*)) from t2)
> print 'different'
> else
> print 'probably the same'
>
That works as well as the binary version. If you're looking for an easy,
but not completely foolproof, method to compare data in tables, I think
it's a good fit. But as my other post mentioned, some values produce the
same checksum... in a lab, and the agg function returns the same value
as well. Whether you're likely to see the same test data in a real world
application is debatable, but it's worth knowing the function is not
foolproof.
David Gugick
Imceda Software
www.imceda.com|||Yep, David
Based on your DDL, CHECKSUM does show a difference on text columns as
opposite BINARY_CHECKSUM does.
create table #emp (col2 varchar(20) not null)
create table #emp_bc (col2 varchar(20) not null)
insert into #emp values ('David GReen')
insert into #emp values ('David Smith')
insert into #emp values ('David Jones')
insert into #emp_bc values ('David Green')
insert into #emp_bc values ('David Smith')
insert into #emp_bc values ('David Jones')
if (select checksum_agg(BINARY_CHECKSUM(*)) from #emp)
<> (select checksum_agg(BINARY_CHECKSUM(*)) from #emp_bc)
print 'different'
else
print 'probably the same'
drop table #emp,#emp_bc
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OHIdfXzOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> Uri Dimant wrote:
> That works as well as the binary version. If you're looking for an easy,
> but not completely foolproof, method to compare data in tables, I think
> it's a good fit. But as my other post mentioned, some values produce the
> same checksum... in a lab, and the agg function returns the same value
> as well. Whether you're likely to see the same test data in a real world
> application is debatable, but it's worth knowing the function is not
> foolproof.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Very
stuff. Thanks for the information.----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uATTb7zOFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Yep, David
> Based on your DDL, CHECKSUM does show a difference on text columns as
> opposite BINARY_CHECKSUM does.
> create table #emp (col2 varchar(20) not null)
> create table #emp_bc (col2 varchar(20) not null)
> insert into #emp values ('David GReen')
> insert into #emp values ('David Smith')
> insert into #emp values ('David Jones')
> insert into #emp_bc values ('David Green')
> insert into #emp_bc values ('David Smith')
> insert into #emp_bc values ('David Jones')
> if (select checksum_agg(BINARY_CHECKSUM(*)) from #emp)
> <> (select checksum_agg(BINARY_CHECKSUM(*)) from #emp_bc)
> print 'different'
> else
> print 'probably the same'
> drop table #emp,#emp_bc
>
>
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OHIdfXzOFHA.1884@.TK2MSFTNGP15.phx.gbl...
>
No comments:
Post a Comment