|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL to synchronize two tablesThis is probably a common requirement, but I need help in getting this going. I have a table declared as CREATE TABLE iTransRow ( idLoc TINYINT NOT NULL, idSeq INTEGER NOT NULL, idLine INTEGER NOT NULL, Item INTEGER NULL, ItemExt VARCHAR(14) NULL, PieceCount DECIMAL(7,2) NULL, Pack DECIMAL(8,3) NULL, InvoiceQty DECIMAL(11,5) NULL, Qty DECIMAL(11,5) NULL, Cost DECIMAL(8,5) NULL, SuggestedPrice DECIMAL(8,5) NULL, Price DECIMAL(8,5) NULL, ListPrice DECIMAL(8,5) NULL, Tax DECIMAL(9,4) NULL, TaxPercent DECIMAL(9,4) NULL, TaxGroup TINYINT NULL, Discounts DECIMAL(9,4) NULL, Expenses DECIMAL(9,4) NULL, OtherTaxes DECIMAL(9,4) NULL, Freight DECIMAL(9,4) NULL, TransactionDate DATETIME NULL, Notes TINYINT NULL, Status TINYINT NULL, CONSTRAINT iTranR_PRIMARY PRIMARY KEY (idLoc,idSeq,idLine) ) GO And a second identical table CREATE TABLE iTrans2Row ( idLoc TINYINT NOT NULL, idSeq INTEGER NOT NULL, idLine INTEGER NOT NULL, Item INTEGER NULL, ItemExt VARCHAR(14) NULL, PieceCount DECIMAL(7,2) NULL, Pack DECIMAL(8,3) NULL, InvoiceQty DECIMAL(11,5) NULL, Qty DECIMAL(11,5) NULL, Cost DECIMAL(8,5) NULL, SuggestedPrice DECIMAL(8,5) NULL, Price DECIMAL(8,5) NULL, ListPrice DECIMAL(8,5) NULL, Tax DECIMAL(9,4) NULL, TaxPercent DECIMAL(9,4) NULL, TaxGroup TINYINT NULL, Discounts DECIMAL(9,4) NULL, Expenses DECIMAL(9,4) NULL, OtherTaxes DECIMAL(9,4) NULL, Freight DECIMAL(9,4) NULL, TransactionDate DATETIME NULL, Notes TINYINT NULL, Status TINYINT NULL, CONSTRAINT iTran2R_PRIMARY PRIMARY KEY (idLoc,idSeq,idLine) ) GO I need to write a stored procedure to synchronize all the rows from iTransRow to iTrans2Row for a given idLoc and idSeq. By synchronize I mean (can be done in any order): 1) Delete all rows in iTrans2Row that are not in iTransRow, 2) Update all rows in iTrans2Row which have different data in iTransRow, 3) Insert all rows which are in iTransRow but are not in iTrans2Row. In addition, I need this to be efficient and to be atomic (the process should complete fully, or no changes should be committed) because I need to do this for about 1200 transactions per day (a transaction is given by an IDLoc/IDSeq pair), and each transaction may have up to about 25000 rows. Could someone with more experience help me out with this? Thanks in advance, Edgard Edgard
> 1) Delete all rows in iTrans2Row that are not in iTransRow, DELETE FROM iTrans2Row WHERE NOT EXISTS(SELECT * FROM iTransRow WHERE iTrans2Row.idLoc=iTransRow.RowidLoc AND iTrans2Row.idSeq=iTransRow.idSeq AND i iTrans2Row.idLine =iTransRow.idLine ) > 2) Update all rows in iTrans2Row which have different data in iTransRow, UPDATE iTrans2Row SET col=I.col,col1=I.col1.........FROM iTrans2Row JOIN iTransRow I ON I.YourPK=iTrans2Row.YoyurPK AND (col<>I.col OR col1<>I.col1 OR .........) > 3) Insert all rows which are in iTransRow but are not in iTrans2Row. INSERT INTO iTransRow (column lists) SELECT <column list> FROM iTrans2RowWHERE NOT EXISTS (SELECT * FROM iTransRow WHERE iTrans2Row.idLoc=iTransRow.RowidLoc AND iTrans2Row.idSeq=iTransRow.idSeq AND i iTrans2Row.idLine =iTransRow.idLine ) > In addition, I need this to be efficient and to be atomic (the process Please read up about SET ISOLATION LEVEL in the BOL as well as specify BEGIN > should complete fully, or no changes should be committed) because I need > to do this for about 1200 transactions per day (a transaction is given by > an IDLoc/IDSeq pair), and each transaction may have up to about 25000 > rows. TRAN ..COMMIT to wrap the DML. Show quote "Edgard L. Riba" <elriba at rimith dot com> wrote in message news:O4u7uvcpFHA.2976@TK2MSFTNGP12.phx.gbl... > Hi, > > This is probably a common requirement, but I need help in getting this > going. > > I have a table declared as > > CREATE TABLE iTransRow ( > idLoc TINYINT NOT NULL, > idSeq INTEGER NOT NULL, > idLine INTEGER NOT NULL, > Item INTEGER NULL, > ItemExt VARCHAR(14) NULL, > PieceCount DECIMAL(7,2) NULL, > Pack DECIMAL(8,3) NULL, > InvoiceQty DECIMAL(11,5) NULL, > Qty DECIMAL(11,5) NULL, > Cost DECIMAL(8,5) NULL, > SuggestedPrice DECIMAL(8,5) NULL, > Price DECIMAL(8,5) NULL, > ListPrice DECIMAL(8,5) NULL, > Tax DECIMAL(9,4) NULL, > TaxPercent DECIMAL(9,4) NULL, > TaxGroup TINYINT NULL, > Discounts DECIMAL(9,4) NULL, > Expenses DECIMAL(9,4) NULL, > OtherTaxes DECIMAL(9,4) NULL, > Freight DECIMAL(9,4) NULL, > TransactionDate DATETIME NULL, > Notes TINYINT NULL, > Status TINYINT NULL, > CONSTRAINT iTranR_PRIMARY PRIMARY KEY (idLoc,idSeq,idLine) > ) > GO > > And a second identical table > > CREATE TABLE iTrans2Row ( > idLoc TINYINT NOT NULL, > idSeq INTEGER NOT NULL, > idLine INTEGER NOT NULL, > Item INTEGER NULL, > ItemExt VARCHAR(14) NULL, > PieceCount DECIMAL(7,2) NULL, > Pack DECIMAL(8,3) NULL, > InvoiceQty DECIMAL(11,5) NULL, > Qty DECIMAL(11,5) NULL, > Cost DECIMAL(8,5) NULL, > SuggestedPrice DECIMAL(8,5) NULL, > Price DECIMAL(8,5) NULL, > ListPrice DECIMAL(8,5) NULL, > Tax DECIMAL(9,4) NULL, > TaxPercent DECIMAL(9,4) NULL, > TaxGroup TINYINT NULL, > Discounts DECIMAL(9,4) NULL, > Expenses DECIMAL(9,4) NULL, > OtherTaxes DECIMAL(9,4) NULL, > Freight DECIMAL(9,4) NULL, > TransactionDate DATETIME NULL, > Notes TINYINT NULL, > Status TINYINT NULL, > CONSTRAINT iTran2R_PRIMARY PRIMARY KEY (idLoc,idSeq,idLine) > ) > GO > > > I need to write a stored procedure to synchronize all the rows from > iTransRow to iTrans2Row for a given idLoc and idSeq. By synchronize I > mean (can be done in any order): > 1) Delete all rows in iTrans2Row that are not in iTransRow, > 2) Update all rows in iTrans2Row which have different data in iTransRow, > 3) Insert all rows which are in iTransRow but are not in iTrans2Row. > > In addition, I need this to be efficient and to be atomic (the process > should complete fully, or no changes should be committed) because I need > to do this for about 1200 transactions per day (a transaction is given by > an IDLoc/IDSeq pair), and each transaction may have up to about 25000 > rows. > > Could someone with more experience help me out with this? > > Thanks in advance, > Edgard > > It will be simpler to delete all and re insert them:
DELETE FROM iTrans2Row [WHERE...] INSERT INTO iTrans2Row (column lists) SELECT <column list> FROM iTransRow [WHERE...] Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:uyKo0EhpFHA.1456@TK2MSFTNGP10.phx.gbl... > Edgard > > 1) Delete all rows in iTrans2Row that are not in iTransRow, > DELETE FROM iTrans2Row WHERE NOT EXISTS > (SELECT * FROM iTransRow WHERE iTrans2Row.idLoc=iTransRow.RowidLoc > AND iTrans2Row.idSeq=iTransRow.idSeq AND i iTrans2Row.idLine > =iTransRow.idLine ) > > > 2) Update all rows in iTrans2Row which have different data in iTransRow, > > UPDATE iTrans2Row SET col=I.col,col1=I.col1......... > FROM iTrans2Row JOIN iTransRow I ON I.YourPK=iTrans2Row.YoyurPK > AND (col<>I.col OR col1<>I.col1 OR .........) > > > > 3) Insert all rows which are in iTransRow but are not in iTrans2Row. > > INSERT INTO iTransRow (column lists) SELECT <column list> FROM iTrans2Row > WHERE NOT EXISTS > (SELECT * FROM iTransRow WHERE iTrans2Row.idLoc=iTransRow.RowidLoc > AND iTrans2Row.idSeq=iTransRow.idSeq AND i iTrans2Row.idLine > =iTransRow.idLine ) > > > > In addition, I need this to be efficient and to be atomic (the process > > should complete fully, or no changes should be committed) because I need > > to do this for about 1200 transactions per day (a transaction is given by > > an IDLoc/IDSeq pair), and each transaction may have up to about 25000 > > rows. > > Please read up about SET ISOLATION LEVEL in the BOL as well as specify BEGIN > TRAN ..COMMIT to wrap the DML. > > > > > "Edgard L. Riba" <elriba at rimith dot com> wrote in message > news:O4u7uvcpFHA.2976@TK2MSFTNGP12.phx.gbl... > > Hi, > > > > This is probably a common requirement, but I need help in getting this > > going. > > > > I have a table declared as > > > > CREATE TABLE iTransRow ( > > idLoc TINYINT NOT NULL, > > idSeq INTEGER NOT NULL, > > idLine INTEGER NOT NULL, > > Item INTEGER NULL, > > ItemExt VARCHAR(14) NULL, > > PieceCount DECIMAL(7,2) NULL, > > Pack DECIMAL(8,3) NULL, > > InvoiceQty DECIMAL(11,5) NULL, > > Qty DECIMAL(11,5) NULL, > > Cost DECIMAL(8,5) NULL, > > SuggestedPrice DECIMAL(8,5) NULL, > > Price DECIMAL(8,5) NULL, > > ListPrice DECIMAL(8,5) NULL, > > Tax DECIMAL(9,4) NULL, > > TaxPercent DECIMAL(9,4) NULL, > > TaxGroup TINYINT NULL, > > Discounts DECIMAL(9,4) NULL, > > Expenses DECIMAL(9,4) NULL, > > OtherTaxes DECIMAL(9,4) NULL, > > Freight DECIMAL(9,4) NULL, > > TransactionDate DATETIME NULL, > > Notes TINYINT NULL, > > Status TINYINT NULL, > > CONSTRAINT iTranR_PRIMARY PRIMARY KEY (idLoc,idSeq,idLine) > > ) > > GO > > > > And a second identical table > > > > CREATE TABLE iTrans2Row ( > > idLoc TINYINT NOT NULL, > > idSeq INTEGER NOT NULL, > > idLine INTEGER NOT NULL, > > Item INTEGER NULL, > > ItemExt VARCHAR(14) NULL, > > PieceCount DECIMAL(7,2) NULL, > > Pack DECIMAL(8,3) NULL, > > InvoiceQty DECIMAL(11,5) NULL, > > Qty DECIMAL(11,5) NULL, > > Cost DECIMAL(8,5) NULL, > > SuggestedPrice DECIMAL(8,5) NULL, > > Price DECIMAL(8,5) NULL, > > ListPrice DECIMAL(8,5) NULL, > > Tax DECIMAL(9,4) NULL, > > TaxPercent DECIMAL(9,4) NULL, > > TaxGroup TINYINT NULL, > > Discounts DECIMAL(9,4) NULL, > > Expenses DECIMAL(9,4) NULL, > > OtherTaxes DECIMAL(9,4) NULL, > > Freight DECIMAL(9,4) NULL, > > TransactionDate DATETIME NULL, > > Notes TINYINT NULL, > > Status TINYINT NULL, > > CONSTRAINT iTran2R_PRIMARY PRIMARY KEY (idLoc,idSeq,idLine) > > ) > > GO > > > > > > I need to write a stored procedure to synchronize all the rows from > > iTransRow to iTrans2Row for a given idLoc and idSeq. By synchronize I > > mean (can be done in any order): > > 1) Delete all rows in iTrans2Row that are not in iTransRow, > > 2) Update all rows in iTrans2Row which have different data in iTransRow, > > 3) Insert all rows which are in iTransRow but are not in iTrans2Row. > > > > In addition, I need this to be efficient and to be atomic (the process > > should complete fully, or no changes should be committed) because I need > > to do this for about 1200 transactions per day (a transaction is given by > > an IDLoc/IDSeq pair), and each transaction may have up to about 25000 > > rows. > > > > Could someone with more experience help me out with this? > > > > Thanks in advance, > > Edgard > > > > > > > It will be simpler to delete all and re insert them: Unless they are referenced by another table, then you must use UPDATE.By the way, the order is important. The OP has it correct, however. DELETES must occur first, so that none of the updates fail due to a primary key or unique constraint violation. UPDATES have to occur next to make room for the INSERTS. BEGIN TRAN DELETE ... UPDATE ... INSERT ... COMMIT Show quote <Moshe> wrote in message news:OX9GylhpFHA.2156@TK2MSFTNGP14.phx.gbl... > It will be simpler to delete all and re insert them: > > DELETE FROM iTrans2Row [WHERE...] > INSERT INTO iTrans2Row (column lists) SELECT <column list> FROM iTransRow > [WHERE...] > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:uyKo0EhpFHA.1456@TK2MSFTNGP10.phx.gbl... > > Edgard > > > 1) Delete all rows in iTrans2Row that are not in iTransRow, > > DELETE FROM iTrans2Row WHERE NOT EXISTS > > (SELECT * FROM iTransRow WHERE iTrans2Row.idLoc=iTransRow.RowidLoc > > AND iTrans2Row.idSeq=iTransRow.idSeq AND i iTrans2Row.idLine > > =iTransRow.idLine ) > > > > > 2) Update all rows in iTrans2Row which have different data in > iTransRow, > > > > UPDATE iTrans2Row SET col=I.col,col1=I.col1......... > > FROM iTrans2Row JOIN iTransRow I ON I.YourPK=iTrans2Row.YoyurPK > > AND (col<>I.col OR col1<>I.col1 OR .........) > > > > > > > 3) Insert all rows which are in iTransRow but are not in iTrans2Row. > > > > INSERT INTO iTransRow (column lists) SELECT <column list> FROM iTrans2Row > > WHERE NOT EXISTS > > (SELECT * FROM iTransRow WHERE iTrans2Row.idLoc=iTransRow.RowidLoc > > AND iTrans2Row.idSeq=iTransRow.idSeq AND i iTrans2Row.idLine > > =iTransRow.idLine ) > > > > > > > In addition, I need this to be efficient and to be atomic (the process > > > should complete fully, or no changes should be committed) because I need > > > to do this for about 1200 transactions per day (a transaction is given > by > > > an IDLoc/IDSeq pair), and each transaction may have up to about 25000 > > > rows. > > > > Please read up about SET ISOLATION LEVEL in the BOL as well as specify > BEGIN > > TRAN ..COMMIT to wrap the DML. > > > > > > > > > > "Edgard L. Riba" <elriba at rimith dot com> wrote in message > > news:O4u7uvcpFHA.2976@TK2MSFTNGP12.phx.gbl... > > > Hi, > > > > > > This is probably a common requirement, but I need help in getting this > > > going. > > > > > > I have a table declared as > > > > > > CREATE TABLE iTransRow ( > > > idLoc TINYINT NOT NULL, > > > idSeq INTEGER NOT NULL, > > > idLine INTEGER NOT NULL, > > > Item INTEGER NULL, > > > ItemExt VARCHAR(14) NULL, > > > PieceCount DECIMAL(7,2) NULL, > > > Pack DECIMAL(8,3) NULL, > > > InvoiceQty DECIMAL(11,5) NULL, > > > Qty DECIMAL(11,5) NULL, > > > Cost DECIMAL(8,5) NULL, > > > SuggestedPrice DECIMAL(8,5) NULL, > > > Price DECIMAL(8,5) NULL, > > > ListPrice DECIMAL(8,5) NULL, > > > Tax DECIMAL(9,4) NULL, > > > TaxPercent DECIMAL(9,4) NULL, > > > TaxGroup TINYINT NULL, > > > Discounts DECIMAL(9,4) NULL, > > > Expenses DECIMAL(9,4) NULL, > > > OtherTaxes DECIMAL(9,4) NULL, > > > Freight DECIMAL(9,4) NULL, > > > TransactionDate DATETIME NULL, > > > Notes TINYINT NULL, > > > Status TINYINT NULL, > > > CONSTRAINT iTranR_PRIMARY PRIMARY KEY (idLoc,idSeq,idLine) > > > ) > > > GO > > > > > > And a second identical table > > > > > > CREATE TABLE iTrans2Row ( > > > idLoc TINYINT NOT NULL, > > > idSeq INTEGER NOT NULL, > > > idLine INTEGER NOT NULL, > > > Item INTEGER NULL, > > > ItemExt VARCHAR(14) NULL, > > > PieceCount DECIMAL(7,2) NULL, > > > Pack DECIMAL(8,3) NULL, > > > InvoiceQty DECIMAL(11,5) NULL, > > > Qty DECIMAL(11,5) NULL, > > > Cost DECIMAL(8,5) NULL, > > > SuggestedPrice DECIMAL(8,5) NULL, > > > Price DECIMAL(8,5) NULL, > > > ListPrice DECIMAL(8,5) NULL, > > > Tax DECIMAL(9,4) NULL, > > > TaxPercent DECIMAL(9,4) NULL, > > > TaxGroup TINYINT NULL, > > > Discounts DECIMAL(9,4) NULL, > > > Expenses DECIMAL(9,4) NULL, > > > OtherTaxes DECIMAL(9,4) NULL, > > > Freight DECIMAL(9,4) NULL, > > > TransactionDate DATETIME NULL, > > > Notes TINYINT NULL, > > > Status TINYINT NULL, > > > CONSTRAINT iTran2R_PRIMARY PRIMARY KEY (idLoc,idSeq,idLine) > > > ) > > > GO > > > > > > > > > I need to write a stored procedure to synchronize all the rows from > > > iTransRow to iTrans2Row for a given idLoc and idSeq. By synchronize I > > > mean (can be done in any order): > > > 1) Delete all rows in iTrans2Row that are not in iTransRow, > > > 2) Update all rows in iTrans2Row which have different data in > iTransRow, > > > 3) Insert all rows which are in iTransRow but are not in iTrans2Row. > > > > > > In addition, I need this to be efficient and to be atomic (the process > > > should complete fully, or no changes should be committed) because I need > > > to do this for about 1200 transactions per day (a transaction is given > by > > > an IDLoc/IDSeq pair), and each transaction may have up to about 25000 > > > rows. > > > > > > Could someone with more experience help me out with this? > > > > > > Thanks in advance, > > > Edgard > > > > > > > > > > > > Why not just create a view on the first table? Having two identical tables
is a recipe for disaster, unless there is a really good reason, which you haven't stated. The only possible reason I can think of is that iTransRow had a ton of indexes on it used for reporting that caused a bottleneck on the applications that change the data, so you've created a separate table just for reporting that is updated periodically. If that is the case, then your best bet would be to create an AFTER INSERT, UPDATE, DELETE trigger on iTransRow that queues changes into an intermediate table without any of the extra indexes, and have a separate process dequeue the changes and commit them to the reporting table. The intermediate table does not have to reside in the same database, so you may be able to use the simple recovery model (careful....), and it can be placed on its own disk subsystem to improve performance. This solution has only moderate impact on the applications that change the data, does not require any modification to either of the existing table definitions (aside from the trigger, of course), and minimizes the locking required on iTransRow. The performance impact of the additional trigger may be offset by the reduced load on iTransRow. A synchronization process must scan both tables to detect any changes, and then commit any changes to the other table. The reads must be done with a sufficient transaction isolation level to ensure that when the changes are committed, nothing in iTransRow will have changed. This is especially true in this case because you will be issuing multiple change statements to update iTrans2Row. Holding shared locks (which will most likely escalate to table locks), will most assuredly impact change throughput--probably to a greater extent than an additional trigger that spools up changes in an intermediate table. Show quote "Edgard L. Riba" <elriba at rimith dot com> wrote in message news:O4u7uvcpFHA.2976@TK2MSFTNGP12.phx.gbl... > Hi, > > This is probably a common requirement, but I need help in getting this > going. > > I have a table declared as > > CREATE TABLE iTransRow ( > idLoc TINYINT NOT NULL, > idSeq INTEGER NOT NULL, > idLine INTEGER NOT NULL, > Item INTEGER NULL, > ItemExt VARCHAR(14) NULL, > PieceCount DECIMAL(7,2) NULL, > Pack DECIMAL(8,3) NULL, > InvoiceQty DECIMAL(11,5) NULL, > Qty DECIMAL(11,5) NULL, > Cost DECIMAL(8,5) NULL, > SuggestedPrice DECIMAL(8,5) NULL, > Price DECIMAL(8,5) NULL, > ListPrice DECIMAL(8,5) NULL, > Tax DECIMAL(9,4) NULL, > TaxPercent DECIMAL(9,4) NULL, > TaxGroup TINYINT NULL, > Discounts DECIMAL(9,4) NULL, > Expenses DECIMAL(9,4) NULL, > OtherTaxes DECIMAL(9,4) NULL, > Freight DECIMAL(9,4) NULL, > TransactionDate DATETIME NULL, > Notes TINYINT NULL, > Status TINYINT NULL, > CONSTRAINT iTranR_PRIMARY PRIMARY KEY (idLoc,idSeq,idLine) > ) > GO > > And a second identical table > > CREATE TABLE iTrans2Row ( > idLoc TINYINT NOT NULL, > idSeq INTEGER NOT NULL, > idLine INTEGER NOT NULL, > Item INTEGER NULL, > ItemExt VARCHAR(14) NULL, > PieceCount DECIMAL(7,2) NULL, > Pack DECIMAL(8,3) NULL, > InvoiceQty DECIMAL(11,5) NULL, > Qty DECIMAL(11,5) NULL, > Cost DECIMAL(8,5) NULL, > SuggestedPrice DECIMAL(8,5) NULL, > Price DECIMAL(8,5) NULL, > ListPrice DECIMAL(8,5) NULL, > Tax DECIMAL(9,4) NULL, > TaxPercent DECIMAL(9,4) NULL, > TaxGroup TINYINT NULL, > Discounts DECIMAL(9,4) NULL, > Expenses DECIMAL(9,4) NULL, > OtherTaxes DECIMAL(9,4) NULL, > Freight DECIMAL(9,4) NULL, > TransactionDate DATETIME NULL, > Notes TINYINT NULL, > Status TINYINT NULL, > CONSTRAINT iTran2R_PRIMARY PRIMARY KEY (idLoc,idSeq,idLine) > ) > GO > > > I need to write a stored procedure to synchronize all the rows from > iTransRow to iTrans2Row for a given idLoc and idSeq. By synchronize I mean > (can be done in any order): > 1) Delete all rows in iTrans2Row that are not in iTransRow, > 2) Update all rows in iTrans2Row which have different data in iTransRow, > 3) Insert all rows which are in iTransRow but are not in iTrans2Row. > > In addition, I need this to be efficient and to be atomic (the process > should complete fully, or no changes should be committed) because I need to > do this for about 1200 transactions per day (a transaction is given by an > IDLoc/IDSeq pair), and each transaction may have up to about 25000 rows. > > Could someone with more experience help me out with this? > > Thanks in advance, > Edgard > >  
Other interesting topics
|
|||||||||||||||||||||||
