Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Update Table from Another DB

Author  Topic 

Apollois
Starting Member

49 Posts
Posted - 2004-07-14 : 14:53:15
What is the best way to on demand update Table A in DB X from Table A in DB Y?

The tables are identical in structure, and in PKs. I would want Table A DB X to be updated based on the PK.

This task will need to be done from time-to-time at non-regular, periodic basis, on demand. Only this one table needs to be updated.

TIA.


Best Regards,
Jim

Kristen
Test

22859 Posts
Posted - 2004-07-14 : 15:10:18
Do you want to do a "Delta" update of DB_Y based on DB_X - i.e. all INSERTS and DELETES as necessary, and UPDATEs to only those rows that are different, so that tables in both DBs become identical??

Kristen
Go to Top of Page

Apollois
Starting Member

49 Posts
Posted - 2004-07-14 : 15:48:52
Kirsten,

That would be the optimum.

I was orignally thinking of only a simple update - Replace the data in all rows in Table_A DB_X with corresponding data (based on PK) from Table_A DB_Y.

But if your suggestion can be easily done -- then it would be a better solution.

So, the next question is -- how?

Thanks.


Best Regards,
Jim
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-07-14 : 20:31:26
I've got a tool here that can generate a script to do a DELTA update. If you post a CREATE TABLE script for your table I'll run it through the tool.

But if you just want to copy over the whole lot you should be able to do:

TRUNCATE DB_X.dbo.TABLE_A
INSERT INTO DB_X.dbo.TABLE_A
SELECT *
FROM DB_Y.dbo.TABLE_A

but if there are lots of rows it will take a little while and have a big impact on the log file.

Kristen
Go to Top of Page

Apollois
Starting Member

49 Posts
Posted - 2004-07-14 : 22:06:28
Kristen,

Thanks for the suggestion, but I don't want to delete the data in either DB. "TRUNCATE" deletes the data right?

What I want is for the tables to be syncronized. I suppose that setting up SQL Server replication is the only way to do this. I had hoped that there might be another approach since I only need to sync the tables on an occasionaly basis.


Best Regards,
Jim
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-07-14 : 22:16:56
My suggestion was to TRUNCATE (yes, DELETE) the target table then copy over all the rows; at that point the tables would be identical.

That's a Brute Force approach!, but will get the data across.

Perhaps I'm misunderstanding and you need to have something else? I was understanding you saying "What I want is for the tables to be syncronized." to mean that Table_A on DB_X gets an exact copy of the data in Table_A on DB_Y, but maybe there are some other issues to solve too?

If you post a CREATE TABLE statement I'll show you how the more efficient DELTA update would look.

Kristen
Go to Top of Page

PapillonUK
Starting Member

22 Posts
Posted - 2004-07-19 : 12:24:26
The DELTA update sound interesting - was this ever posted?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-07-19 : 13:27:53
If PapillonUK, or Apollois, want to post a CREATE TABLE I'll hapilly run it through our Delta Update generator.

Kristen
Go to Top of Page

Apollois
Starting Member

49 Posts
Posted - 2004-07-19 : 13:49:40
Kirsten,

OK, thanks. Here's my table:
=====================================
CREATE TABLE PhD_Activities (
Activity_PK int IDENTITY (1, 1) NOT NULL ,
Program_ID int NOT NULL ,
List_Order decimal(5, 1) NOT NULL CONSTRAINT DF_PhD_Activities_List_Order DEFAULT (1.0),
Activity_Group int NULL ,
Activity_Title varchar (80) NOT NULL ,
Min_Points int NOT NULL ,
Max_Points int NULL ,
Point_Units varchar (16) NULL ,
Point_Suffix varchar (8) NULL ,
Point_Value AS (convert(varchar(4),Min_Points) + isnull(('-' + convert(varchar(4),Max_Points)),'') + isnull((' ' + Point_Units),'')) ,
Max_Allowed int NOT NULL CONSTRAINT DF_PhD_Activities_Max_Allowed DEFAULT (1),
Activity_Desc varchar (2000) NOT NULL ,
Ref_Caption varchar (80) NULL ,
Criteria varchar (256) NULL ,
Activity_Freq varchar (24) NOT NULL ,
Supv_Only bit NOT NULL CONSTRAINT DF_PhD_Activities_Supv_Only DEFAULT (0),
Activity_Add varchar (8) NOT NULL ,
Act_Details_Page varchar (256) NOT NULL CONSTRAINT DF_PhD_Activities_Act_Details_Page DEFAULT ('PhD_SC_Details.asp'),
Last_Update smalldatetime NOT NULL CONSTRAINT DF_PhD_Activities_Act_Last_Update DEFAULT (getdate()),
Update_By_FK int NULL ,
Sys_Time_Stamp timestamp NULL ,
Activity_ID int NULL ,
CONSTRAINT PK_SC_Activities PRIMARY KEY CLUSTERED
(
Activity_PK
)
)
=====================================



Best Regards,
Jim
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-07-19 : 14:14:59
You need to globally change "MySourceDB" and "MyDestDB" to your database names.

I run the DELETES section manually (highlight and run) and see what
tables have rows to be deleted, then I go back any highlight the tables
that need some deletions - you'll see that there are a variety of bits
commented to enable easy testing of SELECT *, a SELECT that generates
a set of DELETE SQL statements - that can themselves be fine tuned - and
a straight "DELETE" to just kill them off.

The COMPUTED field will need commenting out on the INSERT/UPDATE (we only have two of thsoe, so I haven't automated that) and I have NO timestamp columns so I don't know how that will work.

Hope it makes sense!

Kristen

-- ========== DELETE PhD_Activities ========== --

PRINT '-- PhD_Activities'
IF EXISTS (SELECT * FROM MySourceDB.dbo.[PhD_Activities])
BEGIN
SELECT [Table] = 'PhD_Activities', [Delete rows]=COUNT(*)
-- SELECT [--PhD_Activities]='DELETE D FROM MyDestDB.dbo.[PhD_Activities] D WHERE D.[Activity_PK] = ' + CONVERT(varchar(20), D.[Activity_PK]), '--', *
-- SELECT *
-- DELETE D
FROM	MyDestDB.dbo.[PhD_Activities] D
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	MySourceDB.dbo.[PhD_Activities] S
	WHERE
		    D.[Activity_PK] = S.[Activity_PK]
	)

END
GO
-- __________ DELETE PhD_Activities __________ --


-- ========== INSERT/UPDATE PhD_Activities ========== --

SELECT '-- PhD_Activities', 'MySourceDB', COUNT(*) FROM MySourceDB.dbo.[PhD_Activities]
UNION SELECT '-- PhD_Activities', 'MyDestDB', COUNT(*) FROM MyDestDB.dbo.[PhD_Activities]
ORDER BY 1,2
GO

IF EXISTS (SELECT * FROM MySourceDB.dbo.[PhD_Activities])
BEGIN
UPDATE D
SET
--	[Activity_PK] = S.[Activity_PK],
	[Program_ID] = S.[Program_ID],
	[List_Order] = S.[List_Order],
	[Activity_Group] = S.[Activity_Group],
	[Activity_Title] = S.[Activity_Title],
	[Min_Points] = S.[Min_Points],
	[Max_Points] = S.[Max_Points],
	[Point_Units] = S.[Point_Units],
	[Point_Suffix] = S.[Point_Suffix],
	[Point_Value] = S.[Point_Value],
	[Max_Allowed] = S.[Max_Allowed],
	[Activity_Desc] = S.[Activity_Desc],
	[Ref_Caption] = S.[Ref_Caption],
	[Criteria] = S.[Criteria],
	[Activity_Freq] = S.[Activity_Freq],
	[Supv_Only] = S.[Supv_Only],
	[Activity_Add] = S.[Activity_Add],
	[Act_Details_Page] = S.[Act_Details_Page],
	[Last_Update] = S.[Last_Update],
	[Update_By_FK] = S.[Update_By_FK],
	[Sys_Time_Stamp] = S.[Sys_Time_Stamp],
	[Activity_ID] = S.[Activity_ID]
FROM	MyDestDB.dbo.[PhD_Activities] D
	JOIN MySourceDB.dbo.[PhD_Activities] S 
		 ON D.[Activity_PK] = S.[Activity_PK]

WHERE
		(
			-- Pessimistic WHERE (UPDATE)
			   (D.[Activity_PK] <> S.[Activity_PK] OR (D.[Activity_PK] IS NULL AND S.[Activity_PK] IS NOT NULL) OR (D.[Activity_PK] IS NOT NULL AND S.[Activity_PK] IS NULL))
			OR (D.[Program_ID] <> S.[Program_ID] OR (D.[Program_ID] IS NULL AND S.[Program_ID] IS NOT NULL) OR (D.[Program_ID] IS NOT NULL AND S.[Program_ID] IS NULL))
			OR (D.[List_Order] <> S.[List_Order] OR (D.[List_Order] IS NULL AND S.[List_Order] IS NOT NULL) OR (D.[List_Order] IS NOT NULL AND S.[List_Order] IS NULL))
			OR (D.[Activity_Group] <> S.[Activity_Group] OR (D.[Activity_Group] IS NULL AND S.[Activity_Group] IS NOT NULL) OR (D.[Activity_Group] IS NOT NULL AND S.[Activity_Group] IS NULL))
			OR (D.[Activity_Title] <> S.[Activity_Title] OR (D.[Activity_Title] IS NULL AND S.[Activity_Title] IS NOT NULL) OR (D.[Activity_Title] IS NOT NULL AND S.[Activity_Title] IS NULL))
			OR (D.[Min_Points] <> S.[Min_Points] OR (D.[Min_Points] IS NULL AND S.[Min_Points] IS NOT NULL) OR (D.[Min_Points] IS NOT NULL AND S.[Min_Points] IS NULL))
			OR (D.[Max_Points] <> S.[Max_Points] OR (D.[Max_Points] IS NULL AND S.[Max_Points] IS NOT NULL) OR (D.[Max_Points] IS NOT NULL AND S.[Max_Points] IS NULL))
			OR (D.[Point_Units] <> S.[Point_Units] OR (D.[Point_Units] IS NULL AND S.[Point_Units] IS NOT NULL) OR (D.[Point_Units] IS NOT NULL AND S.[Point_Units] IS NULL))
			OR (D.[Point_Suffix] <> S.[Point_Suffix] OR (D.[Point_Suffix] IS NULL AND S.[Point_Suffix] IS NOT NULL) OR (D.[Point_Suffix] IS NOT NULL AND S.[Point_Suffix] IS NULL))
			OR (D.[Point_Value] <> S.[Point_Value] OR (D.[Point_Value] IS NULL AND S.[Point_Value] IS NOT NULL) OR (D.[Point_Value] IS NOT NULL AND S.[Point_Value] IS NULL))
			OR (D.[Max_Allowed] <> S.[Max_Allowed] OR (D.[Max_Allowed] IS NULL AND S.[Max_Allowed] IS NOT NULL) OR (D.[Max_Allowed] IS NOT NULL AND S.[Max_Allowed] IS NULL))
			OR (D.[Activity_Desc] <> S.[Activity_Desc] OR (D.[Activity_Desc] IS NULL AND S.[Activity_Desc] IS NOT NULL) OR (D.[Activity_Desc] IS NOT NULL AND S.[Activity_Desc] IS NULL))
			OR (D.[Ref_Caption] <> S.[Ref_Caption] OR (D.[Ref_Caption] IS NULL AND S.[Ref_Caption] IS NOT NULL) OR (D.[Ref_Caption] IS NOT NULL AND S.[Ref_Caption] IS NULL))
			OR (D.[Criteria] <> S.[Criteria] OR (D.[Criteria] IS NULL AND S.[Criteria] IS NOT NULL) OR (D.[Criteria] IS NOT NULL AND S.[Criteria] IS NULL))
			OR (D.[Activity_Freq] <> S.[Activity_Freq] OR (D.[Activity_Freq] IS NULL AND S.[Activity_Freq] IS NOT NULL) OR (D.[Activity_Freq] IS NOT NULL AND S.[Activity_Freq] IS NULL))
			OR (D.[Supv_Only] <> S.[Supv_Only] OR (D.[Supv_Only] IS NULL AND S.[Supv_Only] IS NOT NULL) OR (D.[Supv_Only] IS NOT NULL AND S.[Supv_Only] IS NULL))
			OR (D.[Activity_Add] <> S.[Activity_Add] OR (D.[Activity_Add] IS NULL AND S.[Activity_Add] IS NOT NULL) OR (D.[Activity_Add] IS NOT NULL AND S.[Activity_Add] IS NULL))
			OR (D.[Act_Details_Page] <> S.[Act_Details_Page] OR (D.[Act_Details_Page] IS NULL AND S.[Act_Details_Page] IS NOT NULL) OR (D.[Act_Details_Page] IS NOT NULL AND S.[Act_Details_Page] IS NULL))
			OR (D.[Last_Update] <> S.[Last_Update] OR (D.[Last_Update] IS NULL AND S.[Last_Update] IS NOT NULL) OR (D.[Last_Update] IS NOT NULL AND S.[Last_Update] IS NULL))
			OR (D.[Update_By_FK] <> S.[Update_By_FK] OR (D.[Update_By_FK] IS NULL AND S.[Update_By_FK] IS NOT NULL) OR (D.[Update_By_FK] IS NOT NULL AND S.[Update_By_FK] IS NULL))
			OR (D.[Sys_Time_Stamp] <> S.[Sys_Time_Stamp] OR (D.[Sys_Time_Stamp] IS NULL AND S.[Sys_Time_Stamp] IS NOT NULL) OR (D.[Sys_Time_Stamp] IS NOT NULL AND S.[Sys_Time_Stamp] IS NULL))
			OR (D.[Activity_ID] <> S.[Activity_ID] OR (D.[Activity_ID] IS NULL AND S.[Activity_ID] IS NOT NULL) OR (D.[Activity_ID] IS NOT NULL AND S.[Activity_ID] IS NULL))
		)
END
GO

IF EXISTS (SELECT * FROM MySourceDB.dbo.[PhD_Activities])
BEGIN
SET IDENTITY_INSERT MyDestDB.dbo.[PhD_Activities] ON

INSERT INTO MyDestDB.dbo.[PhD_Activities]
(
	[Activity_PK],
	[Program_ID],
	[List_Order],
	[Activity_Group],
	[Activity_Title],
	[Min_Points],
	[Max_Points],
	[Point_Units],
	[Point_Suffix],
	[Point_Value],
	[Max_Allowed],
	[Activity_Desc],
	[Ref_Caption],
	[Criteria],
	[Activity_Freq],
	[Supv_Only],
	[Activity_Add],
	[Act_Details_Page],
	[Last_Update],
	[Update_By_FK],
	[Sys_Time_Stamp],
	[Activity_ID]
)
SELECT	*
FROM	MySourceDB.dbo.[PhD_Activities] S
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	MyDestDB.dbo.[PhD_Activities] D
	WHERE
		    D.[Activity_PK] = S.[Activity_PK]
	)

SET IDENTITY_INSERT MyDestDB.dbo.[PhD_Activities] OFF

END
GO
-- __________ INSERT/UPDATE PhD_Activities ___________ --
Go to Top of Page

PapillonUK
Starting Member

22 Posts
Posted - 2004-07-20 : 05:09:59
Thanks Kristen, i think my tables a bit simpler:


CREATE TABLE [dbo].[tblWorksOrder] (
	[JobRunID] [int] NOT NULL ,
	[WorksOrder] [char] (7) NOT NULL ,
	[UpdateDateTime] [datetime] NOT NULL ,
	[Line] [char] (4) NOT NULL ,
	[IS_MachType] [tinyint] NOT NULL ,
	[20DigitPartID] [int] NOT NULL ,
	[JobDesc] [varchar] (32) NOT NULL ,
	[Comment] [varchar] (32) NULL ,
	[SchedStartDate] [datetime] NOT NULL ,
	[SchedEndDate] [datetime] NOT NULL ,
	[SchedQty] [int] NOT NULL ,
	[WorksOrderStatusID] AS ([dbo].[GetWorksOrderStatusID]([WorksOrder], [dbo].[DateValue](getdate()))) 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblWorksOrder3] ADD 
	CONSTRAINT [PK_tblWorksOrder3] PRIMARY KEY  NONCLUSTERED 
	(
		[WorksOrder]
	)  ON [PRIMARY] 
GO



The WorksOrderStatusID is just a calculated SMALLINT that calls a function (i'd want to pass the value rather than the calculation if thats poss?).

Thanks for your help. Is this a program you've writen yourself?

R's
Sime
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-07-20 : 05:25:25
I am not at my desk at the moment, so I'll run it when I get back.

"Is this a program you've writen yourself?"

Yup

Kristen
Go to Top of Page

PapillonUK
Starting Member

22 Posts
Posted - 2004-07-20 : 05:33:16
Very clever!

That's "written", sorry
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-07-20 : 10:22:13
Same comments as before apply to CALCUALTED column ...

Kristen

-- ========== DELETE tblWorksOrder ========== --

PRINT '-- tblWorksOrder'
IF EXISTS (SELECT * FROM MySourceDB.dbo.[tblWorksOrder])
BEGIN
-- select [Table] = 'tblWorksOrder', [Delete rows]=COUNT(*)
SELECT [--tblWorksOrder]='DELETE D FROM MyDestDB.dbo.[tblWorksOrder] D WHERE D.[WorksOrder] = ' + '''' + REPLACE(D.[WorksOrder], '''', '''''')  + '''', '--', *
-- select *
-- DELETE D
FROM	MyDestDB.dbo.[tblWorksOrder] D
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	MySourceDB.dbo.[tblWorksOrder] S
	WHERE
		    D.[WorksOrder] = S.[WorksOrder]
	)

END
GO
-- __________ DELETE tblWorksOrder __________ --


-- ========== INSERT/UPDATE tblWorksOrder ========== --

SELECT '-- tblWorksOrder', 'MySourceDB', COUNT(*) FROM MySourceDB.dbo.[tblWorksOrder]
UNION SELECT '-- tblWorksOrder', 'MyDestDB', COUNT(*) FROM MyDestDB.dbo.[tblWorksOrder]
ORDER BY 1,2
GO

IF EXISTS (SELECT * FROM MySourceDB.dbo.[tblWorksOrder])
BEGIN
UPDATE D
SET
	[JobRunID] = S.[JobRunID],
--	[WorksOrder] = S.[WorksOrder],
	[UpdateDateTime] = S.[UpdateDateTime],
	[Line] = S.[Line],
	[IS_MachType] = S.[IS_MachType],
	[20DigitPartID] = S.[20DigitPartID],
	[JobDesc] = S.[JobDesc],
	[Comment] = S.[Comment],
	[SchedStartDate] = S.[SchedStartDate],
	[SchedEndDate] = S.[SchedEndDate],
	[SchedQty] = S.[SchedQty],
	[WorksOrderStatusID] = S.[WorksOrderStatusID]
FROM	MyDestDB.dbo.[tblWorksOrder] D
	JOIN MySourceDB.dbo.[tblWorksOrder] S
		 ON D.[WorksOrder] = S.[WorksOrder]

WHERE
	(
		-- Pessimistic WHERE (UPDATE)
		   (D.[JobRunID] <> S.[JobRunID] OR (D.[JobRunID] IS NULL AND S.[JobRunID] IS NOT NULL) OR (D.[JobRunID] IS NOT NULL AND S.[JobRunID] IS NULL))
		OR (D.[WorksOrder] <> S.[WorksOrder] OR (D.[WorksOrder] IS NULL AND S.[WorksOrder] IS NOT NULL) OR (D.[WorksOrder] IS NOT NULL AND S.[WorksOrder] IS NULL))
		OR (D.[UpdateDateTime] <> S.[UpdateDateTime] OR (D.[UpdateDateTime] IS NULL AND S.[UpdateDateTime] IS NOT NULL) OR (D.[UpdateDateTime] IS NOT NULL AND S.[UpdateDateTime] IS NULL))
		OR (D.[Line] <> S.[Line] OR (D.[Line] IS NULL AND S.[Line] IS NOT NULL) OR (D.[Line] IS NOT NULL AND S.[Line] IS NULL))
		OR (D.[IS_MachType] <> S.[IS_MachType] OR (D.[IS_MachType] IS NULL AND S.[IS_MachType] IS NOT NULL) OR (D.[IS_MachType] IS NOT NULL AND S.[IS_MachType] IS NULL))
		OR (D.[20DigitPartID] <> S.[20DigitPartID] OR (D.[20DigitPartID] IS NULL AND S.[20DigitPartID] IS NOT NULL) OR (D.[20DigitPartID] IS NOT NULL AND S.[20DigitPartID] IS NULL))
		OR (D.[JobDesc] <> S.[JobDesc] OR (D.[JobDesc] IS NULL AND S.[JobDesc] IS NOT NULL) OR (D.[JobDesc] IS NOT NULL AND S.[JobDesc] IS NULL))
		OR (D.[Comment] <> S.[Comment] OR (D.[Comment] IS NULL AND S.[Comment] IS NOT NULL) OR (D.[Comment] IS NOT NULL AND S.[Comment] IS NULL))
		OR (D.[SchedStartDate] <> S.[SchedStartDate] OR (D.[SchedStartDate] IS NULL AND S.[SchedStartDate] IS NOT NULL) OR (D.[SchedStartDate] IS NOT NULL AND S.[SchedStartDate] IS NULL))
		OR (D.[SchedEndDate] <> S.[SchedEndDate] OR (D.[SchedEndDate] IS NULL AND S.[SchedEndDate] IS NOT NULL) OR (D.[SchedEndDate] IS NOT NULL AND S.[SchedEndDate] IS NULL))
		OR (D.[SchedQty] <> S.[SchedQty] OR (D.[SchedQty] IS NULL AND S.[SchedQty] IS NOT NULL) OR (D.[SchedQty] IS NOT NULL AND S.[SchedQty] IS NULL))
		OR (D.[WorksOrderStatusID] <> S.[WorksOrderStatusID] OR (D.[WorksOrderStatusID] IS NULL AND S.[WorksOrderStatusID] IS NOT NULL) OR (D.[WorksOrderStatusID] IS NOT NULL AND S.[WorksOrderStatusID] IS NULL))
	)
END
GO

IF EXISTS (SELECT * FROM MySourceDB.dbo.[tblWorksOrder])
BEGIN
INSERT INTO MyDestDB.dbo.[tblWorksOrder]
(
	[JobRunID],
	[WorksOrder],
	[UpdateDateTime],
	[Line],
	[IS_MachType],
	[20DigitPartID],
	[JobDesc],
	[Comment],
	[SchedStartDate],
	[SchedEndDate],
	[SchedQty],
	[WorksOrderStatusID]
)
SELECT	*
FROM	MySourceDB.dbo.[tblWorksOrder] S
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	MyDestDB.dbo.[tblWorksOrder] D
	WHERE
		    D.[WorksOrder] = S.[WorksOrder]
	)
END
GO
-- __________ INSERT/UPDATE tblWorksOrder ___________ --
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-07-20 : 10:26:54
quote:
Originally posted by PapillonUK

Very clever!

Well, of course I don;t see it like that ... once you've got a template sorted out its just a question of querying the Table/Column names and chruning out a "canned" DELETE/UPDATE/INSERT set.

Actually, in case of interest, my little program also churns out a version that creates a set if SQL statements to make the changes. Run this in Query Anayser with "Results in Text" set.

Kristen

-- ========== DELETE tblWorksOrder ========== --

PRINT '-- tblWorksOrder'
IF EXISTS (SELECT * FROM MySourceDB.dbo.[tblWorksOrder])
BEGIN
-- select [Table] = 'tblWorksOrder', [Delete rows]=COUNT(*)
SELECT [--tblWorksOrder]='DELETE D FROM MyDestDB.dbo.[tblWorksOrder] D WHERE D.[WorksOrder] = ' + '''' + REPLACE(D.[WorksOrder], '''', '''''')  + '''', '--', *
-- select *
-- DELETE D
FROM	MyDestDB.dbo.[tblWorksOrder] D
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	MySourceDB.dbo.[tblWorksOrder] S
	WHERE
		    D.[WorksOrder] = S.[WorksOrder]
	)

END
GO
-- __________ DELETE tblWorksOrder __________ --


-- SQL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

-- ========== INSERT/UPDATE tblWorksOrder ========== --

select '-- tblWorksOrder', 'MySourceDB', COUNT(*) FROM MySourceDB.dbo.[tblWorksOrder]
UNION SELECT '-- tblWorksOrder', 'MyDestDB', COUNT(*) FROM MyDestDB.dbo.[tblWorksOrder]
ORDER BY 1,2
GO

IF EXISTS (SELECT * FROM MySourceDB.dbo.[tblWorksOrder])
BEGIN
SELECT
'UPDATE D
SET
	[JobRunID] =	', CONVERT(varchar(20), S.[JobRunID]), ',	-- ', D.[JobRunID], '
	[WorksOrder] =	', '''' + REPLACE(S.[WorksOrder], '''', '''''')  + '''', ',	-- ', D.[WorksOrder], '
	[UpdateDateTime] =	', '''' + CONVERT(varchar(24), S.[UpdateDateTime], 113) + '''', ',	-- ', D.[UpdateDateTime], '
	[Line] =	', '''' + REPLACE(S.[Line], '''', '''''')  + '''', ',	-- ', D.[Line], '
	[IS_MachType] =	', CONVERT(varchar(20), S.[IS_MachType]), ',	-- ', D.[IS_MachType], '
	[20DigitPartID] =	', CONVERT(varchar(20), S.[20DigitPartID]), ',	-- ', D.[20DigitPartID], '
	[JobDesc] =	', '''' + REPLACE(S.[JobDesc], '''', '''''')  + '''', ',	-- ', D.[JobDesc], '
	[Comment] =	', '''' + REPLACE(S.[Comment], '''', '''''')  + '''', ',	-- ', D.[Comment], '
	[SchedStartDate] =	', '''' + CONVERT(varchar(24), S.[SchedStartDate], 113) + '''', ',	-- ', D.[SchedStartDate], '
	[SchedEndDate] =	', '''' + CONVERT(varchar(24), S.[SchedEndDate], 113) + '''', ',	-- ', D.[SchedEndDate], '
	[SchedQty] =	', CONVERT(varchar(20), S.[SchedQty]), ',	-- ', D.[SchedQty], '
	[WorksOrderStatusID] =	', CONVERT(varchar(20), S.[WorksOrderStatusID]), ',	-- ', D.[WorksOrderStatusID], '
FROM	MyDestDB.dbo.[tblWorksOrder] D
WHERE
		    D.[WorksOrder] = ' + '''' + REPLACE(S.[WorksOrder], '''', '''''')  + '''','
'+CHAR(13)+CHAR(10)+'GO
----------'
-- SELECT TOP 100 'D', D.*, CHAR(13)+CHAR(10)+'S   ', S.*, CHAR(13)+CHAR(10)+'----------'
FROM	MyDestDB.dbo.[tblWorksOrder] D
	join MySourceDB.dbo.[tblWorksOrder] S on
		    D.[WorksOrder] = S.[WorksOrder]

WHERE
	(
		-- Pessimistic WHERE (UPDATE)
		   (D.[JobRunID] <> S.[JobRunID] OR (D.[JobRunID] IS NULL AND S.[JobRunID] IS NOT NULL) OR (D.[JobRunID] IS NOT NULL AND S.[JobRunID] IS NULL))
		OR (D.[WorksOrder] <> S.[WorksOrder] OR (D.[WorksOrder] IS NULL AND S.[WorksOrder] IS NOT NULL) OR (D.[WorksOrder] IS NOT NULL AND S.[WorksOrder] IS NULL))
		OR (D.[UpdateDateTime] <> S.[UpdateDateTime] OR (D.[UpdateDateTime] IS NULL AND S.[UpdateDateTime] IS NOT NULL) OR (D.[UpdateDateTime] IS NOT NULL AND S.[UpdateDateTime] IS NULL))
		OR (D.[Line] <> S.[Line] OR (D.[Line] IS NULL AND S.[Line] IS NOT NULL) OR (D.[Line] IS NOT NULL AND S.[Line] IS NULL))
		OR (D.[IS_MachType] <> S.[IS_MachType] OR (D.[IS_MachType] IS NULL AND S.[IS_MachType] IS NOT NULL) OR (D.[IS_MachType] IS NOT NULL AND S.[IS_MachType] IS NULL))
		OR (D.[20DigitPartID] <> S.[20DigitPartID] OR (D.[20DigitPartID] IS NULL AND S.[20DigitPartID] IS NOT NULL) OR (D.[20DigitPartID] IS NOT NULL AND S.[20DigitPartID] IS NULL))
		OR (D.[JobDesc] <> S.[JobDesc] OR (D.[JobDesc] IS NULL AND S.[JobDesc] IS NOT NULL) OR (D.[JobDesc] IS NOT NULL AND S.[JobDesc] IS NULL))
		OR (D.[Comment] <> S.[Comment] OR (D.[Comment] IS NULL AND S.[Comment] IS NOT NULL) OR (D.[Comment] IS NOT NULL AND S.[Comment] IS NULL))
		OR (D.[SchedStartDate] <> S.[SchedStartDate] OR (D.[SchedStartDate] IS NULL AND S.[SchedStartDate] IS NOT NULL) OR (D.[SchedStartDate] IS NOT NULL AND S.[SchedStartDate] IS NULL))
		OR (D.[SchedEndDate] <> S.[SchedEndDate] OR (D.[SchedEndDate] IS NULL AND S.[SchedEndDate] IS NOT NULL) OR (D.[SchedEndDate] IS NOT NULL AND S.[SchedEndDate] IS NULL))
		OR (D.[SchedQty] <> S.[SchedQty] OR (D.[SchedQty] IS NULL AND S.[SchedQty] IS NOT NULL) OR (D.[SchedQty] IS NOT NULL AND S.[SchedQty] IS NULL))
		OR (D.[WorksOrderStatusID] <> S.[WorksOrderStatusID] OR (D.[WorksOrderStatusID] IS NULL AND S.[WorksOrderStatusID] IS NOT NULL) OR (D.[WorksOrderStatusID] IS NOT NULL AND S.[WorksOrderStatusID] IS NULL))
	)
END
GO

IF EXISTS (SELECT * FROM MySourceDB.dbo.[tblWorksOrder])
BEGIN
SELECT '
INSERT INTO MyDestDB.dbo.[tblWorksOrder]
(
	[JobRunID],','
	[WorksOrder],','
	[UpdateDateTime],','
	[Line],','
	[IS_MachType],','
	[20DigitPartID],','
	[JobDesc],','
	[Comment],','
	[SchedStartDate],','
	[SchedEndDate],','
	[SchedQty],','
	[WorksOrderStatusID]','
)
SELECT	
	[JobRunID] =	',CONVERT(varchar(20), S.[JobRunID]),',
	[WorksOrder] =	','''' + REPLACE(S.[WorksOrder], '''', '''''')  + '''',',
	[UpdateDateTime] =	','''' + CONVERT(varchar(24), S.[UpdateDateTime], 113) + '''',',
	[Line] =	','''' + REPLACE(S.[Line], '''', '''''')  + '''',',
	[IS_MachType] =	',CONVERT(varchar(20), S.[IS_MachType]),',
	[20DigitPartID] =	',CONVERT(varchar(20), S.[20DigitPartID]),',
	[JobDesc] =	','''' + REPLACE(S.[JobDesc], '''', '''''')  + '''',',
	[Comment] =	','''' + REPLACE(S.[Comment], '''', '''''')  + '''',',
	[SchedStartDate] =	','''' + CONVERT(varchar(24), S.[SchedStartDate], 113) + '''',',
	[SchedEndDate] =	','''' + CONVERT(varchar(24), S.[SchedEndDate], 113) + '''',',
	[SchedQty] =	',CONVERT(varchar(20), S.[SchedQty]),',
	[WorksOrderStatusID] =	',CONVERT(varchar(20), S.[WorksOrderStatusID]),'
'+CHAR(13)+CHAR(10)+'GO
----------'
FROM	MySourceDB.dbo.[tblWorksOrder] S
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	MyDestDB.dbo.[tblWorksOrder] D
	WHERE
		    D.[WorksOrder] = S.[WorksOrder]
	)
END
GO
-- __________ INSERT/UPDATE tblWorksOrder ___________ --
Go to Top of Page

PapillonUK
Starting Member

22 Posts
Posted - 2004-07-20 : 13:28:44
Crikey! Just about to go home so i'll have a look at this in QA and see how it works in more detail tomorrow.

Thanks, Sime
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site