Append records in SQL Server 2005 Replication

I am having a problem with my database replication. I had it working once before but accidentally deleted the publication/subscription.

Anyhow, what I need to do is copy the records from tables in my catalog database and append them to the existing records in my archive database tables. This is a once-per-year, on-demand action, so shouldn't be terribly complicated, but I have spent all day on this.

I set up a new snapshot publication, which replicates, but it is overwriting the data that exists in the table already. I need for it not do do that, but append the new data.

Example:  EXISTING DATA

ID     NAME     CODE
1      abc         2010111
2      def          2010111
3      ghi          2010111

NEW DATA:
ID     NAME     CODE
1      abc         2011222
2      def          2011222
3      ghi          2011222

DESIRED END RESULT:

ID     NAME     CODE
1      abc         2010111
2      def          2010111
3      ghi          2010111
1      abc         2011222
2      def          2011222
3      ghi          2011222

I have removed the primary key designation from the ID field.  Can someone please help?
vcbertiniAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MangusDatabase AdministratorCommented:
Then you don't want replication.  Replication will always overwrite your data in your destination.

Why not just use a simple 'Insert Into destination_table Select * From source_table'
Kevin CrossChief Technology OfficerCommented:
I am not sure that is possible with replication; however, you can do this with a simple INSERT statement from one database to the next. You can have this in a yearly SQL Server Agent job. To prevent duplicate runs, you can check for Name, Code or ID, Code combination before inserting using NOT EXISTS.

INSERT INTO subdb.dbo.sub_tbl(ID, NAME, CODE)
SELECT ID, NAME, CODE
FROM pubdb.dbo.pub_tbl p
WHERE NOT EXISTS (
   SELECT 1
   FROM subdb.dbo.sub_tbl s
   WHERE s.ID = p.ID
   AND s.CODE = p.CODE
)
;

Open in new window


Hope that helps!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
@cmangus: sorry! I should have clicked refresh on browser. Was typing up example and did not see your suggestion. I agree 100%.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Chris MangusDatabase AdministratorCommented:
It happens...I've done it myself!
vcbertiniAuthor Commented:
I had the replication working before...somehow. It was not over-writing records because they were inherently different (code & id combination made it unique). I thought maybe it was a transaction replication, just run on demand.

How would I run an INSERT statement against every record in a database? Because basically I have to copy the entire contents of four tables from catalog into their corresponding table in archive. There's not millions of records or anything, but there's enough that i wouldn't want to run an insert statement 6,000 times...
Anthony PerkinsCommented:
INSERT YourArchiveDatabaseName.dbo.YourTableName (Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM    YourCatalogDatabaseName.dbo.YourTableName
WHERE  SomeOptionalConditionHere
Anthony PerkinsCommented:
Incidentally no points please, you had already had been given the solution.
vcbertiniAuthor Commented:
Hm. Not the answer I wanted, but it is what it is. I tried this with one table and it seemed to work. Thanks for the additional code-check for existing records, mwvisa1.  Thanks to you both for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.