SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 what is the best way to compare two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

541 Posts

Posted - 02/13/2013 :  13:39:36  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 02/13/2013 :  14:02:11  Show Profile  Visit russell's Homepage  Reply with Quote
show the DDL for the tables
Go to Top of Page

cplusplus
Aged Yak Warrior

541 Posts

Posted - 02/13/2013 :  14:12:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000