Most performant way in SQL Server to condense multiple data changes into before and after values -

-

i have sql server database audit records showing changes third party database (openedge). have no control on structure of audit data, nor way third party database audits data changes. i'm left with, example, following data...

source dataset

if follow first 5 rows can see belong transid 1532102 (represents database transaction) transseq represents database action within single transaction.

in columns prefix new audit changes visible. if value null no change field took place.

looking @ data can see transid = 1532102 primaryidentifier changed 2 -2 (row 1), -2 3 (row 3), 3 4 (row 4) , 4 5 (row 5). might notice when primaryidentifier changes 3 4 secondaryidentifier changes 'abcd' 'efgh' (row 4). these multiple changes occurring on single source record. in mind rows 1, 3, 4 & 5 can condensed single row (see below)

enter image description here

ultimately there 2 record changes in transid 1532102..

enter image description here

i need translate these changes single update statement on target database. in order need ensure have single record showing before , after values.

so given source data presented here need produce following data set..

required dataset

what query structures use achieve this? thinking recursive ctes or perhaps using hierarchical structures? need perform possible wanted pose question here in case hadn't considered possible approaches.

thoughts welcome , here's script sample data

declare @testtable table (syncid int, transid int, transseq int, primaryidentifier int, secondaryidentifier nchar(4), newprimaryidentifier int, newsecondaryidentifier nchar(4), newlevel int, newvalue nvarchar(20)) insert  @testtable         select 128, 1532102, 0,  2, 'abcd',   -2,   null, null, 'test data' union   select 128, 1532102, 1,  3, 'abcd',    2,   null, null, null union   select 128, 1532102, 2, -2, 'abcd',    3,   null, null, null union   select 128, 1532102, 3,  3, 'abcd',    4, 'efgh', null, null union   select 128, 1532102, 4,  4, 'efgh',    5,   null,    2, null union   select 128, 1532102, 5,  5, 'efgh', null, 'ghfi', null, null union   select 128, 1532106, 0,  3, 'abcd',   -3,   null, null, null union   select 128, 1532106, 1,  4, 'abcd',    3,   null, null, null union   select 128, 1532106, 2, -3, 'abcd',    4,   null, null, null union   select 128, 1532110, 0,  4, 'abcd',   -4,   null, null, null union   select 128, 1532110, 1,  5, 'abcd',    4,   null, null, null union   select 128, 1532110, 2, -4, 'abcd',    5,   null, null, null union   select 128, 1532114, 0,  5, 'abcd',   -5,   null, null, null union   select 128, 1532114, 1,  4, 'abcd',    5,   null,    1, null union   select 128, 1532114, 2, -5, 'abcd',    4,   null, null, 'some more test data'  select  *    @testtable

edit: i've been unable write queries track identifier changes. can - need query tracks changes in primaryidentifier values , provides single record each tracking start values , end values.

edit 2: there's been deleted answer suggests update key identifiers not possible when condensed , should step through changes instead. thought valuable add comments further info question..

i need condense dataset because of volume of audit records being generated; of unecessary because of way source dbms makes changes. need reduce dataset , need track key identifier changes. update should possible without clashing on id change during update statement - see this example.

i assume that
1) (primaryidentifier, secondaryidentifier) pk of target table,
2) every transacton in audit table leaves target table in consistent state. update of pk in single statement every transaction using case run ok:

declare @t table (id int primary key, old int); insert @t(id, old) values (4,4),(5,5); update @t set id = case id       when 4 5       when 5 4 end; select * @t;

the plan 1. condense transactions 2. generate update sql temp table. can run or selected items temp table. every item of form

update mytable set           primaryidentifier = case when primaryidentifier=2 , secondaryidentifier='abcd' 5                                    when primaryidentifier=3 , secondaryidentifier='abcd' 2 end,           secondaryidentifier = case when primaryidentifier=2 , secondaryidentifier='abcd' 'efgh'                                     when primaryidentifier=3 , secondaryidentifier='abcd' 'abcd' end ,          level= case when primaryidentifier=2 , secondaryidentifier='abcd' 2                      when primaryidentifier=3 , secondaryidentifier='abcd'  level  end ,          value= case when primaryidentifier=2 , secondaryidentifier='abcd' 'test data'                      when primaryidentifier=3 , secondaryidentifier='abcd'  value  end 1=2 or (primaryidentifier=2 , secondaryidentifier='abcd')            or (primaryidentifier=3 , secondaryidentifier='abcd')

the query

declare @testtable table (syncid int, transid int, transseq int, primaryidentifier int, secondaryidentifier nchar(4), newprimaryidentifier int, newsecondaryidentifier nchar(4), newlevel int, newvalue nvarchar(20)) insert  @testtable         select 128, 1532102, 0,  2, 'abcd', -2, null,   null,   'test data' union   select 128, 1532102, 1,  3, 'abcd',  2, null,   null,   null union   select 128, 1532102, 2, -2, 'abcd',  3, null,   null,   null union   select 128, 1532102, 3,  3, 'abcd',  4, 'efgh', null,   null union   select 128, 1532102, 4,  4, 'efgh',  5, null,   2,      null union   select 128, 1532106, 0,  3, 'abcd', -3, null,   null,   null union   select 128, 1532106, 1,  4, 'abcd',  3, null,   null,   null union   select 128, 1532106, 2, -3, 'abcd',  4, null,   null,   null union   select 128, 1532110, 0,  4, 'abcd', -4, null,   null,   null union   select 128, 1532110, 1,  5, 'abcd',  4, null,   null,   null union   select 128, 1532110, 2, -4, 'abcd',  5, null,   null,   null union   select 128, 1532110, 3,  5, 'abcd',  6, null,   null,   null union   select 128, 1532110, 4,  6, 'abcd',  5, null,   null,   null union   select 128, 1532114, 0,  5, 'abcd', -5, null,   null,   null union   select 128, 1532114, 1,  4, 'abcd',  5, null,   1,      null union   select 128, 1532114, 2, -5, 'abcd',  4, null,   null,   'some more test data' ; root (     -- top parent updates within transactions     select syncid, transid, transseq, primaryidentifier rprimaryidentifier, secondaryidentifier rsecondaryidentifier,      newprimaryidentifier,      coalesce(newsecondaryidentifier, secondaryidentifier) newsecondaryidentifier,     newlevel, newvalue      @testtable t     not exists (select 1                      @testtable t2                     t2.syncid=t.syncid , t2.transid = t.transid                        , t2.transseq < t.transseq                         , t.primaryidentifier = t2.newprimaryidentifier                        , t.secondaryidentifier = coalesce(t2.newsecondaryidentifier, t2.secondaryidentifier)                     )     -- recursion track chain of updates     union     select root.syncid, root.transid, t.transseq, rprimaryidentifier, rsecondaryidentifier,          t.newprimaryidentifier,          coalesce(t.newsecondaryidentifier, root.newsecondaryidentifier),          coalesce(root.newlevel, t.newlevel), coalesce(root.newvalue, t.newvalue)     root      join @testtable t on root.syncid=t.syncid , root.transid = t.transid                        , root.transseq < t.transseq                         , t.primaryidentifier = root.newprimaryidentifier                        , t.secondaryidentifier = root.newsecondaryidentifier  ) ,condensed (     -- last update in chain     select top(1) ties *       root     order row_number() on (partition syncid, transid, rprimaryidentifier, rsecondaryidentifier                                  order transseq desc) ) -- generate sql select syncid, transid, sql = 'update mytable set primaryidentifier = case'      + (select ' when primaryidentifier='+ cast(rprimaryidentifier varchar(20))               +' , secondaryidentifier=''' + rsecondaryidentifier               +''' ' + cast(newprimaryidentifier varchar(20))                      condensed c2          c1.syncid = c2.syncid ,  c1.transid= c2.transid         xml path('') )      + ' end,  secondaryidentifier = case'     + (select ' when primaryidentifier='+ cast(rprimaryidentifier varchar(20))               +' , secondaryidentifier=''' + rsecondaryidentifier              +''' ''' + newsecondaryidentifier + ''''         condensed c2          c1.syncid = c2.syncid ,  c1.transid= c2.transid         xml path('') )     + ' end , level= case'     + (select ' when primaryidentifier='+ cast(rprimaryidentifier varchar(20))               +' , secondaryidentifier=''' + rsecondaryidentifier              +''' '               + case when newlevel null ' level ' else cast(newlevel  varchar(20)) end          condensed c2          c1.syncid = c2.syncid ,  c1.transid= c2.transid         xml path('') )     + ' end , value= case'     + (select ' when primaryidentifier='+ cast(rprimaryidentifier varchar(20))               +' , secondaryidentifier=''' + rsecondaryidentifier              +''' '               + case when newvalue null ' value ' else '''' + newvalue + '''' end          condensed c2          c1.syncid = c2.syncid ,  c1.transid= c2.transid         xml path('') )      + ' end'      + ' 1=2'      + (select ' or (primaryidentifier='+ cast(rprimaryidentifier varchar(20))           +' , secondaryidentifier=''' + rsecondaryidentifier +''')'     condensed c2      c1.syncid = c2.syncid ,  c1.transid= c2.transid     xml path('') ) #updsql     condensed c1  group syncid, transid   select *  #updsql order syncid, transid

edit

taking account newprimaryidentifier can null too. see added row @ @testtable. sql generation skipped.

declare @testtable table (syncid int, transid int, transseq int, primaryidentifier int, secondaryidentifier nchar(4), newprimaryidentifier int, newsecondaryidentifier nchar(4), newlevel int, newvalue nvarchar(20)) insert  @testtable         select 128, 1532102, 0,  2, 'abcd', -2, null,   null,   'test data' union   select 128, 1532102, 1,  3, 'abcd',  2, null,   null,   null union   select 128, 1532102, 2, -2, 'abcd',  3, null,   null,   null union   select 128, 1532102, 3,  3, 'abcd',  4, 'efgh', null,   null union   select 128, 1532102, 4,  4, 'efgh',  5, null,   2,      null union   select 128, 1532102, 5,  5, 'efgh', null, 'ghfi', null, null -- added union   select 128, 1532106, 0,  3, 'abcd', -3, null,   null,   null union   select 128, 1532106, 1,  4, 'abcd',  3, null,   null,   null union   select 128, 1532106, 2, -3, 'abcd',  4, null,   null,   null union   select 128, 1532110, 0,  4, 'abcd', -4, null,   null,   null union   select 128, 1532110, 1,  5, 'abcd',  4, null,   null,   null union   select 128, 1532110, 2, -4, 'abcd',  5, null,   null,   null union   select 128, 1532110, 3,  5, 'abcd',  6, null,   null,   null union   select 128, 1532110, 4,  6, 'abcd',  5, null,   null,   null union   select 128, 1532114, 0,  5, 'abcd', -5, null,   null,   null union   select 128, 1532114, 1,  4, 'abcd',  5, null,   1,      null union   select 128, 1532114, 2, -5, 'abcd',  4, null,   null,   'some more test data' ; root (     -- top parent updates within transactions     select syncid, transid, transseq, primaryidentifier rprimaryidentifier, secondaryidentifier rsecondaryidentifier,      coalesce(newprimaryidentifier, primaryidentifier) newprimaryidentifier,     coalesce(newsecondaryidentifier, secondaryidentifier) newsecondaryidentifier,     newlevel, newvalue      @testtable t     not exists (select 1                      @testtable t2                     t2.syncid=t.syncid , t2.transid = t.transid                        , t2.transseq < t.transseq                         , t.primaryidentifier = coalesce(t2.newprimaryidentifier, t2.primaryidentifier)                        , t.secondaryidentifier = coalesce(t2.newsecondaryidentifier, t2.secondaryidentifier)                     )     -- recursion track chain of updates     union     select root.syncid, root.transid, t.transseq, rprimaryidentifier, rsecondaryidentifier,          coalesce(t.newprimaryidentifier, root.newprimaryidentifier),          coalesce(t.newsecondaryidentifier, root.newsecondaryidentifier),          coalesce(t.newlevel, root.newlevel), coalesce(t.newvalue, root.newvalue)     root      join @testtable t on root.syncid=t.syncid , root.transid = t.transid                        , root.transseq < t.transseq                         , t.primaryidentifier = root.newprimaryidentifier                        , t.secondaryidentifier = root.newsecondaryidentifier  ) ,condensed (     -- last update in chain     select top(1) ties *       root     order row_number() on (partition syncid, transid, rprimaryidentifier, rsecondaryidentifier                                  order transseq desc) ) select *  condensed  order syncid, transid, rprimaryidentifier, rsecondaryidentifier

Comments

Post a Comment

Popular posts from this blog

php - How to display all orders for a single product showing the most recent first? Woocommerce -

-
i have password protected page on frontend. on page, want display full list of purchases associated single woocommerce product id. option 1: i've looked through woocommerce docs , , found: wc_cli_order , list_( $args, $assoc_args ) function lists orders. presumably, orders can filtered product id [--=] filter orders based on order property. line item fields (numeric array, started index zero): line_items.0.product_id option 2: i found article , based on customer id. i've modified code based on guesses meta_key , meta_value. $customer_orders = get_posts( array( 'numberposts' => -1, 'meta_key' => '_product', 'meta_value' => get_product_id(), 'post_type' => wc_get_order_types(), 'post_status' => array_keys( wc_get_order_statuses() ), ) ); how display orders single product showing recent order first? here solution put based on these articles: http://www.w...
Read more

asp.net - How to correctly use QUERY_STRING in ISAPI rewrite? -

-
i'm having difficult time , hope guys can help. using helicon isapi rewrite version 3.1.0.104 on our iis server. edit http.conf file , have tried , still fail. this trying do: redirect url: https://www.domain.com/switch-by-version?version=2.8.5.2594 to: https://test.domain.com/load/load.aspx?tver=2.8.5.2594 the version number @ end source url change, , need target url have same version number @ end of url example above shows. i tried following not work: rewritecond %{query_string} ^version=(\d\d?)\.(\d\d?)\.(\d\d?)\.(.*)$ [nc] rewriterule ^/switch-by-version(.*)$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l] any appreciated! thanks. if it's running in equivalent context of .htaccess , rule shouldn't start / . here's simpler version: rewritecond %{query_string} ^version=(\d+\.\d+\.\d+(?:\..+)?)$ [nc] rewriterule ^switch-by-version/?$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l]
Read more

angularjs - How restrict admin panel using in backend laravel and admin panel on angular? -

-
https://scotch.io/tutorials/token-based-authentication-for-angularjs-and-laravel-apps following tutorials, try write authentication restrict admin panel, didn't understood how can that. this admin panel, admin panel don't want show , request database unauthenticated users. my laravel-routes route::get('/admin',function(){ return view('index'); }); route::get('/api/v1/employees/{id?}', 'employees@index'); route::get('/api/v1/users/{id?}', 'employees@users'); route::post('/api/v1/employees', 'employees@store'); route::post('/api/v1/employees/{id}', 'employees@update'); route::delete('/api/v1/employees/{id}', 'employees@destroy'); route::group(['prefix' => 'api'], function() { route::resource('authenticate', 'authenticatecontroller', ['only' => ['index']]); route::post('authenticate', 'authentic...
Read more