October 14, 2018 at 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
October 14, 2018 at 10:53 pm
allan.ford17 - Sunday, October 14, 2018 9:18 PMHi,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
Change is inevitable... Change for the better is not.
October 15, 2018 at 5:20 am
allan.ford17 - Sunday, October 14, 2018 9:18 PMHi,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;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 15, 2018 at 7:59 am
Phil Parkin - Monday, October 15, 2018 5:20 AMallan.ford17 - Sunday, October 14, 2018 9:18 PMHi,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 = 1Please 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
Change is inevitable... Change for the better is not.
October 15, 2018 at 11:07 am
Jeff Moden - Monday, October 15, 2018 7:59 AMPhil Parkin - Monday, October 15, 2018 5:20 AMallan.ford17 - Sunday, October 14, 2018 9:18 PMHi,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 = 1Please 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;
October 15, 2018 at 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]
October 15, 2018 at 5:20 pm
allan.ford17 - Monday, October 15, 2018 4:09 PMThank 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.
October 16, 2018 at 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.
October 17, 2018 at 5:36 am
INSERT scripts can be generated from SSMS, though it's not entirely intuitive how you do it.
Please take a look here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 18, 2018 at 2:26 am
allan.ford17 - Monday, October 15, 2018 4:09 PMThank 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]
GOSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE 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]
GOCREATE 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]
GOCREATE 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 = NULLSELECT @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 );
GOselect count(*) from dbo.Table_1;
select count(*) from dbo.Table_2;USE [foraldb]
GODECLARE @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
October 18, 2018 at 6:51 am
allan.ford17 - Tuesday, October 16, 2018 8:44 PMIt 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 yourtablei.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
NULLCan 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)
October 18, 2018 at 4:27 pm
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'
;
October 18, 2018 at 4:48 pm
Sergiy - Thursday, October 18, 2018 2:26 AMallan.ford17 - Monday, October 15, 2018 4:09 PMThank 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]
GOSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE 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]
GOCREATE 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]
GOCREATE 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 = NULLSELECT @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 );
GOselect count(*) from dbo.Table_1;
select count(*) from dbo.Table_2;USE [foraldb]
GODECLARE @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 ) ;
goThen 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 ..
October 18, 2018 at 5:45 pm
allan.ford17 - Thursday, October 18, 2018 4:48 PMSergiy - 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
October 18, 2018 at 6:11 pm
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 ...
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply