synch Production and Dev tables (same server)

[sparkbyte]

I would like to synch the primary transaction table in Dev with the Prod table.

Currently if i have made changes in the Dev table I will truncate it afterwards and use a SSIS package to reimport the records from Prod. (with 6.5M rows, I like to watch the progress)

I also have a script which runs 3x per day to keep Dev up to Date with Prod.

What I would like to have is a script that overwrites any discrepencies in the Dev Table with the data in Prod.

Say I add 20+ rows of data and change/update 3 rows
in the Dev table testing the user UI, what i would like to have happen

The Added test rows are overwritten/updated with the data from the Production table (ie. prod.PK = dev.PK) Don't care if Dev has more rows that Prod. Prod rows will eventually overwrite them anyway. Then any rows that differ from Prod are updated to match.



This is the script I am currently using to 'sync' the tables but it will fail if the PK already exists.

DECLARE @TTL_Prod int
DECLARE @TTL_Dev  int
SET @TTL_Prod = 0
SET @TTL_Dev  = 0

Select @TTL_Prod = Count(*)
       From MailroomTracking.dbo.tblTrackingTable

Select @TTL_Dev  = Count(*)
       From MailroomTracking_DEV.dbo.tblTrackingTable

IF (@TTL_Prod - @TTL_Dev) > 0
   Begin
		/*******************************************************/
		/* Copy new rows from production to developement       */
		/*******************************************************/

		SET NOCOUNT ON
		SET IDENTITY_INSERT MailroomTracking_DEV.dbo.tblTrackingTable On

		Insert Into MailroomTracking_DEV.dbo.tblTrackingTable
		(
			Tracking_ID, 
			EmployeeID, 
			MachineName, 
			BoxNumber, 
			FileNumber, 
			TrackingDate
		)

		(
			Select 
					Tracking_ID, 
					EmployeeID, 
					MachineName, 
					BoxNumber, 
					FileNumber, 
					TrackingDate
			From MailroomTracking.dbo.tblTrackingTable
			Where Tracking_ID Not In (Select Tracking_ID from 
						MailroomTracking_DEV.dbo.tblTrackingTable)
		)

   End

Prod Table

CREATE TABLE [dbo].[tblTrackingTable](
	[Tracking_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[EmployeeID] [varchar](50) NULL,
	[MachineName] [varchar](20) NULL,
	[BoxNumber] [varchar](45) NOT NULL,
	[FileNumber] [varchar](25) NOT NULL,
	[TrackingDate] [datetime] NULL,
	[Reship] [bit] NULL,
	[BoxNumberOriginal] [varchar](50) NULL,
	[TrackingYear]  AS (datepart(year,[TrackingDate])),
	[TrackingMonth]  AS (datepart(month,[TrackingDate])),
 CONSTRAINT [PK_tblTrackingTable] PRIMARY KEY CLUSTERED 
(
	[Tracking_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Dev Table

CREATE TABLE [dbo].[tblTrackingTable](
	[Tracking_ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [varchar](50) NULL,
	[MachineName] [varchar](20) NULL,
	[BoxNumber] [varchar](45) NOT NULL,
	[FileNumber] [varchar](25) NOT NULL,
	[TrackingDate] [datetime] NULL,
	[Reship] [bit] NULL,
	[BoxNumberOriginal] [varchar](50) NULL,
	[RowGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_tblTrackingTable_RowGUID]  DEFAULT (newid()),
	[RowDeleted] [bit] NULL CONSTRAINT [DF_tblTrackingTable_RowDeleted]  DEFAULT ((0)),
	[ModifiedDate] [datetime] NULL,
	[ModifiedBy] [varchar](25) NULL,
	[TrackingYear]  AS (datepart(year,[TrackingDate])),
	[TrackingMonth]  AS (datepart(month,[TrackingDate])),
	[TrackingDay]  AS (datepart(day,[TrackingDate])),
 CONSTRAINT [PK_tblTrackingTable] PRIMARY KEY CLUSTERED 
(
	[Tracking_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Thanks

John Fuhrman

 

[sparkbyte]

What's up? Did I not provide enough info?

I would realy appreciated a nudge in the right direction.



Thanks

John Fuhrman

 

[gmmastros]

I very rarely suggest that people spend money on things, but in this case, it seems like a very big wheel to reinvent.

http://www.red-gate.com/products/sql-development/sql-data-compare/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You can also try
SQL Accessories Suit.

It has 30 days trial.


PluralSight Learning Library