Dynamic Sql Query

Question

Dynamic Sql Query

Viewing 3 posts - 16 through 17 (of 17 total)

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

skandan1976 SSC Enthusiast Points: 101 More actions · Answer 1

Hi,
Thanks for all your valuable suggestions. The task was allocated to senior analysts now, but still I try to solve it. The task is another team developed the new process so that they have created a table in development environment which is ThinWater. ThinWater dont include any duplicates, as they developed they have done the dupe process. Now I need to compare the counts with that table with existing derivation table. Derivation table having loaid which helps as to know the current month and dupe flag, if I say dupe ='y' and loadid =max(loadid) then I expect the count total should match with ThinWater Table.
Unfortunately I am in position to create the data sample.

Kind regards
Tom

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

Lynn Pettis - Wednesday, March 15, 2017 10:48 AM
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.

Sorry mate, I didn't mean to tread on anyone's toes - but it seemed to me, rightly or wrongly, that there was too much background missing to formulate a sound solution. Moot now since OP's done a runner.

^{“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 3

ChrisM@Work - Thursday, March 16, 2017 2:51 AM
Lynn Pettis - Wednesday, March 15, 2017 10:48 AM
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.
Sorry mate, I didn't mean to tread on anyone's toes - but it seemed to me, rightly or wrongly, that there was too much background missing to formulate a sound solution. Moot now since OP's done a runner.

Nothing to apologize for, Chris. I actually agreed with you. You could say I am tired of ops bashing me for not answering their question that I try to answer their question then hope to learn more as they expand their requirements because the first answer didn't really answer their real question.