Dynamic Sql Query

Question

Dynamic Sql Query

skandan1976

SSC Enthusiast

Points: 101
More actions
March 14, 2017 at 2:58 pm

#319658

I am trying to compare two tables columns to make sure both are matching. I have done the basic row counts which is matching now I would like to compare the column level matching.
Data base Name - Thin PRC First table - Thin_Water -
Select count(*) from Thin_Water = Answer 10
Second Database name : - Load
Second Table - Water_Derivation
To get count I have removed the duplicates by joining with Water_Duplicate as below
Select count(*)From Water_Derivation AS WDERinner join Water_Dupe AS WD (Duplicate data table)on WDER.ident = WD.Identwhere WDER.Loadid =50 and WD.Dupe='y'
so this counts matches the Thin_Water = answer 10
Now I want to amend the below query to include the above principal to get an answer, i dont know how to get rid of duplicates using Duplicate table and using condition for latest load id
Below query works but brings unmatched records, because I have not eliminated the duplicates and currnet load id. I need to include those tasks in below script. Any help will be appreciated
declare @tbl1 varchar(255)= 'Thin_water'
declare @tbl2 varchar(255)= 'Water_Derivation'
declare @cols table (id int identity(1,1),colname varchar(255))
declare @joinType varchar(10)='right'
insert into @cols (colname) values ('Der_Postcode_Sector')
declare @rc int=1,@mr int=(Select count(id) from @cols)
declare @colName varchar(255)
declare @cmd nvarchar(max)
exec sp_executesql @cmd
while (@rc<=@mr)
begin
select @colName=colName from @cols where id=@rc
set @cmd=
'declare @diffs table
([id] [int] identity(1,1),
   [colName] [varchar](255),
   [colVal_real] [varchar](255),
   [colVal_mine] [varchar](255),
   [real] [int],
   [mine] [int],
   [diff] [int]
)
insert into @diffs (colName,colVal_real,colVal_mine,real,mine,diff)
select '''+@colName+''' as colName,a.colVal,b.colVal,isnull(a.c,0) as real,isnull(b.c,0) as mine, isnull(a.c,0)-isnull(b.c,0) as diff
from
(select isnull(convert(varchar(255),'+@colName+'),''_<null>_'') as colVal,count(*) as c
   from '+@tbl1+' group by '+@colName+'
) a
'+@joinType+' join
(select isnull(convert(varchar(255),'+@colName+'),''_<null>_'') as colVal,count(*) as c
   from '+@tbl2+' group by '+@colName+'
) b on a.colVal=b.colVal
   where a.c is null or b.c is null or (isnull(a.c,0)-isnull(b.c,0))!=0
   select * from @diffs
    '
print @cmd
exec sp_executeSQL @cmd
set @rc=@rc+1
end

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

Is this some sort of homework assignment? I am confused what "Answer 10" is supposed to be.

but there are a few odd things in your code. Like why do you call "EXEC [sys].[sp_executesql] @cmd" before your loop where @cmd is null? This line will do nothing.

I do not see the point of the loop when @cols contains 1 row so it only loops 1 time. Is this intentional?

And the reason you are getting duplicates when you don't want them is because you are not filtering where WD.Dupe = 'Y'.
So... if a loop and dynamic SQL are what is required for this........ task, I'd look at the FROM clause with respect to @tbl2 and add a WHERE dupe = 'Y' to it before grouping.

Also, in the future, to make the code more readable, could you highlight all of your SQL code and use the "SQL Code" button at the bottom of the input box? It makes reading your code a lot easier.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

skandan1976 SSC Enthusiast Points: 101 More actions · Answer 2

bmg002 - Tuesday, March 14, 2017 3:37 PM
Is this some sort of homework assignment? I am confused what "Answer 10" is supposed to be.
Its not an assignment, the answer is just an example for count
but there are a few odd things in your code. Like why do you call "EXEC [sys].[sp_executesql] @cmd" before your loop where @cmd is null? This line will do nothing.
ok
I do not see the point of the loop when @cols contains 1 row so it only loops 1 time. Is this intentional?
And the reason you are getting duplicates when you don't want them is because you are not filtering where WD.Dupe = 'Y'.
So... if a loop and dynamic SQL are what is required for this........ task, I'd look at the FROM clause with respect to @tbl2 and add a WHERE dupe = 'Y' to it before grouping.
I cant use where clause directly @tbl2 becuase that column not belogs to @tbl2, tbl2 counts derived by joining another table (Water_Dupe)
Also, in the future, to make the code more readable, could you highlight all of your SQL code and use the "SQL Code" button at the bottom of the input box? It makes reading your code a lot easier.
Sorry I am new to this forum, I dont know how to format my code.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Is this what you are trying to accomplish? (Please note I commented out the actual execution of the code):

declare @cols table (id int identity(1,1),colname varchar(255)) insert into @cols (colname) values ('Der_Postcode_Sector')

declare @tbl1 varchar(255) = 'Thin_water',
        @tbl2 varchar(255) = 'Water_Derivation',
        @joinType varchar(10)='right',
        @LoadId int = 50,
        @rc int = 1,
        @mr int = (Select count(id) from @cols),
        @colName varchar(255),
        @cmd nvarchar(max),
        @SQLcmd nvarchar(max),
        @SQLparms nvarchar(max) = '@colName varchar(255), @LoadId int';

set @cmd = N'
declare @diffs table (
[id] [int] identity(1,1),
[colName] [varchar](255),
[colVal_real] [varchar](255),
[colVal_mine] [varchar](255),
[real] [int],
[mine] [int],
[diff] [int]
);

insert into @diffs (
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
)
select
@colName as colName
,a.colVal
,b.colVal
,isnull(a.c,0) as real
,isnull(b.c,0) as mine
,isnull(a.c,0) - isnull(b.c,0) as diff
from (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal,
count(*) as c
from
@tbl1@ WDER
inner join Water_Dupe AS WD --(Duplicate data table)
on WDER.ident = WD.Ident
where
WDER.Loadid = @LoadId
and WD.Dupe = ''y''
group by
@colName@
) a
@joinType@ join (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal
,count(*) as c
from
@tbl2@
group by
@colName@
) b on a.colVal = b.colVal
where
a.c is null
or b.c is null
or (isnull(a.c,0) - isnull(b.c,0)) != 0;

select
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
from
@diffs;
';

DECLARE FireHose CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
select
colName
from
@cols;

open FireHose;

fetch next from FireHose
into @colName;

while @@FETCH_STATUS = 0
begin
set @SQLcmd = replace(replace(replace(replace(@cmd,'@colName@',@colname),'@tbl1@',@tbl1),'@tbl2@',@tbl2),'@joinType@',@joinType);
--exec sys.sp_executesql @SQLCmd, @SQLparms, @colName = @colName, @LoadId = @LoadId;
print @SQLCmd;

fetch next from FireHose
into @colName;
end

close FireHose;

deallocate FireHose;

After you look at the code, I will expect there to be some questions. This isn't just a straight forward piece of dynamic SQL.

skandan1976 SSC Enthusiast Points: 101 More actions · Answer 4

Lynn Pettis - Tuesday, March 14, 2017 6:48 PM
Is this what you are trying to accomplish? (Please note I commented out the actual execution of the code):
Thanks for the code but still I have some errors, Error :Ident invalid column - (Ident is bigint in both tables) and Loadid is invalid column, this is one of the column in derviation table, I need to select max load id.
declare @cols table (id int identity(1,1),colname varchar(255)) insert into @cols (colname) values ('Der_Postcode_Sector')
declare @tbl1 varchar(255) = 'Thin_water',
        @tbl2 varchar(255) = 'Water_Derivation',
        @joinType varchar(10)='right',
        @LoadId int = 50,
        @rc int = 1,
        @mr int = (Select count(id) from @cols),
        @colName varchar(255),
        @cmd nvarchar(max),
        @SQLcmd nvarchar(max),
        @SQLparms nvarchar(max) = '@colName varchar(255), @LoadId int';
set @cmd = N'
declare @diffs table (
[id] [int] identity(1,1),
[colName] [varchar](255),
[colVal_real] [varchar](255),
[colVal_mine] [varchar](255),
[real] [int],
[mine] [int],
[diff] [int]
);
insert into @diffs (
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
)
select
@colName as colName
,a.colVal
,b.colVal
,isnull(a.c,0) as real
,isnull(b.c,0) as mine
,isnull(a.c,0) - isnull(b.c,0) as diff
from (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal,
count(*) as c
from
@tbl1@ WDER
inner join Water_Dupe AS WD --(Duplicate data table)
    on WDER.ident = WD.Ident
where
WDER.Loadid = @LoadId
and WD.Dupe = ''y''
group by
@colName@
) a
@joinType@ join (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal
,count(*) as c
from
@tbl2@
group by
@colName@
) b on a.colVal = b.colVal
where
a.c is null
or b.c is null
or (isnull(a.c,0) - isnull(b.c,0)) != 0;
select
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
from
@diffs;
';

DECLARE FireHose CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
select
colName
from
@cols;
open FireHose;
fetch next from FireHose
into @colName;
while @@FETCH_STATUS = 0
begin
set @SQLcmd = replace(replace(replace(replace(@cmd,'@colName@',@colname),'@tbl1@',@tbl1),'@tbl2@',@tbl2),'@joinType@',@joinType);
--exec sys.sp_executesql @SQLCmd, @SQLparms, @colName = @colName, @LoadId = @LoadId;
print @SQLCmd;
fetch next from FireHose
into @colName;
end
close FireHose;
deallocate FireHose;
After you look at the code, I will expect there to be some questions. This isn't just a straight forward piece of dynamic SQL.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

skandan1976 - Tuesday, March 14, 2017 11:36 PM
Lynn Pettis - Tuesday, March 14, 2017 6:48 PM
Is this what you are trying to accomplish? (Please note I commented out the actual execution of the code):
Thanks for the code but still I have some errors, Error :Ident invalid column - (Ident is bigint in both tables) and Loadid is invalid column, this is one of the column in derviation table, I need to select max load id.
declare @cols table (id int identity(1,1),colname varchar(255)) insert into @cols (colname) values ('Der_Postcode_Sector')
declare @tbl1 varchar(255) = 'Thin_water',
        @tbl2 varchar(255) = 'Water_Derivation',
        @joinType varchar(10)='right',
        @LoadId int = 50,
        @rc int = 1,
        @mr int = (Select count(id) from @cols),
        @colName varchar(255),
        @cmd nvarchar(max),
        @SQLcmd nvarchar(max),
        @SQLparms nvarchar(max) = '@colName varchar(255), @LoadId int';
set @cmd = N'
declare @diffs table (
[id] [int] identity(1,1),
[colName] [varchar](255),
[colVal_real] [varchar](255),
[colVal_mine] [varchar](255),
[real] [int],
[mine] [int],
[diff] [int]
);
insert into @diffs (
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
)
select
@colName as colName
,a.colVal
,b.colVal
,isnull(a.c,0) as real
,isnull(b.c,0) as mine
,isnull(a.c,0) - isnull(b.c,0) as diff
from (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal,
count(*) as c
from
@tbl1@ WDER
inner join Water_Dupe AS WD --(Duplicate data table)
    on WDER.ident = WD.Ident
where
WDER.Loadid = @LoadId
and WD.Dupe = ''y''
group by
@colName@
) a
@joinType@ join (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal
,count(*) as c
from
@tbl2@
group by
@colName@
) b on a.colVal = b.colVal
where
a.c is null
or b.c is null
or (isnull(a.c,0) - isnull(b.c,0)) != 0;
select
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
from
@diffs;
';

DECLARE FireHose CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
select
colName
from
@cols;
open FireHose;
fetch next from FireHose
into @colName;
while @@FETCH_STATUS = 0
begin
set @SQLcmd = replace(replace(replace(replace(@cmd,'@colName@',@colname),'@tbl1@',@tbl1),'@tbl2@',@tbl2),'@joinType@',@joinType);
--exec sys.sp_executesql @SQLCmd, @SQLparms, @colName = @colName, @LoadId = @LoadId;
print @SQLCmd;
fetch next from FireHose
into @colName;
end
close FireHose;
deallocate FireHose;
After you look at the code, I will expect there to be some questions. This isn't just a straight forward piece of dynamic SQL.

Sorry, but looking back at what you originally posted, ident is the key value used to eliminate dups. I also included that code. Was this wrong? Wasn't that what also needed to be done?

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 6

Lynn Pettis - Tuesday, March 14, 2017 6:48 PM
Is this what you are trying to accomplish? (Please note I commented out the actual execution of the code):
declare @cols table (id int identity(1,1),colname varchar(255)) insert into @cols (colname) values ('Der_Postcode_Sector')
declare @tbl1 varchar(255) = 'Thin_water',
        @tbl2 varchar(255) = 'Water_Derivation',
        @joinType varchar(10)='right',
        @LoadId int = 50,
        @rc int = 1,
        @mr int = (Select count(id) from @cols),
        @colName varchar(255),
        @cmd nvarchar(max),
        @SQLcmd nvarchar(max),
        @SQLparms nvarchar(max) = '@colName varchar(255), @LoadId int';
set @cmd = N'
declare @diffs table (
[id] [int] identity(1,1),
[colName] [varchar](255),
[colVal_real] [varchar](255),
[colVal_mine] [varchar](255),
[real] [int],
[mine] [int],
[diff] [int]
);
insert into @diffs (
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
)
select
@colName as colName
,a.colVal
,b.colVal
,isnull(a.c,0) as real
,isnull(b.c,0) as mine
,isnull(a.c,0) - isnull(b.c,0) as diff
from (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal,
count(*) as c
from
@tbl1@ WDER
inner join Water_Dupe AS WD --(Duplicate data table)
    on WDER.ident = WD.Ident
where
WDER.Loadid = @LoadId
and WD.Dupe = ''y''
group by
@colName@
) a
@joinType@ join (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal
,count(*) as c
from
@tbl2@
group by
@colName@
) b on a.colVal = b.colVal
where
a.c is null
or b.c is null
or (isnull(a.c,0) - isnull(b.c,0)) != 0;
select
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
from
@diffs;
';

DECLARE FireHose CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
select
colName
from
@cols;
open FireHose;
fetch next from FireHose
into @colName;
while @@FETCH_STATUS = 0
begin
set @SQLcmd = replace(replace(replace(replace(@cmd,'@colName@',@colname),'@tbl1@',@tbl1),'@tbl2@',@tbl2),'@joinType@',@joinType);
--exec sys.sp_executesql @SQLCmd, @SQLparms, @colName = @colName, @LoadId = @LoadId;
print @SQLCmd;
fetch next from FireHose
into @colName;
end
close FireHose;
deallocate FireHose;
After you look at the code, I will expect there to be some questions. This isn't just a straight forward piece of dynamic SQL.

Not meaning to nit-pick, but doesn't this miss the original question and just re-write the original SQL? The SQL is nicer and easier to read/follow and I believe from reading it should be more efficient, but doesn't it have the same net effect?
I think you'd need to add in a
JOIN Water_Dupe ON @tbl2.Ident = Water_Dupe.Ident AND Water_Dupe.Dupe = 'Y'
no?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

bmg002 - Wednesday, March 15, 2017 9:26 AM
Lynn Pettis - Tuesday, March 14, 2017 6:48 PM
Is this what you are trying to accomplish? (Please note I commented out the actual execution of the code):
declare @cols table (id int identity(1,1),colname varchar(255)) insert into @cols (colname) values ('Der_Postcode_Sector')
declare @tbl1 varchar(255) = 'Thin_water',
        @tbl2 varchar(255) = 'Water_Derivation',
        @joinType varchar(10)='right',
        @LoadId int = 50,
        @rc int = 1,
        @mr int = (Select count(id) from @cols),
        @colName varchar(255),
        @cmd nvarchar(max),
        @SQLcmd nvarchar(max),
        @SQLparms nvarchar(max) = '@colName varchar(255), @LoadId int';
set @cmd = N'
declare @diffs table (
[id] [int] identity(1,1),
[colName] [varchar](255),
[colVal_real] [varchar](255),
[colVal_mine] [varchar](255),
[real] [int],
[mine] [int],
[diff] [int]
);
insert into @diffs (
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
)
select
@colName as colName
,a.colVal
,b.colVal
,isnull(a.c,0) as real
,isnull(b.c,0) as mine
,isnull(a.c,0) - isnull(b.c,0) as diff
from (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal,
count(*) as c
from
@tbl1@ WDER
inner join Water_Dupe AS WD --(Duplicate data table)
    on WDER.ident = WD.Ident
where
WDER.Loadid = @LoadId
and WD.Dupe = ''y''
group by
@colName@
) a
@joinType@ join (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal
,count(*) as c
from
@tbl2@
group by
@colName@
) b on a.colVal = b.colVal
where
a.c is null
or b.c is null
or (isnull(a.c,0) - isnull(b.c,0)) != 0;
select
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
from
@diffs;
';

DECLARE FireHose CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
select
colName
from
@cols;
open FireHose;
fetch next from FireHose
into @colName;
while @@FETCH_STATUS = 0
begin
set @SQLcmd = replace(replace(replace(replace(@cmd,'@colName@',@colname),'@tbl1@',@tbl1),'@tbl2@',@tbl2),'@joinType@',@joinType);
--exec sys.sp_executesql @SQLCmd, @SQLparms, @colName = @colName, @LoadId = @LoadId;
print @SQLCmd;
fetch next from FireHose
into @colName;
end
close FireHose;
deallocate FireHose;
After you look at the code, I will expect there to be some questions. This isn't just a straight forward piece of dynamic SQL.
Not meaning to nit-pick, but doesn't this miss the original question and just re-write the original SQL? The SQL is nicer and easier to read/follow and I believe from reading it should be more efficient, but doesn't it have the same net effect?
I think you'd need to add in a
JOIN Water_Dupe ON @tbl2.Ident = Water_Dupe.Ident AND Water_Dupe.Dupe = 'Y'
no?

I did add that.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 8

Lynn Pettis - Wednesday, March 15, 2017 9:48 AM
bmg002 - Wednesday, March 15, 2017 9:26 AM
Lynn Pettis - Tuesday, March 14, 2017 6:48 PM
Is this what you are trying to accomplish? (Please note I commented out the actual execution of the code):
declare @cols table (id int identity(1,1),colname varchar(255)) insert into @cols (colname) values ('Der_Postcode_Sector')
declare @tbl1 varchar(255) = 'Thin_water',
        @tbl2 varchar(255) = 'Water_Derivation',
        @joinType varchar(10)='right',
        @LoadId int = 50,
        @rc int = 1,
        @mr int = (Select count(id) from @cols),
        @colName varchar(255),
        @cmd nvarchar(max),
        @SQLcmd nvarchar(max),
        @SQLparms nvarchar(max) = '@colName varchar(255), @LoadId int';
set @cmd = N'
declare @diffs table (
[id] [int] identity(1,1),
[colName] [varchar](255),
[colVal_real] [varchar](255),
[colVal_mine] [varchar](255),
[real] [int],
[mine] [int],
[diff] [int]
);
insert into @diffs (
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
)
select
@colName as colName
,a.colVal
,b.colVal
,isnull(a.c,0) as real
,isnull(b.c,0) as mine
,isnull(a.c,0) - isnull(b.c,0) as diff
from (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal,
count(*) as c
from
@tbl1@ WDER
inner join Water_Dupe AS WD --(Duplicate data table)
    on WDER.ident = WD.Ident
where
WDER.Loadid = @LoadId
and WD.Dupe = ''y''
group by
@colName@
) a
@joinType@ join (
select
isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal
,count(*) as c
from
@tbl2@
group by
@colName@
) b on a.colVal = b.colVal
where
a.c is null
or b.c is null
or (isnull(a.c,0) - isnull(b.c,0)) != 0;
select
[colName]
,[colVal_real]
,[colVal_mine]
,[real]
,[mine]
,[diff]
from
@diffs;
';

DECLARE FireHose CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
select
colName
from
@cols;
open FireHose;
fetch next from FireHose
into @colName;
while @@FETCH_STATUS = 0
begin
set @SQLcmd = replace(replace(replace(replace(@cmd,'@colName@',@colname),'@tbl1@',@tbl1),'@tbl2@',@tbl2),'@joinType@',@joinType);
--exec sys.sp_executesql @SQLCmd, @SQLparms, @colName = @colName, @LoadId = @LoadId;
print @SQLCmd;
fetch next from FireHose
into @colName;
end
close FireHose;
deallocate FireHose;
After you look at the code, I will expect there to be some questions. This isn't just a straight forward piece of dynamic SQL.
Not meaning to nit-pick, but doesn't this miss the original question and just re-write the original SQL? The SQL is nicer and easier to read/follow and I believe from reading it should be more efficient, but doesn't it have the same net effect?
I think you'd need to add in a
JOIN Water_Dupe ON @tbl2.Ident = Water_Dupe.Ident AND Water_Dupe.Dupe = 'Y'
no?
I did add that.

HAHAHA.... Yes you did. I am blind. I haven't finished my coffee yet... that's my excuse.

Sorry about that. Yes, your code (to me anyways) looks like it meets all of the original requirements.
This'll teach me to re-read the code multiple times before commenting on it. And I think I'll add you to the list of "people I shouldn't question as they know a lot more than me".

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Is there any good reason why this exercise has to be more complicated than
SELECT <column_list> FROM a WHERE...
EXCEPT
SELECT <column_list> FROM b WHERE...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 10

Oh, Lynn... Your code does have that join/where that I mentioned, but you do it on tbl1 which is Thin_water. The original was on tbl2 which is Water_Derivation.

I read through it a few times and I am pretty sure that is the case? But if I am mistaken, I apologize.

And Chris, I think the reason they are not doing that is because the table Water_Dupe contains a column indicating if it is a duplicate or not. But without seeing the actual data, it is hard to know if an EXCEPT would work or if there is weird data between the tables that wouldn't allow that.

If you wanted to use the current query, I also do not see any reason why you need the @diff table variable either. I'm sure there are multiple ways to simplify this query, but without knowing the data and expected output, it is hard to know what the "best" query optimization would be, no? The above loop (either with a while or a cursor) both dump out multiple tables as soon as you have more than 1 column in the @cols table, which is what I expect the OP was trying to avoid by using that table variable, but I am not sure. If that is the case, it would be better to pull the table variable out of the loop and put the final select that is inside the loop, outside of the loop.
But it depends on the data, the expected result and the requirements of this problem.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

bmg002 - Wednesday, March 15, 2017 10:18 AM
Oh, Lynn... Your code does have that join/where that I mentioned, but you do it on tbl1 which is Thin_water. The original was on tbl2 which is Water_Derivation.
I read through it a few times and I am pretty sure that is the case? But if I am mistaken, I apologize.
And Chris, I think the reason they are not doing that is because the table Water_Dupe contains a column indicating if it is a duplicate or not. But without seeing the actual data, it is hard to know if an EXCEPT would work or if there is weird data between the tables that wouldn't allow that.
If you wanted to use the current query, I also do not see any reason why you need the @diff table variable either. I'm sure there are multiple ways to simplify this query, but without knowing the data and expected output, it is hard to know what the "best" query optimization would be, no? The above loop (either with a while or a cursor) both dump out multiple tables as soon as you have more than 1 column in the @cols table, which is what I expect the OP was trying to avoid by using that table variable, but I am not sure. If that is the case, it would be better to pull the table variable out of the loop and put the final select that is inside the loop, outside of the loop.
But it depends on the data, the expected result and the requirements of this problem.

That is one of the problems with working with Dynamic SQL, it is easy to miss what is being done especially when it is someone else's code you are working with. I am looking at a modification to the code now.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Lynn Pettis - Wednesday, March 15, 2017 10:24 AM
bmg002 - Wednesday, March 15, 2017 10:18 AM
Oh, Lynn... Your code does have that join/where that I mentioned, but you do it on tbl1 which is Thin_water. The original was on tbl2 which is Water_Derivation.
I read through it a few times and I am pretty sure that is the case? But if I am mistaken, I apologize.
And Chris, I think the reason they are not doing that is because the table Water_Dupe contains a column indicating if it is a duplicate or not. But without seeing the actual data, it is hard to know if an EXCEPT would work or if there is weird data between the tables that wouldn't allow that.
If you wanted to use the current query, I also do not see any reason why you need the @diff table variable either. I'm sure there are multiple ways to simplify this query, but without knowing the data and expected output, it is hard to know what the "best" query optimization would be, no? The above loop (either with a while or a cursor) both dump out multiple tables as soon as you have more than 1 column in the @cols table, which is what I expect the OP was trying to avoid by using that table variable, but I am not sure. If that is the case, it would be better to pull the table variable out of the loop and put the final select that is inside the loop, outside of the loop.
But it depends on the data, the expected result and the requirements of this problem.
That is one of the problems with working with Dynamic SQL, it is easy to miss what is being done especially when it is someone else's code you are working with. I am looking at a modification to the code now.

Here is my update if anyone wants to take a look:

declare @cols table (id int identity(1,1),colname varchar(255)) insert into @cols (colname) values ('Der_Postcode_Sector')

declare @tbl1 varchar(255) = 'Thin_water', @tbl2 varchar(255) = 'Water_Derivation', @joinType varchar(10)='right', @LoadId int = 50, @rc int = 1, @mr int = (Select count(id) from @cols), @colName varchar(255), @cmd nvarchar(max), @SQLcmd nvarchar(max), @SQLparms nvarchar(max) = '@colName varchar(255), @LoadId int'; set @cmd = N' declare @diffs table ( [id] [int] identity(1,1), [colName] [varchar](255), [colVal_real] [varchar](255), [colVal_mine] [varchar](255), [real] [int], [mine] [int], [diff] [int] );

insert into @diffs ( [colName] ,[colVal_real] ,[colVal_mine] ,[real] ,[mine] ,[diff] ) select @colName as colName ,a.colVal ,b.colVal ,isnull(a.c,0) as real ,isnull(b.c,0) as mine ,isnull(a.c,0) - isnull(b.c,0) as diff from ( select isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal, count(*) as c from @tbl1@ group by @colName@ ) a @joinType@ join ( select isnull(convert(varchar(255),@colName@),''_<null>_'') as colVal ,count(*) as c from @tbl2@ WDER inner join Water_Dupe AS WD --(Duplicate data table) on WDER.ident = WD.Ident where WDER.Loadid = @LoadId and WD.Dupe = ''y'' group by @colName@ ) b on a.colVal = b.colVal where a.c is null or b.c is null or (isnull(a.c,0) - isnull(b.c,0)) != 0;

select [colName] ,[colVal_real] ,[colVal_mine] ,[real] ,[mine] ,[diff] from @diffs; '; DECLARE FireHose CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR select colName from @cols;

open FireHose;

fetch next from FireHose into @colName;

while @@FETCH_STATUS = 0 begin set @SQLcmd = replace(replace(replace(replace(@cmd,'@colName@',@colname),'@tbl1@',@tbl1),'@tbl2@',@tbl2),'@joinType@',@joinType); --exec sys.sp_executesql @SQLCmd, @SQLparms, @colName = @colName, @LoadId = @LoadId; print @SQLCmd;

fetch next from FireHose into @colName; end

close FireHose;

deallocate FireHose;

Edit: Weird how some of the code duplicated. I don't see that in UE where I copied the code.

Edit: I think I have cleaned up all the code. Don't understand how pieces got duped when I added the IF Code for SQL.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

skandan1976 - Tuesday, March 14, 2017 2:58 PM
I am trying to compare two tables columns to make sure both are matching. I have done the basic row counts which is matching now I would like to compare the column level matching.
Data base Name - Thin PRC First table - Thin_Water -

If you are only dealing with two tables then you don't need dynamic SQL. If you are dealing with multiple pairs of tables, then develop your solution in ordinary TSQL first, then convert to dynamic SQL. Much easier.
Please can you provide CREATE TABLE statements for your two tables, and INSERT statements to populate them with a representative sample of data. Finally, similar statements to create the EXACT output you would expect to see from your sample data. Why keep people guessing when you are asking them for help?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

ChrisM@Work - Wednesday, March 15, 2017 10:44 AM
skandan1976 - Tuesday, March 14, 2017 2:58 PM
I am trying to compare two tables columns to make sure both are matching. I have done the basic row counts which is matching now I would like to compare the column level matching.
Data base Name - Thin PRC First table - Thin_Water -
If you are only dealing with two tables then you don't need dynamic SQL. If you are dealing with multiple pairs of tables, then develop your solution in ordinary TSQL first, then convert to dynamic SQL. Much easier.
Please can you provide CREATE TABLE statements for your two tables, and INSERT statements to populate them with a representative sample of data. Finally, similar statements to create the EXACT output you would expect to see from your sample data. Why keep people guessing when you are asking them for help?

I agree Chris, but simply based on the original post, it is possible that the table names may change dynamically. If so, then dynamic SQL may be needed. Unfortunately we don't have the full requirements. It also looks like the JOIN type my be dynamically chosen as well.

Dynamic Sql Query

Select count(*) from Thin_Water = Answer 10

so this counts matches the Thin_Water = answer 10

Below query works but brings unmatched records, because I have not eliminated the duplicates and currnet load id. I need to include those tasks in below script. Any help will be appreciated