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 2008 Forums
 Transact-SQL (2008)
 what is the best way to compare two tables

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-02-13 : 13:39:36
what is the best way to compare two tables and produce new created and updated and deleted rows data in a new table.

Can some one please kindly provide any ideas how to handle this task.

Thank you very much for the helpful info.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-02-13 : 14:02:11
show the DDL for the tables
Go to Top of Page

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-02-13 : 14:12:21
I am sorry here is the tables scripts and details, thank you very much for the helpful info.

First we load the data straight into this table:LAW_DEL_IMGNOW_GLACCT_FIRST

AND THEN DOES COMPARSION BETWEEN TEH fIRST TABLE AND CHANGE TABLE.

AND LOAD ALL CHANGED AND NEWLY INSERTED ROWS AND DELETED ROWS DATA TO THE dELTA TABLE THIRD ONE AT TEH BOTTOM.

CREATE TABLE [dbo].[LAW_DEL_IMGNOW_GLACCT_FIRST](
[TABLE_NAME] [varchar](50) NULL,
[ACTIVE] [varchar](1) NULL,
[BUSINESSUNITID] [int] NULL,
[COST_CENTER] [int] NULL,
[GLACCT] [int] NULL,
[GLSUBACCT] [int] NULL,
[GLACCTDESC] [varchar](250) NULL,
[GLACCT5] [varchar](250) NULL,
[GLACCT6] [varchar](250) NULL,
[STATUS_FLAG] [varchar](20) NULL
) ON [PRIMARY]

GO


SUR_KEY is the autonumber column increments by 1
here want to load all data with teh stataus_flag at end indicating D, N, U(deleted, Updated, New record), based on these flags and the status_date(system run time date) i will update teh third table for that days changed, newly created or deleted rows details.


CREATE TABLE [dbo].[LAW_DEL_IMGNOW_GLACCT_CHG](
[SUR_KEY] [int] NULL,
[TABLE_NAME] [varchar](7) NULL,
[ACTIVE] [varchar](1) NULL,
[BUSINESSUNITID] [int] NULL,
[COST_CENTER] [varchar](15) NULL,
[GLACCT] [varchar](40) NULL,
[GLSUBACCT] [int] NULL,
[GLACCTDESC] [varchar](60) NULL,
[GLACCT5] [varchar](15) NULL,
[GLACCT6] [varchar](5) NULL,
[STATUS_FLAG] [varchar](20) NULL,
[STATUS_DATE] [varchar](10) NULL
) ON [PRIMARY]

GO


---Here want to load just teh changed rows and new rows and deleted rows.

CREATE TABLE [dbo].[LAW_DEL_IMGNOW_GLACCT_DELTA](
[SUR_KEY] [int] NULL,
[TABLE_NAME] [varchar](7) NULL,
[ACTIVE] [varchar](1) NULL,
[BUSINESSUNITID] [int] NULL,
[COST_CENTER] [varchar](15) NULL,
[GLACCT] [varchar](40) NULL,
[GLSUBACCT] [int] NULL,
[GLACCTDESC] [varchar](60) NULL,
[GLACCT5] [varchar](15) NULL,
[GLACCT6] [varchar](5) NULL,
[STATUS_FLAG] [varchar](20) NULL,
[STATUS_DATE] [varchar](10) NULL
) ON [PRIMARY]
Go to Top of Page
   

- Advertisement -