SQL to replicate data in a table

[David Shapiro]

Hello Group, I'm hoping to find some shareware or even some tips on how to
generate an insert/update/delete DDL script based on data in tables. I
suppose it's simulating the replication server functionality. We have lots
of reference tables that are moved from the development environment to QA to
production. The data in the tables changes frequently and keeping exact
copies of the deltas between each minor data modification is a pain. It
seems that the trick is to run a command to generate the exact set of insert
and/or update commands to recreate the data in the table on another
database. I made an attemp at writing a stored proc to do this but there are
a lot of potential errors in it.I don't want to use BCP or even the data
transform copy commands because of all the referential integrity, we often
cannot delete the existing data in the target QA or Prod table because it's
being referred to. So I was thinking about some kind of autogenerated script
that looks like (rough DDL...)

-- first check if the row exists...
if (select count(*) from tab1 where tab1_id = 23) = 1
-- the generation script knows that tab1_id is the primary key
update tab1 set tab1_data1 = <data1 from source table>, tab1_data2 =
<data2 from source..> where tab1_id = 23
else
-- the row doesn't exist on target table so insert it...
insert tab1 (tab1_id, tab1_data1, tab1_data2) values
( 23, <data1 from source table>, tab1_data2 = <data2 from source>)

It would also be nice to make the tables the same as far as deletes (rows
that have been removed on the source are also removed on the target) but I
can't figure out how to determine this in a batch mode.

If you've written something like this, or have seen something like this, or
even can can tell me how to coerse the rep-server to simulate all the data
in the table and capture it to a script, I'd be very appreciative for any
help. Thanks, D Shapiro/Nextcard.

[Gorm Larsen]

In article <c%mw3.5409$36.6...@typhoon-sf.snfc21.pbi.net>,
ju...@junk.com says...

> If you've written something like this, or have seen something like this, or
> even can can tell me how to coerse the rep-server to simulate all the data
> in the table and capture it to a script, I'd be very appreciative for any
> help. Thanks, D Shapiro/Nextcard.

Something like this ?

CREATE PROCEDURE REPL_Categories (
@CheckDate SmallDateTime
)
AS
BEGIN TRAN

DELETE FROM Categories
WHERE NOT EXISTS (SELECT CategoryId FROM CAT_Categories
WHERE CategoryAuto = Categories.CategoryAuto)

UPDATE Categories
SET CategoryName = C.CategoryName
FROM CAT_Categories C
WHERE C.CategoryId = Categories.CategoryId
AND C.LastChanged > @CheckDate

INSERT INTO Categories (CategoryAuto, CategoryId,
CategoryName, Langu)
SELECT CategoryAuto, CategoryId, CategoryName, Langu
FROM CAT_Categories
WHERE NOT EXISTS (SELECT CategoryId FROM Categories WHERE
CategoryAuto = CAT_Categories.CategoryAuto)

--
Gorm Larsen
Hjælp til XAL og AxApta? Abonner på news.tadorna.dk