Except function to find specific column

• coffeejs

  SSC Veteran

  Points: 262

  More actions

  November 28, 2009 at 3:44 pm

  #96122

  Hi everyone,

  I'm trying to compare two tables and figure out which column for a specific record changed.

  Here's an example:

  Let's say farmer Joe uses a database to inventory all his cows.

  table cow2008 has the following data

  cow_id.....cow_name.....cow_color

  123..........betsy...........brown

  124..........clara............orange

  in 2009 Joe decides to upgrade betsy and now calls her daisy. He puts the new data

  into a table called cow2009 like so:

  cow_id.....cow_name.....cow_color

  123.........daisy............brown

  124.........clara............orange

  Using the "except" function as explained on Jeff's SQL Server Blog, Joe can find which record changed, but would it be possible to figure out which column for a specific record changed?

  http://weblogs.sqlteam.com/jeffs/archive/2007/05/02/60194.aspx

  Thanks in advance,

  James

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  December 2, 2009 at 5:12 am

  #1086375

  coffeejs (11/28/2009)

  ---

  Hi everyone,

  I'm trying to compare two tables and figure out which column for a specific record changed.

  Here's an example:

  Let's say farmer Joe uses a database to inventory all his cows.

  table cow2008 has the following data

  cow_id.....cow_name.....cow_color

  123..........betsy...........brown

  124..........clara............orange

  in 2009 Joe decides to upgrade betsy and now calls her daisy. He puts the new data

  into a table called cow2009 like so:

  cow_id.....cow_name.....cow_color

  123.........daisy............brown

  124.........clara............orange

  Using the "except" function as explained on Jeff's SQL Server Blog, Joe can find which record changed, but would it be possible to figure out which column for a specific record changed?

  http://weblogs.sqlteam.com/jeffs/archive/2007/05/02/60194.aspx

  Thanks in advance,

  James

  It Depends

  it depends upon which filed you are trying to except/intersect

  DECLARE @TEMP TABLE (RID INT IDENTITY,cow_name VARCHAR(15),cow_color VARCHAR(15))

  DECLARE @TEMP1 TABLE (RID INT IDENTITY,cow_name VARCHAR(15),cow_color VARCHAR(15))

  INSERT INTO @TEMP Select 'betsy','brown'

  INSERT INTO @TEMP Select 'clara','orange'

  INSERT INTO @TEMP1 Select 'daisy','brown'

  INSERT INTO @TEMP1 Select 'clara','orange'

  select cow_name from @Temp except select cow_name from @Temp1

  select cow_name from @Temp intersect select cow_name from @Temp1

  Here It Changes for Column cow_name

  Thanks
  Parthi

• coffeejs

  SSC Veteran

  Points: 262

  More actions

  December 2, 2009 at 4:11 pm

  #1086818

  Thank you Parthi, much appreciated.

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  December 3, 2009 at 12:36 am

  #1086926

  another simple way

  Select * from

  cow2008 c inner join cow2009 d

  on c.cow_id = d.cow_id

  WHERE c.cow_name <> d.cow_name.

  You could also create a dynamic sql in which the where clause column name (cow_name in the above code) can be changed.

  "Keep Trying"