| Author |
 Topic  |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
 Posted - 01/15/2003 : 21:46:17
|
Based on the discussion at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23038
and BSmith's idea (no relation), I came up with this silly stored proc to compare any two tables or views. Supply the table names and the columns you wish to compare and have fun!
The key to the GROUP BY approach is that it handles NULLS quite well which JOINS have trouble with.
CREATE PROCEDURE CompareTables(@table1 varchar(100),
@table2 Varchar(100), @T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS
-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.
declare @SQL varchar(8000);
IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList
set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2
set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'
exec ( @SQL)
- Jeff |
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 08/07/2003 : 21:37:37
|
Great Stuff jeff, exactly what I was looking for.
Thanks,
Kevin |
 |
|
|
parikhm3
Starting Member
12 Posts |
Posted - 07/23/2007 : 16:49:02
|
how do i write a sync procedure that syncs both tables and ensures that Table_2 keeps all services that are listed in Table_1
how do i write a procedure for the sync between both tables, and put this procedure into the package
so basically there have to be two sync parts: First is INSERT of what is not yet existing, second one is UPDATE on the appropriate attributes.
How would i write this sync procedure? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/23/2007 : 16:52:14
|
There is a tool from RedGate that does this for you.
Peter Larsson
Helsingborg, Sweden |
|
|
|
parikhm3
Starting Member
12 Posts |
Posted - 07/23/2007 : 17:05:13
|
BUT I HAVE TO WRITE A SCRIPT THAT DOES THE ABOVE THINGS
SECONDLY WHAT DO I WRITE FOR TABLENAMES
|
|
|
|
parikhm3
Starting Member
12 Posts |
Posted - 07/23/2007 : 17:10:48
|
CREATE PROCEDURE CompareTables(@HPSTYPE varchar2(100), @WWGD_SERVICENOTES Varchar2(100), @NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT varchar2(1000), @SERVICE_NAME, REBOOT_FLAG, BITS_AVAIL, RTP_LEVEL, ETF_FLAG, DATE_VENDORRELEASE, DATE_ACQUISITION, DATE_TEST, DATE_PRODUCTION, INDIVIDUAL_PRODUCTION, DATE_OBSOLETE, INDIVIDUAL_OBSOLETE, SVC_COMMENT, SVC_CATGROUP, SVC_DESCRIPT, TITLE varchar2(1000) = '')
AS
declare @SQL varchar2(8000);
IF @WWGD_SERVICENOTES = '' SET @WWGD_SERVICENOTES = @HPSTYPE
set @SQL = 'SELECT ''' + @HPSTYPE + ''' AS TableName, ' + @NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT varchar2(1000) + ' FROM ' + @HPSTYPE + ' UNION ALL SELECT ''' + @WWGD_SERVICENOTES + ''' As TableName, ' + @SERVICE_NAME, REBOOT_FLAG, BITS_AVAIL, RTP_LEVEL, ETF_FLAG, DATE_VENDORRELEASE, DATE_ACQUISITION, DATE_TEST, DATE_PRODUCTION, INDIVIDUAL_PRODUCTION, DATE_OBSOLETE, INDIVIDUAL_OBSOLETE, SVC_COMMENT, SVC_CATGROUP, SVC_DESCRIPT, TITLE varchar2(1000) + ' FROM ' + @WWGD_SERVICENOTES
set @SQL = 'SELECT Max(TableName) as TableName, ' + @NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT varchar2(1000) + ' FROM (' + @SQL + ') A GROUP BY ' + @NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT varchar2(1000) + ' HAVING COUNT(*) = 1'
exec ( @SQL)
IN THE ABOVE WRITTEN SCRIPT WHAT WOULD BE MY SELECT STATEMENTS AND MY TABLENAMES
PLEASE ADVICE |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/24/2007 : 03:33:59
|
"First is INSERT of what is not yet existing, second one is UPDATE on the appropriate attributes"
I would say First is to delete any rows no longer existing in Master table.
The deletes have to be done in "reverese FK order" and the inserts in "forward FK order". Even so the need for Updates may break the FKs - e.g. you want to delete a row referenced in an FK which WILL be fixed by the Update but that hasn't run yet.
We use a script to generate:
DELETE D
FROM DestDatabase.dbo.[MyTable] AS D
WHERE NOT EXISTS
(
SELECT *
FROM SourceDatabase.dbo.[MyTable] AS S
WHERE
D.[MyPK1] = S.[MyPK1]
AND D.[MyPK2] = S.[MyPK2]
)
...
IF EXISTS (SELECT * FROM SourceDatabase.dbo.[MyTable])
BEGIN
UPDATE D
SET
[MyCol1] = S.[MyCol1],
[MyCol2] = S.[MyCol2],
...
FROM DestDatabase.dbo.[MyTable] AS D
JOIN SourceDatabase.dbo.[MyTable] AS S
ON D.[MyPK1] = S.[MyPK1]
AND D.[MyPK2] = S.[MyPK2]
WHERE
(
(D.[MyCol1] <> S.[MyCol1]
OR (D.[MyCol1] IS NULL AND S.[MyCol1] IS NOT NULL)
OR (D.[MyCol1] IS NOT NULL AND S.[MyCol1] IS NULL))
OR (D.[MyVarcharCol] COLLATE Latin1_General_BIN <> S.[MyVarcharCol]
OR (D.[MyVarcharCol] IS NULL AND S.[MyVarcharCol] IS NOT NULL)
OR (D.[MyVarcharCol] IS NOT NULL AND S.[MyVarcharCol] IS NULL))
...
)
END
GO
IF EXISTS (SELECT * FROM SourceDatabase.dbo.[MyTable])
BEGIN
INSERT INTO DestDatabase.dbo.[MyTable]
(
[MyCol1],
[MyCol2],
...
)
SELECT S.*
FROM SourceDatabase.dbo.[MyTable] AS S
WHERE NOT EXISTS
(
SELECT *
FROM DestDatabase.dbo.[MyTable] AS D
WHERE
D.[MyPK1] = S.[MyPK1]
AND D.[MyPK2] = S.[MyPK2]
)
END
Kristen |
Edited by - Kristen on 07/24/2007 03:34:53 |
|
|
|
parikhm3
Starting Member
12 Posts |
Posted - 07/24/2007 : 09:56:20
|
can some one tell me a store procedure script to update contents of one table from contents of another.
Help would be appreciated.
Thank you. |
|
|
|
parikhm3
Starting Member
12 Posts |
Posted - 07/24/2007 : 10:31:57
|
I AM NOT A ORACLE GUY BUT NEED TO RUN THIS AS I RUN MY OWN BUSINESS. SO PLEASE HELP OUT.
CREATE PROCEDURE [dbo].[testINS]
(
@AValue int
)
AS
DECLARE @TType nvarchar(3)
SET @TType = 'INS'
BEGIN
INSERT INTO [dbo].[OriginalTable]([aValue]) VALUES (@AValue)
INSERT INTO [dbo].[TransactionsTable]([TType], [aValue]) VALUES (@TTYPE, @AValue)
END
GO
CREATE PROCEDURE [dbo].[testUPD]
(
@oldAValue int
@newAValue int
)
AS
DECLARE @TType nvarchar(3)
SET @TType = 'UPD'
BEGIN
UPDATE [dbo].[OriginalTable] SET [aValue] = @newAValue WHERE [aValue] = @oldAValue
INSERT INTO [dbo].[TransactionsTable]([TType], [aValue], [newAValue]) VALUES (@TTYPE, @oldAValue, @newAValue)
END
GO
-----------------------------------------------------------
i got the above code but the problem is I don't know what to enter where
if someone could help me.
The tables i need to feed are below.
basically table 2 columns have to compare and update from columns in table 1.
can you please help me write a procedure using the info below.
Table 1: MPTYPE
COLUMNS IN TABLE 1:
NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT
TABLE 2: WWW_SVNOTES
COLUMNS IN TABLE 2:
SERVICE_NAME, REBOOT_FLAG, BITS_AVAIL, RTP_LEVEL, ETF_FLAG, DATE_VENDORRELEASE, DATE_ACQUISITION, DATE_TEST, DATE_PRODUCTION, INDIVIDUAL_PRODUCTION, DATE_OBSOLETE, INDIVIDUAL_OBSOLETE, SVC_COMMENT, SVC_CATGROUP, SVC_DESCRIPT, TITLE |
Edited by - parikhm3 on 07/24/2007 10:40:03 |
|
|
|
parikhm3
Starting Member
12 Posts |
Posted - 07/25/2007 : 12:11:25
|
I figured it out but if any one want to know how here you go:
PROCEDURE p_sync_sn
AS
BEGIN
MERGE INTO WWGD_SERVICENOTES B
USING (
SELECT SERVICE_ID, CATGROUP, DESCRIPT, REBOOT
FROM HPSTYPE
WHERE CATGROUP is NOT NULL) E
ON (E.SERVICE_ID = B.SERVICE_NAME)
WHEN MATCHED THEN
UPDATE
SET B.SVC_CATGROUP = E.CATGROUP,
B.SVC_DESCRIPT=E.DESCRIPT,
B.REBOOT_FLAG = E.REBOOT
WHEN NOT MATCHED THEN
INSERT (SERVICE_NAME, REBOOT_FLAG, SVC_CATGROUP, SVC_DESCRIPT)
VALUES (E.SERVICE_ID, E.REBOOT, E.CATGROUP, E.DESCRIPT);
commit;
exception
when others then
rollback;
raise;
end p_sync_sn; |
|
|
|
parikhm3
Starting Member
12 Posts |
Posted - 07/26/2007 : 11:18:33
|
| figured it out |
Edited by - parikhm3 on 07/26/2007 13:58:20 |
|
|
|
motormal
Starting Member
Canada
1 Posts |
|
| |
Topic |
|
|
|
Comparing two tables or views easily
