Compare of variables and handling nulls

• allan.ford17

  SSC Eights!

  Points: 944

  More actions

  October 14, 2018 at 9:18 pm

  #381086

  Hi,

  I wanted to compare 3 variables and set a flag if any of the three differ .. i.e. including handling of null values

  I came up with this which works fine but is not very elegant.   Is there a simple way to do this ?

          if (@SRC_NAME_VAL1 != @DEST_NAME_VAL1) set @PerformUpdate = 1    
           if @SRC_NAME_VAL1 is null and not (@DEST_NAME_VAL1 is null) set @PerformUpdate = 1
           if @DEST_NAME_VAL1 is null and not (@SRC_NAME_VAL1 is null) set @PerformUpdate = 1
          
           if (@SRC_DATE_VAL1 != @DEST_DATE_VAL1) set @PerformUpdate = 1
           if @SRC_DATE_VAL1 is null and not (@DEST_DATE_VAL1 is null) set @PerformUpdate = 1
           if @DEST_DATE_VAL1 is null and not (@SRC_DATE_VAL1 is null) set @PerformUpdate = 1
          
           if (@SRC_NUMBER_VAL1 != @DEST_NUMBER_VAL1) set @PerformUpdate = 1
           if @SRC_NUMBER_VAL1 is null and not (@DEST_NUMBER_VAL1 is null) set @PerformUpdate = 1
           if @DEST_NUMBER_VAL1 is null and not (@SRC_NUMBER_VAL1 is null) set @PerformUpdate = 1

• Jeff Moden

  SSC Guru

  Points: 1003865

  More actions

  October 14, 2018 at 10:53 pm

  #2009533

  allan.ford17 - Sunday, October 14, 2018 9:18 PM

  Hi,

  I wanted to compare 3 variables and set a flag if any of the three differ .. i.e. including handling of null values

  I came up with this which works fine but is not very elegant.   Is there a simple way to do this ?

          if (@SRC_NAME_VAL1 != @DEST_NAME_VAL1) set @PerformUpdate = 1    
           if @SRC_NAME_VAL1 is null and not (@DEST_NAME_VAL1 is null) set @PerformUpdate = 1
           if @DEST_NAME_VAL1 is null and not (@SRC_NAME_VAL1 is null) set @PerformUpdate = 1
          
           if (@SRC_DATE_VAL1 != @DEST_DATE_VAL1) set @PerformUpdate = 1
           if @SRC_DATE_VAL1 is null and not (@DEST_DATE_VAL1 is null) set @PerformUpdate = 1
           if @DEST_DATE_VAL1 is null and not (@SRC_DATE_VAL1 is null) set @PerformUpdate = 1
          
           if (@SRC_NUMBER_VAL1 != @DEST_NUMBER_VAL1) set @PerformUpdate = 1
           if @SRC_NUMBER_VAL1 is null and not (@DEST_NUMBER_VAL1 is null) set @PerformUpdate = 1
           if @DEST_NUMBER_VAL1 is null and not (@SRC_NUMBER_VAL1 is null) set @PerformUpdate = 1

  It would seem that you're working one row of some table at a time.  Is the end game to update more than one row of some table in the broader scope?  If so, it would be helpful to see the rest of the query so that we can show you how to do this without RBAR.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Phil Parkin

  SSC Guru

  Points: 246695

  More actions

  October 15, 2018 at 5:20 am

  #2009566

  allan.ford17 - Sunday, October 14, 2018 9:18 PM

  Hi,

  I wanted to compare 3 variables and set a flag if any of the three differ .. i.e. including handling of null values

  I came up with this which works fine but is not very elegant.   Is there a simple way to do this ?

          if (@SRC_NAME_VAL1 != @DEST_NAME_VAL1) set @PerformUpdate = 1    
           if @SRC_NAME_VAL1 is null and not (@DEST_NAME_VAL1 is null) set @PerformUpdate = 1
           if @DEST_NAME_VAL1 is null and not (@SRC_NAME_VAL1 is null) set @PerformUpdate = 1
          
           if (@SRC_DATE_VAL1 != @DEST_DATE_VAL1) set @PerformUpdate = 1
           if @SRC_DATE_VAL1 is null and not (@DEST_DATE_VAL1 is null) set @PerformUpdate = 1
           if @DEST_DATE_VAL1 is null and not (@SRC_DATE_VAL1 is null) set @PerformUpdate = 1
          
           if (@SRC_NUMBER_VAL1 != @DEST_NUMBER_VAL1) set @PerformUpdate = 1
           if @SRC_NUMBER_VAL1 is null and not (@DEST_NUMBER_VAL1 is null) set @PerformUpdate = 1
           if @DEST_NUMBER_VAL1 is null and not (@SRC_NUMBER_VAL1 is null) set @PerformUpdate = 1

  Please take the time to answer Jeff's question – there may be ways to significantly improve the way you are currently doing things. Having said that, I believe that the following code is a shorter version of what you currently have:

  IF NOT EXISTS
(
  SELECT
   @SRC_NAME_VAL1
  , @SRC_DATE_VAL1
  , @SRC_NUMBER_VAL1
  INTERSECT
  SELECT
   @DEST_NAME_VAL1
  , @DEST_DATE_VAL1
  , @DEST_NUMBER_VAL1
)
  SET @PerformUpdate = 1;

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Jeff Moden

  SSC Guru

  Points: 1003865

  More actions

  October 15, 2018 at 7:59 am

  #2009616

  Phil Parkin - Monday, October 15, 2018 5:20 AM

  allan.ford17 - Sunday, October 14, 2018 9:18 PM

  Hi,

  I wanted to compare 3 variables and set a flag if any of the three differ .. i.e. including handling of null values

  I came up with this which works fine but is not very elegant.   Is there a simple way to do this ?

          if (@SRC_NAME_VAL1 != @DEST_NAME_VAL1) set @PerformUpdate = 1    
           if @SRC_NAME_VAL1 is null and not (@DEST_NAME_VAL1 is null) set @PerformUpdate = 1
           if @DEST_NAME_VAL1 is null and not (@SRC_NAME_VAL1 is null) set @PerformUpdate = 1
          
           if (@SRC_DATE_VAL1 != @DEST_DATE_VAL1) set @PerformUpdate = 1
           if @SRC_DATE_VAL1 is null and not (@DEST_DATE_VAL1 is null) set @PerformUpdate = 1
           if @DEST_DATE_VAL1 is null and not (@SRC_DATE_VAL1 is null) set @PerformUpdate = 1
          
           if (@SRC_NUMBER_VAL1 != @DEST_NUMBER_VAL1) set @PerformUpdate = 1
           if @SRC_NUMBER_VAL1 is null and not (@DEST_NUMBER_VAL1 is null) set @PerformUpdate = 1
           if @DEST_NUMBER_VAL1 is null and not (@SRC_NUMBER_VAL1 is null) set @PerformUpdate = 1

  Please take the time to answer Jeff's question – there may be ways to significantly improve the way you are currently doing things. Having said that, I believe that the following code is a shorter version of what you currently have:

  IF NOT EXISTS
(
  SELECT
   @SRC_NAME_VAL1
  , @SRC_DATE_VAL1
  , @SRC_NUMBER_VAL1
  INTERSECT
  SELECT
   @DEST_NAME_VAL1
  , @DEST_DATE_VAL1
  , @DEST_NUMBER_VAL1
)
  SET @PerformUpdate = 1;

  Heh... damn.... now we'll never get an answer and the OP may end up in deep Kimchi.  😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  October 15, 2018 at 11:07 am

  #2009664

  Jeff Moden - Monday, October 15, 2018 7:59 AM

  Phil Parkin - Monday, October 15, 2018 5:20 AM

  allan.ford17 - Sunday, October 14, 2018 9:18 PM

  Hi,

  I wanted to compare 3 variables and set a flag if any of the three differ .. i.e. including handling of null values

  I came up with this which works fine but is not very elegant.   Is there a simple way to do this ?

          if (@SRC_NAME_VAL1 != @DEST_NAME_VAL1) set @PerformUpdate = 1    
           if @SRC_NAME_VAL1 is null and not (@DEST_NAME_VAL1 is null) set @PerformUpdate = 1
           if @DEST_NAME_VAL1 is null and not (@SRC_NAME_VAL1 is null) set @PerformUpdate = 1
          
           if (@SRC_DATE_VAL1 != @DEST_DATE_VAL1) set @PerformUpdate = 1
           if @SRC_DATE_VAL1 is null and not (@DEST_DATE_VAL1 is null) set @PerformUpdate = 1
           if @DEST_DATE_VAL1 is null and not (@SRC_DATE_VAL1 is null) set @PerformUpdate = 1
          
           if (@SRC_NUMBER_VAL1 != @DEST_NUMBER_VAL1) set @PerformUpdate = 1
           if @SRC_NUMBER_VAL1 is null and not (@DEST_NUMBER_VAL1 is null) set @PerformUpdate = 1
           if @DEST_NUMBER_VAL1 is null and not (@SRC_NUMBER_VAL1 is null) set @PerformUpdate = 1

  Please take the time to answer Jeff's question – there may be ways to significantly improve the way you are currently doing things. Having said that, I believe that the following code is a shorter version of what you currently have:

  IF NOT EXISTS
(
  SELECT
   @SRC_NAME_VAL1
  , @SRC_DATE_VAL1
  , @SRC_NUMBER_VAL1
  INTERSECT
  SELECT
   @DEST_NAME_VAL1
  , @DEST_DATE_VAL1
  , @DEST_NUMBER_VAL1
)
  SET @PerformUpdate = 1;

  Heh... damn.... now we'll never get an answer and the OP may end up in deep Kimchi.  😉

  Just for the fun of it...

  SELECT
    @PerformUpdate = COUNT(1)
FROM
    ( SELECT @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1 ) x (name, date, num)
    JOIN ( SELECT @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1 ) y (name, date, num)
        ON x.name <> y.name
        OR x.date <> y.date
        OR x.num <> y.num;

• allan.ford17

  SSC Eights!

  Points: 944

  More actions

  October 15, 2018 at 4:09 pm

  #2009764

  Thank you for these 3 replies !  Excellent ..

  I wasn't sure what the "IF NOT EXISTS" code was about ... Am googling to learn ... Ah ... yes this makes sense to me now ..
  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-2017
  EXISTS ... Returns TRUE if a subquery contains any rows.

  The last option looks good too !
  I'm coming from an Oracle background and PL*SQL ..
  I will test for performance of solutions that you have provided but I assume no issues with any of these solutions ..

  Currently code being used in a test scenario of comparing source and destination with about 50000 rows in each ..
  i.e. the code is used plenty of times !

  I have posted full process code below .. 

  Process can compare and sync 50000 rows in 5 seconds from a source table (or view) to a destination table ..

  Appreciate that better approaches are available than the one row at a time processing, but I was wanting to do this for this process /  algorithm to copy an Oracle PL*SQL process ..

  cheers, thanks, Allan

  Full current code I'm using as per this:

  USE [foraldb]
GO

  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  CREATE TABLE [dbo].[Table_1](
    [ID_COL] [int] NOT NULL,
    [NAME_VAL1] [nchar](50) NOT NULL,
    [DATE_VAL1] [date] NULL,
    [NUMBER_VAL1] [float] NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [ID_COL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

  CREATE TABLE [dbo].[Table_2](
    [ID_COL] [int] NOT NULL,
    [NAME_VAL1] [nchar](50) NOT NULL,
    [DATE_VAL1] [date] NULL,
    [NUMBER_VAL1] [float] NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
    [ID_COL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

  CREATE or ALTER PROCEDURE [dbo].[TablesSyncByCompare]
AS
--
-- note: sql server does not have equivalent of Oracle %ROWTYPE
--https://www.aspsnippets.com/Articles/Using-Cursor-in-SQL-Server-Stored-Procedure-with-example.aspx
--https://www.sqlservercentral.com/Forums/16689/Nesting-Cursors
--https://stackoverflow.com/questions/770300/since-sql-server-doesnt-have-packages-what-do-programmers-do-to-get-around-it
--
DECLARE @i int = 50000
DECLARE @SRC_ID_COL     int = NULL
DECLARE @SRC_NAME_VAL1    nchar (50) = NULL
DECLARE @SRC_DATE_VAL1    datetime = NULL
DECLARE @SRC_NUMBER_VAL1    float = NULL
DECLARE @DEST_ID_COL     int = NULL
DECLARE @DEST_NAME_VAL1    NCHAR (50) = NULL
DECLARE @DEST_DATE_VAL1    DATETIME = NULL
DECLARE @DEST_NUMBER_VAL1    FLOAT = NULL
DECLARE @reccount int = NULL
DECLARE @StopProcessing int = 0
DECLARE @PerformUpdate int = 0
DECLARE @GTFLAG int = NULL
DECLARE @LTFLAG int = NULL
DECLARE @STR nchar (50) = NULL
DECLARE @SRC_FETCH_STATUS int = NULL
DECLARE @DEST_FETCH_STATUS int = NULL

  SELECT @STR = convert(varchar(19), getdate(), 121)
Print ('At start of process at datetime: ' + @STR );

    --DECLARE THE CURSOR FOR A SOURCE QUERY.
  DECLARE C_Source CURSOR LOCAL READ_ONLY
  FOR
  SELECT ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1
  FROM dbo.Table_1
     order by ID_COL
    
  --DECLARE THE CURSOR FOR A DESTINATION QUERY.
  DECLARE C_Dest CURSOR LOCAL READ_ONLY
  FOR
  SELECT ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1
  FROM dbo.Table_2    
     order by ID_COL
    
  --OPEN Source CURSOR.
  OPEN C_Source    
  FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
  SET @SRC_FETCH_STATUS = @@FETCH_STATUS

    --OPEN Dest CURSOR.
  OPEN C_Dest    
  FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
  SET @DEST_FETCH_STATUS = @@FETCH_STATUS    
    
  WHILE (@StopProcessing != 1)
     BEGIN
    
   IF (@DEST_FETCH_STATUS!=0) and (@SRC_FETCH_STATUS =0)
   BEGIN
    --PERFORMADD;
         Insert dbo.Table_2 values (@SRC_ID_COL,@SRC_NAME_VAL1,@SRC_DATE_VAL1,@SRC_NUMBER_VAL1);
    FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
    SET @SRC_FETCH_STATUS = @@FETCH_STATUS        
   END;    

     IF (@DEST_FETCH_STATUS = 0) and (@SRC_FETCH_STATUS !=0)
   BEGIN
    --PERFORMDELETE;
    FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
    SET @DEST_FETCH_STATUS = @@FETCH_STATUS        
   END;    

     IF (@DEST_FETCH_STATUS=0) and (@SRC_FETCH_STATUS =0)
   BEGIN
    set @GTFLAG =0;
    set @LTFLAG =0;
    -- note: this depends upon both source and destination query being ordered correctly.

       if (not (@GTFLAG=1 or @LTFLAG=1)) and (@SRC_ID_COL > @DEST_ID_COL) 
      set @GTFLAG=1;
  if (not (@GTFLAG=1 or @LTFLAG=1)) and (@SRC_ID_COL < @DEST_ID_COL)
      set @LTFLAG=1;    

    if (@GTFLAG=0 and @LTFLAG=0)
  begin
   --performcompare;
         set @PerformUpdate = 0
         /*
         if (@SRC_NAME_VAL1 != @DEST_NAME_VAL1) set @PerformUpdate = 1    
         if @SRC_NAME_VAL1 is null and not (@DEST_NAME_VAL1 is null) set @PerformUpdate = 1
         if @DEST_NAME_VAL1 is null and not (@SRC_NAME_VAL1 is null) set @PerformUpdate = 1
        
         if (@SRC_DATE_VAL1 != @DEST_DATE_VAL1) set @PerformUpdate = 1
         if @SRC_DATE_VAL1 is null and not (@DEST_DATE_VAL1 is null) set @PerformUpdate = 1
         if @DEST_DATE_VAL1 is null and not (@SRC_DATE_VAL1 is null) set @PerformUpdate = 1
        
         if (@SRC_NUMBER_VAL1 != @DEST_NUMBER_VAL1) set @PerformUpdate = 1
         if @SRC_NUMBER_VAL1 is null and not (@DEST_NUMBER_VAL1 is null) set @PerformUpdate = 1
         if @DEST_NUMBER_VAL1 is null and not (@SRC_NUMBER_VAL1 is null) set @PerformUpdate = 1        
    */
        
    IF NOT EXISTS
    (
    SELECT
     @SRC_NAME_VAL1
    , @SRC_DATE_VAL1
    , @SRC_NUMBER_VAL1
    INTERSECT
    SELECT
     @DEST_NAME_VAL1
    , @DEST_DATE_VAL1
    , @DEST_NUMBER_VAL1
    )
    SET @PerformUpdate = 1;
        
         if (@SRC_ID_COL < 40 )
          Print ('debug : record compare performed: ' + CONVERT(varchar(10), @SRC_ID_COL) + ' compare result is:'+ CONVERT(varchar(10), @PerformUpdate));
         if (@PerformUpdate = 1)
         begin
    update dbo.Table_2 set NAME_VAL1 = @SRC_NAME_VAL1, DATE_VAL1=@SRC_DATE_VAL1, NUMBER_VAL1 =@SRC_NUMBER_VAL1 where ID_COL = @DEST_ID_COL;    
            Print ('record update performed: ' + CONVERT(varchar(10), @SRC_ID_COL));
       end        
    FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
    SET @SRC_FETCH_STATUS = @@FETCH_STATUS        
    FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
    SET @DEST_FETCH_STATUS = @@FETCH_STATUS
  end;
  else
   if (@GTFLAG=1)
   begin
    --performdelete;
         delete dbo.Table_2 where ID_COL = @DEST_ID_COL;
    Print ('record delete performed: ' + CONVERT(varchar(10), @DEST_ID_COL));        
    FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
    SET @DEST_FETCH_STATUS = @@FETCH_STATUS
   end;
   else
    if (@LTFLAG=1)
    begin
    --performadd;
            Insert dbo.Table_2 values (@SRC_ID_COL,@SRC_NAME_VAL1,@SRC_DATE_VAL1,@SRC_NUMBER_VAL1);
            Print ('record insert performed: ' + CONVERT(varchar(10), @SRC_ID_COL));
    FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
    SET @SRC_FETCH_STATUS = @@FETCH_STATUS
    end;
    end ;

     if (@SRC_FETCH_STATUS !=0) and (@DEST_FETCH_STATUS!=0)
   BEGIN
   set @StopProcessing = 1;
   END;    
     
     END
    
SELECT @STR = convert(varchar(19), getdate(), 121)    
Print ('At end of process at datetime: ' + @STR );    
GO

  select count(*) from dbo.Table_1;
select count(*) from dbo.Table_2;

  USE [foraldb]
GO

  DECLARE    @return_value int

  EXEC    @return_value = [dbo].[TablesSyncByCompare]

  SELECT    'Return Value' = @return_value

  GO

  SELECT TOP (1000) [ID_COL]
  ,[NAME_VAL1]
  ,[DATE_VAL1]
  ,[NUMBER_VAL1]
FROM [foraldb].[dbo].[Table_2]

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  October 15, 2018 at 5:20 pm

  #2009776

  allan.ford17 - Monday, October 15, 2018 4:09 PM

  Thank you for these 3 replies !  Excellent ..

  I wasn't sure what the "IF NOT EXISTS" code was about ... Am googling to learn ... Ah ... yes this makes sense to me now ..
  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-2017
  EXISTS ... Returns TRUE if a subquery contains any rows.

  The last option looks good too !
  I'm coming from an Oracle background and PL*SQL ..
  I will test for performance of solutions that you have provided but I assume no issues with any of these solutions ..

  Currently code being used in a test scenario of comparing source and destination with about 50000 rows in each ..
  i.e. the code is used plenty of times !

  I have posted full process code below .. 

  Process can compare and sync 50000 rows in 5 seconds from a source table (or view) to a destination table ..

  Appreciate that better approaches are available than the one row at a time processing, but I was wanting to do this for this process /  algorithm to copy an Oracle PL*SQL process ..

  cheers, thanks, Allan

  Allan,

  Thank you for posting what you did, If you were to add some sample/test data along with the expected output, based on that data. We could actually help you come up with a better total solution. 
  (Hint: take a look at the 1st link in Jeff's signature 😉 )

  Based on the code you posted, you don't have to convince anyone you came from Oracle & PL-SQL... Trust me, we believe you...
  But... You're not in Kansas (Oracle) any more... For better or worse, SQL Server was never optimized to handle loops & cursors the same way Oracle has been. It may not be as bad as putting diesel in a gasoline engine but it's not far off. Needless to say this put Oracle developers at a pretty severe disadvantage when they attempt to make the move to SQL Server. 
  It's not just a matter of learning the minor syntax differences between PL_SQL & T-SQL. It actually requires that you change the way you actually think at a very base level... learning to "think in sets" rather than "think in iterations".

  I can't tell you whether or not 50,000 rows in 5 secs is good enough or not but I wouldn't be in the least bit surprised to see a set based solution drop that to a sub-second time.

• allan.ford17

  SSC Eights!

  Points: 944

  More actions

  October 16, 2018 at 8:44 pm

  #2009982

  It would seem that example steps regarding providing data for sqlservercentral does not handle nulls either
  i.e. as per:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  SELECT 'SELECT '
  + QUOTENAME(ID,'''')+','
  + QUOTENAME(DateValue,'''')+','
  + QUOTENAME(Value,'''')+','
  + QUOTENAME(YearValue,'''')+','
  + QUOTENAME(MonthValue,'''')
  + ' UNION ALL'
FROM yourtable

  i.e. I get:
  SELECT '1','John Smith              ','2011-12-12','1234.46' UNION ALL
  SELECT '3','Tom Jones              ','2011-11-21','345.555' UNION ALL
  NULL
  NULL
  NULL

  Can code insert statements like these three:

  Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 10,'Fred Blogs','2011-11-21',NULL ) ;
Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 ) 
 values ( 9,'Tom Jones',NULL,NULL ) ; 
Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
 values ( 12,'John Smith','2011-12-12',1234.456 ) ;

  I looked at SSMS for ability to export data as insert statements but I did not find.
  ? Perhaps I am missing something ..  SSMS must have data export capabilities ?
  (i.e. The Oracle tool SQL*Developer has the ability to export data as insert statements to a file .. )

  I found the export wizard under tasks option from right click of database .. This doesn't work for me ..
  I get error:
  Error 0xc0208030: Data Flow Task 1: The data type for "Destination - sqlserverdata.Inputs[Flat File Destination Input].Columns[definition]" is DT_IMAGE, which is not supported. Use DT_TEXT or DT_NTEXT instead and convert the data from, or to, DT_IMAGE using the data conversion component.
  (SQL Server Import and Export Wizard)

  Oh well .. no matter ..

  The SSMS export wizard worked if I supplied a source query ... but the data is in csv type of format rather than insert statements .. 

  I am wondering how an approach to this type of requirement (data sync between a source and a destination) might look when "thinking in sets" ..
  i.e. This would be a quite common requirement might be to sync a table from a view (or another table) .. i.e. like a materialised view type of thing.

  Am also wanting to avoid any unnecessary inserts, updates, updates.
  i.e. bare minimum of database transactions to sync source and destination.

• Phil Parkin

  SSC Guru

  Points: 246695

  More actions

  October 17, 2018 at 5:36 am

  #2010016

  INSERT scripts can be generated from SSMS, though it's not entirely intuitive how you do it.

  Please take a look here.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  October 18, 2018 at 2:26 am

  #2010190

  allan.ford17 - Monday, October 15, 2018 4:09 PM

  Thank you for these 3 replies !  Excellent ..

  I wasn't sure what the "IF NOT EXISTS" code was about ... Am googling to learn ... Ah ... yes this makes sense to me now ..
  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-2017
  EXISTS ... Returns TRUE if a subquery contains any rows.

  The last option looks good too !
  I'm coming from an Oracle background and PL*SQL ..
  I will test for performance of solutions that you have provided but I assume no issues with any of these solutions ..

  Currently code being used in a test scenario of comparing source and destination with about 50000 rows in each ..
  i.e. the code is used plenty of times !

  I have posted full process code below .. 

  Process can compare and sync 50000 rows in 5 seconds from a source table (or view) to a destination table ..

  Appreciate that better approaches are available than the one row at a time processing, but I was wanting to do this for this process /  algorithm to copy an Oracle PL*SQL process ..

  cheers, thanks, Allan

  Full current code I'm using as per this:

  USE [foraldb]
GO

  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  CREATE TABLE [dbo].[Table_1](
    [ID_COL] [int] NOT NULL,
    [NAME_VAL1] [nchar](50) NOT NULL,
    [DATE_VAL1] [date] NULL,
    [NUMBER_VAL1] [float] NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [ID_COL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

  CREATE TABLE [dbo].[Table_2](
    [ID_COL] [int] NOT NULL,
    [NAME_VAL1] [nchar](50) NOT NULL,
    [DATE_VAL1] [date] NULL,
    [NUMBER_VAL1] [float] NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
    [ID_COL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

  CREATE or ALTER PROCEDURE [dbo].[TablesSyncByCompare]
AS
--
-- note: sql server does not have equivalent of Oracle %ROWTYPE
--https://www.aspsnippets.com/Articles/Using-Cursor-in-SQL-Server-Stored-Procedure-with-example.aspx
--https://www.sqlservercentral.com/Forums/16689/Nesting-Cursors
--https://stackoverflow.com/questions/770300/since-sql-server-doesnt-have-packages-what-do-programmers-do-to-get-around-it
--
DECLARE @i int = 50000
DECLARE @SRC_ID_COL     int = NULL
DECLARE @SRC_NAME_VAL1    nchar (50) = NULL
DECLARE @SRC_DATE_VAL1    datetime = NULL
DECLARE @SRC_NUMBER_VAL1    float = NULL
DECLARE @DEST_ID_COL     int = NULL
DECLARE @DEST_NAME_VAL1    NCHAR (50) = NULL
DECLARE @DEST_DATE_VAL1    DATETIME = NULL
DECLARE @DEST_NUMBER_VAL1    FLOAT = NULL
DECLARE @reccount int = NULL
DECLARE @StopProcessing int = 0
DECLARE @PerformUpdate int = 0
DECLARE @GTFLAG int = NULL
DECLARE @LTFLAG int = NULL
DECLARE @STR nchar (50) = NULL
DECLARE @SRC_FETCH_STATUS int = NULL
DECLARE @DEST_FETCH_STATUS int = NULL

  SELECT @STR = convert(varchar(19), getdate(), 121)
Print ('At start of process at datetime: ' + @STR );

    --DECLARE THE CURSOR FOR A SOURCE QUERY.
  DECLARE C_Source CURSOR LOCAL READ_ONLY
  FOR
  SELECT ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1
  FROM dbo.Table_1
     order by ID_COL
    
  --DECLARE THE CURSOR FOR A DESTINATION QUERY.
  DECLARE C_Dest CURSOR LOCAL READ_ONLY
  FOR
  SELECT ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1
  FROM dbo.Table_2    
     order by ID_COL
    
  --OPEN Source CURSOR.
  OPEN C_Source    
  FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
  SET @SRC_FETCH_STATUS = @@FETCH_STATUS

    --OPEN Dest CURSOR.
  OPEN C_Dest    
  FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
  SET @DEST_FETCH_STATUS = @@FETCH_STATUS    
    
  WHILE (@StopProcessing != 1)
     BEGIN
    
   IF (@DEST_FETCH_STATUS!=0) and (@SRC_FETCH_STATUS =0)
   BEGIN
    --PERFORMADD;
         Insert dbo.Table_2 values (@SRC_ID_COL,@SRC_NAME_VAL1,@SRC_DATE_VAL1,@SRC_NUMBER_VAL1);
    FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
    SET @SRC_FETCH_STATUS = @@FETCH_STATUS        
   END;    

     IF (@DEST_FETCH_STATUS = 0) and (@SRC_FETCH_STATUS !=0)
   BEGIN
    --PERFORMDELETE;
    FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
    SET @DEST_FETCH_STATUS = @@FETCH_STATUS        
   END;    

     IF (@DEST_FETCH_STATUS=0) and (@SRC_FETCH_STATUS =0)
   BEGIN
    set @GTFLAG =0;
    set @LTFLAG =0;
    -- note: this depends upon both source and destination query being ordered correctly.

       if (not (@GTFLAG=1 or @LTFLAG=1)) and (@SRC_ID_COL > @DEST_ID_COL) 
      set @GTFLAG=1;
  if (not (@GTFLAG=1 or @LTFLAG=1)) and (@SRC_ID_COL < @DEST_ID_COL)
      set @LTFLAG=1;    

    if (@GTFLAG=0 and @LTFLAG=0)
  begin
   --performcompare;
         set @PerformUpdate = 0
         /*
         if (@SRC_NAME_VAL1 != @DEST_NAME_VAL1) set @PerformUpdate = 1    
         if @SRC_NAME_VAL1 is null and not (@DEST_NAME_VAL1 is null) set @PerformUpdate = 1
         if @DEST_NAME_VAL1 is null and not (@SRC_NAME_VAL1 is null) set @PerformUpdate = 1
        
         if (@SRC_DATE_VAL1 != @DEST_DATE_VAL1) set @PerformUpdate = 1
         if @SRC_DATE_VAL1 is null and not (@DEST_DATE_VAL1 is null) set @PerformUpdate = 1
         if @DEST_DATE_VAL1 is null and not (@SRC_DATE_VAL1 is null) set @PerformUpdate = 1
        
         if (@SRC_NUMBER_VAL1 != @DEST_NUMBER_VAL1) set @PerformUpdate = 1
         if @SRC_NUMBER_VAL1 is null and not (@DEST_NUMBER_VAL1 is null) set @PerformUpdate = 1
         if @DEST_NUMBER_VAL1 is null and not (@SRC_NUMBER_VAL1 is null) set @PerformUpdate = 1        
    */
        
    IF NOT EXISTS
    (
    SELECT
     @SRC_NAME_VAL1
    , @SRC_DATE_VAL1
    , @SRC_NUMBER_VAL1
    INTERSECT
    SELECT
     @DEST_NAME_VAL1
    , @DEST_DATE_VAL1
    , @DEST_NUMBER_VAL1
    )
    SET @PerformUpdate = 1;
        
         if (@SRC_ID_COL < 40 )
          Print ('debug : record compare performed: ' + CONVERT(varchar(10), @SRC_ID_COL) + ' compare result is:'+ CONVERT(varchar(10), @PerformUpdate));
         if (@PerformUpdate = 1)
         begin
    update dbo.Table_2 set NAME_VAL1 = @SRC_NAME_VAL1, DATE_VAL1=@SRC_DATE_VAL1, NUMBER_VAL1 =@SRC_NUMBER_VAL1 where ID_COL = @DEST_ID_COL;    
            Print ('record update performed: ' + CONVERT(varchar(10), @SRC_ID_COL));
       end        
    FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
    SET @SRC_FETCH_STATUS = @@FETCH_STATUS        
    FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
    SET @DEST_FETCH_STATUS = @@FETCH_STATUS
  end;
  else
   if (@GTFLAG=1)
   begin
    --performdelete;
         delete dbo.Table_2 where ID_COL = @DEST_ID_COL;
    Print ('record delete performed: ' + CONVERT(varchar(10), @DEST_ID_COL));        
    FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
    SET @DEST_FETCH_STATUS = @@FETCH_STATUS
   end;
   else
    if (@LTFLAG=1)
    begin
    --performadd;
            Insert dbo.Table_2 values (@SRC_ID_COL,@SRC_NAME_VAL1,@SRC_DATE_VAL1,@SRC_NUMBER_VAL1);
            Print ('record insert performed: ' + CONVERT(varchar(10), @SRC_ID_COL));
    FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
    SET @SRC_FETCH_STATUS = @@FETCH_STATUS
    end;
    end ;

     if (@SRC_FETCH_STATUS !=0) and (@DEST_FETCH_STATUS!=0)
   BEGIN
   set @StopProcessing = 1;
   END;    
     
     END
    
SELECT @STR = convert(varchar(19), getdate(), 121)    
Print ('At end of process at datetime: ' + @STR );    
GO

  select count(*) from dbo.Table_1;
select count(*) from dbo.Table_2;

  USE [foraldb]
GO

  DECLARE    @return_value int

  EXEC    @return_value = [dbo].[TablesSyncByCompare]

  SELECT    'Return Value' = @return_value

  GO

  SELECT TOP (1000) [ID_COL]
  ,[NAME_VAL1]
  ,[DATE_VAL1]
  ,[NUMBER_VAL1]
FROM [foraldb].[dbo].[Table_2]

  Before we do anything about optimisation - are you sure the logic works?
  Try this:

  Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 10,'Fred Blogs','2011-11-21',NULL ) ;
Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 9,'Tom Jones',NULL,NULL ) ;
Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 12,'John Smith','2011-12-12',1234.456 ) ;

  Insert into Table_2 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 10,'Fred Blogs','2011-11-21',252.1 ) ;
Insert into Table_2 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 11,'Tom Jones',NULL,NULL ) ;
Insert into Table_2 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 12,'John Smith','2011-12-12',1234.456 ) ;
go

  Then execute he procedure:

  select * from dbo.Table_1;
select * from dbo.Table_2;

  DECLARE  @return_value int

  EXEC  @return_value = [dbo].[TablesSyncByCompare]

  SELECT  'Return Value' = @return_value

  GO

  SELECT TOP (1000) [ID_COL]
,[NAME_VAL1]
,[DATE_VAL1]
,[NUMBER_VAL1]
FROM [dbo].[Table_2]
  Is it how it should look like?

  _____________
  Code for TallyGenerator

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  October 18, 2018 at 6:51 am

  #2010216

  allan.ford17 - Tuesday, October 16, 2018 8:44 PM

  It would seem that example steps regarding providing data for sqlservercentral does not handle nulls either
  i.e. as per:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  SELECT 'SELECT '
  + QUOTENAME(ID,'''')+','
  + QUOTENAME(DateValue,'''')+','
  + QUOTENAME(Value,'''')+','
  + QUOTENAME(YearValue,'''')+','
  + QUOTENAME(MonthValue,'''')
  + ' UNION ALL'
FROM yourtable

  i.e. I get:
  SELECT '1','John Smith              ','2011-12-12','1234.46' UNION ALL
  SELECT '3','Tom Jones              ','2011-11-21','345.555' UNION ALL
  NULL
  NULL
  NULL

  Can code insert statements like these three:

  Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 10,'Fred Blogs','2011-11-21',NULL ) ;
Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 ) 
 values ( 9,'Tom Jones',NULL,NULL ) ; 
Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
 values ( 12,'John Smith','2011-12-12',1234.456 ) ;

  I looked at SSMS for ability to export data as insert statements but I did not find.
  ? Perhaps I am missing something ..  SSMS must have data export capabilities ?
  (i.e. The Oracle tool SQL*Developer has the ability to export data as insert statements to a file .. )

  I found the export wizard under tasks option from right click of database .. This doesn't work for me ..
  I get error:
  Error 0xc0208030: Data Flow Task 1: The data type for "Destination - sqlserverdata.Inputs[Flat File Destination Input].Columns[definition]" is DT_IMAGE, which is not supported. Use DT_TEXT or DT_NTEXT instead and convert the data from, or to, DT_IMAGE using the data conversion component.
  (SQL Server Import and Export Wizard)

  Oh well .. no matter ..

  The SSMS export wizard worked if I supplied a source query ... but the data is in csv type of format rather than insert statements .. 

  I am wondering how an approach to this type of requirement (data sync between a source and a destination) might look when "thinking in sets" ..
  i.e. This would be a quite common requirement might be to sync a table from a view (or another table) .. i.e. like a materialised view type of thing.

  Am also wanting to avoid any unnecessary inserts, updates, updates.
  i.e. bare minimum of database transactions to sync source and destination.

  Try this instead:
  SELECT 'SELECT '
+ ISNULL(QUOTENAME(ID,''''), QUOTENAME('NULL', '''')) +','
+ ISNULL(QUOTENAME(DateValue,''''), QUOTENAME('NULL', '''')) +','
+ ISNULL(QUOTENAME([Value],''''), QUOTENAME('NULL', '''')) +','
+ ISNULL(QUOTENAME(YearValue,''''), QUOTENAME('NULL', '''')) +','
+ ISNULL(QUOTENAME(MonthValue,''''), QUOTENAME('NULL', ''''))
+ ' UNION ALL'
FROM yourtable;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• allan.ford17

  SSC Eights!

  Points: 944

  More actions

  October 18, 2018 at 4:27 pm

  #2010324

  Thanks Steve,
  Yep this worked ... but I didn't need the NULL in quotes ..
  i.e. 
  SELECT 'SELECT '
+ ISNULL(QUOTENAME(ID_COL,''''), 'NULL') +','
+ ISNULL(QUOTENAME(NAME_VAL1,''''), 'NULL') +','
+ ISNULL(QUOTENAME(DATE_VAL1,''''), 'NULL') +','
+ ISNULL(QUOTENAME(NUMBER_VAL1,''''), 'NULL')
+ ' UNION ALL'
FROM dbo.Table_1

  helps generate a statement like:
  INSERT INTO TABLE_3
   (ID, DateValue, Value, YearValue, Monthvalue)
SELECT '1','John Smith              ','2011-12-12','1234.46', UNION ALL
SELECT '2','John Smith              ','2011-12-12','1234.46', UNION ALL
SELECT '3','Tom Jones              ','2011-11-21','345.555', UNION ALL
SELECT '4','Sarah Ford              ',NULL,'43434', UNION ALL
SELECT '6','John Smith              ','2011-12-12','1234.46'
;

• allan.ford17

  SSC Eights!

  Points: 944

  More actions

  October 18, 2018 at 4:48 pm

  #2010327

  Sergiy - Thursday, October 18, 2018 2:26 AM

  allan.ford17 - Monday, October 15, 2018 4:09 PM

  Thank you for these 3 replies !  Excellent ..

  I wasn't sure what the "IF NOT EXISTS" code was about ... Am googling to learn ... Ah ... yes this makes sense to me now ..
  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-2017
  EXISTS ... Returns TRUE if a subquery contains any rows.

  The last option looks good too !
  I'm coming from an Oracle background and PL*SQL ..
  I will test for performance of solutions that you have provided but I assume no issues with any of these solutions ..

  Currently code being used in a test scenario of comparing source and destination with about 50000 rows in each ..
  i.e. the code is used plenty of times !

  I have posted full process code below .. 

  Process can compare and sync 50000 rows in 5 seconds from a source table (or view) to a destination table ..

  Appreciate that better approaches are available than the one row at a time processing, but I was wanting to do this for this process /  algorithm to copy an Oracle PL*SQL process ..

  cheers, thanks, Allan

  Full current code I'm using as per this:

  USE [foraldb]
GO

  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  CREATE TABLE [dbo].[Table_1](
    [ID_COL] [int] NOT NULL,
    [NAME_VAL1] [nchar](50) NOT NULL,
    [DATE_VAL1] [date] NULL,
    [NUMBER_VAL1] [float] NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [ID_COL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

  CREATE TABLE [dbo].[Table_2](
    [ID_COL] [int] NOT NULL,
    [NAME_VAL1] [nchar](50) NOT NULL,
    [DATE_VAL1] [date] NULL,
    [NUMBER_VAL1] [float] NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
    [ID_COL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

  CREATE or ALTER PROCEDURE [dbo].[TablesSyncByCompare]
AS
--
-- note: sql server does not have equivalent of Oracle %ROWTYPE
--https://www.aspsnippets.com/Articles/Using-Cursor-in-SQL-Server-Stored-Procedure-with-example.aspx
--https://www.sqlservercentral.com/Forums/16689/Nesting-Cursors
--https://stackoverflow.com/questions/770300/since-sql-server-doesnt-have-packages-what-do-programmers-do-to-get-around-it
--
DECLARE @i int = 50000
DECLARE @SRC_ID_COL     int = NULL
DECLARE @SRC_NAME_VAL1    nchar (50) = NULL
DECLARE @SRC_DATE_VAL1    datetime = NULL
DECLARE @SRC_NUMBER_VAL1    float = NULL
DECLARE @DEST_ID_COL     int = NULL
DECLARE @DEST_NAME_VAL1    NCHAR (50) = NULL
DECLARE @DEST_DATE_VAL1    DATETIME = NULL
DECLARE @DEST_NUMBER_VAL1    FLOAT = NULL
DECLARE @reccount int = NULL
DECLARE @StopProcessing int = 0
DECLARE @PerformUpdate int = 0
DECLARE @GTFLAG int = NULL
DECLARE @LTFLAG int = NULL
DECLARE @STR nchar (50) = NULL
DECLARE @SRC_FETCH_STATUS int = NULL
DECLARE @DEST_FETCH_STATUS int = NULL

  SELECT @STR = convert(varchar(19), getdate(), 121)
Print ('At start of process at datetime: ' + @STR );

    --DECLARE THE CURSOR FOR A SOURCE QUERY.
  DECLARE C_Source CURSOR LOCAL READ_ONLY
  FOR
  SELECT ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1
  FROM dbo.Table_1
     order by ID_COL
    
  --DECLARE THE CURSOR FOR A DESTINATION QUERY.
  DECLARE C_Dest CURSOR LOCAL READ_ONLY
  FOR
  SELECT ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1
  FROM dbo.Table_2    
     order by ID_COL
    
  --OPEN Source CURSOR.
  OPEN C_Source    
  FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
  SET @SRC_FETCH_STATUS = @@FETCH_STATUS

    --OPEN Dest CURSOR.
  OPEN C_Dest    
  FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
  SET @DEST_FETCH_STATUS = @@FETCH_STATUS    
    
  WHILE (@StopProcessing != 1)
     BEGIN
    
   IF (@DEST_FETCH_STATUS!=0) and (@SRC_FETCH_STATUS =0)
   BEGIN
    --PERFORMADD;
         Insert dbo.Table_2 values (@SRC_ID_COL,@SRC_NAME_VAL1,@SRC_DATE_VAL1,@SRC_NUMBER_VAL1);
    FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
    SET @SRC_FETCH_STATUS = @@FETCH_STATUS        
   END;    

     IF (@DEST_FETCH_STATUS = 0) and (@SRC_FETCH_STATUS !=0)
   BEGIN
    --PERFORMDELETE;
    FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
    SET @DEST_FETCH_STATUS = @@FETCH_STATUS        
   END;    

     IF (@DEST_FETCH_STATUS=0) and (@SRC_FETCH_STATUS =0)
   BEGIN
    set @GTFLAG =0;
    set @LTFLAG =0;
    -- note: this depends upon both source and destination query being ordered correctly.

       if (not (@GTFLAG=1 or @LTFLAG=1)) and (@SRC_ID_COL > @DEST_ID_COL) 
      set @GTFLAG=1;
  if (not (@GTFLAG=1 or @LTFLAG=1)) and (@SRC_ID_COL < @DEST_ID_COL)
      set @LTFLAG=1;    

    if (@GTFLAG=0 and @LTFLAG=0)
  begin
   --performcompare;
         set @PerformUpdate = 0
         /*
         if (@SRC_NAME_VAL1 != @DEST_NAME_VAL1) set @PerformUpdate = 1    
         if @SRC_NAME_VAL1 is null and not (@DEST_NAME_VAL1 is null) set @PerformUpdate = 1
         if @DEST_NAME_VAL1 is null and not (@SRC_NAME_VAL1 is null) set @PerformUpdate = 1
        
         if (@SRC_DATE_VAL1 != @DEST_DATE_VAL1) set @PerformUpdate = 1
         if @SRC_DATE_VAL1 is null and not (@DEST_DATE_VAL1 is null) set @PerformUpdate = 1
         if @DEST_DATE_VAL1 is null and not (@SRC_DATE_VAL1 is null) set @PerformUpdate = 1
        
         if (@SRC_NUMBER_VAL1 != @DEST_NUMBER_VAL1) set @PerformUpdate = 1
         if @SRC_NUMBER_VAL1 is null and not (@DEST_NUMBER_VAL1 is null) set @PerformUpdate = 1
         if @DEST_NUMBER_VAL1 is null and not (@SRC_NUMBER_VAL1 is null) set @PerformUpdate = 1        
    */
        
    IF NOT EXISTS
    (
    SELECT
     @SRC_NAME_VAL1
    , @SRC_DATE_VAL1
    , @SRC_NUMBER_VAL1
    INTERSECT
    SELECT
     @DEST_NAME_VAL1
    , @DEST_DATE_VAL1
    , @DEST_NUMBER_VAL1
    )
    SET @PerformUpdate = 1;
        
         if (@SRC_ID_COL < 40 )
          Print ('debug : record compare performed: ' + CONVERT(varchar(10), @SRC_ID_COL) + ' compare result is:'+ CONVERT(varchar(10), @PerformUpdate));
         if (@PerformUpdate = 1)
         begin
    update dbo.Table_2 set NAME_VAL1 = @SRC_NAME_VAL1, DATE_VAL1=@SRC_DATE_VAL1, NUMBER_VAL1 =@SRC_NUMBER_VAL1 where ID_COL = @DEST_ID_COL;    
            Print ('record update performed: ' + CONVERT(varchar(10), @SRC_ID_COL));
       end        
    FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
    SET @SRC_FETCH_STATUS = @@FETCH_STATUS        
    FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
    SET @DEST_FETCH_STATUS = @@FETCH_STATUS
  end;
  else
   if (@GTFLAG=1)
   begin
    --performdelete;
         delete dbo.Table_2 where ID_COL = @DEST_ID_COL;
    Print ('record delete performed: ' + CONVERT(varchar(10), @DEST_ID_COL));        
    FETCH NEXT FROM C_Dest INTO @DEST_ID_COL, @DEST_NAME_VAL1, @DEST_DATE_VAL1, @DEST_NUMBER_VAL1
    SET @DEST_FETCH_STATUS = @@FETCH_STATUS
   end;
   else
    if (@LTFLAG=1)
    begin
    --performadd;
            Insert dbo.Table_2 values (@SRC_ID_COL,@SRC_NAME_VAL1,@SRC_DATE_VAL1,@SRC_NUMBER_VAL1);
            Print ('record insert performed: ' + CONVERT(varchar(10), @SRC_ID_COL));
    FETCH NEXT FROM C_Source INTO @SRC_ID_COL, @SRC_NAME_VAL1, @SRC_DATE_VAL1, @SRC_NUMBER_VAL1
    SET @SRC_FETCH_STATUS = @@FETCH_STATUS
    end;
    end ;

     if (@SRC_FETCH_STATUS !=0) and (@DEST_FETCH_STATUS!=0)
   BEGIN
   set @StopProcessing = 1;
   END;    
     
     END
    
SELECT @STR = convert(varchar(19), getdate(), 121)    
Print ('At end of process at datetime: ' + @STR );    
GO

  select count(*) from dbo.Table_1;
select count(*) from dbo.Table_2;

  USE [foraldb]
GO

  DECLARE    @return_value int

  EXEC    @return_value = [dbo].[TablesSyncByCompare]

  SELECT    'Return Value' = @return_value

  GO

  SELECT TOP (1000) [ID_COL]
  ,[NAME_VAL1]
  ,[DATE_VAL1]
  ,[NUMBER_VAL1]
FROM [foraldb].[dbo].[Table_2]

  Before we do anything about optimisation - are you sure the logic works?
  Try this:

  Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 10,'Fred Blogs','2011-11-21',NULL ) ;
Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 9,'Tom Jones',NULL,NULL ) ;
Insert into Table_1 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 12,'John Smith','2011-12-12',1234.456 ) ;

  Insert into Table_2 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 10,'Fred Blogs','2011-11-21',252.1 ) ;
Insert into Table_2 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 11,'Tom Jones',NULL,NULL ) ;
Insert into Table_2 ( ID_COL,NAME_VAL1,DATE_VAL1,NUMBER_VAL1 )
values ( 12,'John Smith','2011-12-12',1234.456 ) ;
go

  Then execute he procedure:

  select * from dbo.Table_1;
select * from dbo.Table_2;

  DECLARE  @return_value int

  EXEC  @return_value = [dbo].[TablesSyncByCompare]

  SELECT  'Return Value' = @return_value

  GO

  SELECT TOP (1000) [ID_COL]
,[NAME_VAL1]
,[DATE_VAL1]
,[NUMBER_VAL1]
FROM [dbo].[Table_2]
  Is it how it should look like?

  Sergiy - Yep ... The result from this above steps is 3 rows in Table_2 which exactly match the 3 rows in Table_1 ... which is what I wanted via this sync process. .. and running the procedure again does data compare but no actual table transactions ..

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  October 18, 2018 at 5:45 pm

  #2010334

  allan.ford17 - Thursday, October 18, 2018 4:48 PM

  Sergiy - Yep ... The result from this above steps is 3 rows in Table_2 which exactly match the 3 rows in Table_1 ... which is what I wanted via this sync process. .. and running the procedure again does data compare but no actual table transactions ..

  OK, if that's the point - why not just truncate Table 2 and copy all records from Table 1 to it?

  _____________
  Code for TallyGenerator

• allan.ford17

  SSC Eights!

  Points: 944

  More actions

  October 18, 2018 at 6:11 pm

  #2010337

  I wanted a process that does absolute minimal DB transactions ... i.e. there may be a table row level trigger than logs transactions for other integration purposes. There may also be child records in child and grandchildren tables ...