I have two tables with identical structures (for example):

items_1
id    int    identity
item_no    nvarchar   11
description    nvarchar    255

items_2
id    int    identity
item_no    nvarchar   11
description    nvarchar    255

Users can add, delete and change data in items_1 at will using forms,
etc. items_2 is data used to generate intranet webpage views. The goal
is to have all the data in items_2 (including the identity column id)
match the data in items_1, once all changes are ready for comittment.

Can someone help me with a StoredProcedure that can do this? The only
thing I can think to do is:

1. Check each id in items_2 to see if it exists in items_1.
        a. If yes, update it.
        b. If no, delete it.
2. Check each id in items_1 to see if it exists in items_2.
        a. If yes, ignore it (already updated above)
        b. If no, add it.

Does this make sense? Is there some easier way?

Make the id column in items_2 *not* an identity column.  Then your job is
simple.

DELETE items_2
INSERT items_2 SELECT id, item_no, description FROM items_1

I do something very similar to this in a web page administration tool I just
built for a client.  When they go to edit the page, I take the content from
the live table and stuff it in a staging table.  They can preview their
changes on the same page, continually updating the staging table.  When they
are happy with it, I do something very similar to the two SQL statements I
mentioned above.

A


Show quoteHide quote

Thank you.

Show quote Hide quote Having duplicate sets of data seems silly in this case.  If you must do
this, you might look at using replication to keep things synchronized, or
simply use a view on items_1.

Before you do that however, it looks like you need to run a cleanup process.
The outline you gave above should do the trick.

Rick Sawtell
MCT, MCSD, MCDBA

Well, I think that really depends.  What if they want to make all those
changes and then decide they want to revert?  It sounded to me like they
wanted to test things, play with them, and preview them before making the
changes to the *real* data (which may very well be on a web site or a live
client-server app).

A

I think I misunderstood what they were after.

Rick

If a significant portion of data is changed, I would:

drop table items_2 /* very fast */

select *
into items_2
from items_1

If a very small portion of data is changed, I would:

1. add a column last_modified_date to both tables
and maintain it via defaults and triggers
2. refresh like this:

delete from item_2 where id not in(select id from item_1)
or exists(select 1 from item_1 where item_1.id=item_2.id
  and item_1.last_modified_date > item_2.last_modified_date)

insert into item_2 select * from item_1 where last_modified_date >
(select max(last_modified_date) from item_2)

The solution Aaron provided was perfect for what I was doing. I did
elect to TRUNCATE the table rather than DELETE FROM as that seems to be
executing much faster for me.

As for the purpose of having duplicate data, Aaron was again right on.
I needed to give the user the ability to modify and preview lots of
potential changes before executing to a live database that is used to
dynamically display web pages. This provides two functions:

1. The user can do a bulk revert if they've gotten too far in and would
rather start over, and
2. The customer needs to maintain consistency between their site
(ecommerce), an inventory list that is stored by their payment gateway,
and miscellaneous payment feeds. If prices are different for any time
period longer than seconds, you can imagine how confused the checkout
process would be for customers. (The webpage said $0.50, but the
shopping cart says $0.60....)

Doing things this way, I can allow them to make potentially hours worth
of changes, and then once their preview is accurate, they can run one
utility which then generates the required product feeds from the
modified database, and uploads them via FTP, then update their payment
gateway inventory using the modified data and upload it, and then last
but not least, move the changes to the live database, which then
updates the webpages. This way all 6 places can have nearly instanteous
synchronicity (the whole process now takes just around 6 seconds to
generate 4 product feeds, an rss catalog feed, upload the feeds via FTP
to the various shopping sites, and move the data).

Thanks again for your help. Sometimes the simple answer is the one I
miss.

Well, just to play devil's advocate, there is always the chance that the
bulk set of changes are made directly between a customer looking at a
product screen and adding it to their cart, and/or looking at their cart and
checking out.  In both cases, the price won't be what they originally
thought.  For content it's not as big a deal, but for prices (or anything
that affects what the customer pays or gets), your best bet is to schedule
the changes for a maintenance window and/or ensure that no users are logged
in (e.g. by restarting the server / application just prior to making the
changes).

A

calculate base 32 notation from int value?
How to SELECT data among 4 tables?
Optimizing queries
Inserting into joined tables
Better way to do a large delete?