[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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?
0
vcbertini
Asked:
vcbertini
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
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'
0
 
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!
0
 
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%.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Chris MangusDatabase AdministratorCommented:
It happens...I've done it myself!
0
 
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...
0
 
Anthony PerkinsCommented:
INSERT YourArchiveDatabaseName.dbo.YourTableName (Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM    YourCatalogDatabaseName.dbo.YourTableName
WHERE  SomeOptionalConditionHere
0
 
Anthony PerkinsCommented:
Incidentally no points please, you had already had been given the solution.
0
 
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.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now