Want to avoid Dynamic Sql even if the Table name is random
-
Hi, I want to avoid dynamic sql but my Table name varies for different run, is there any better way to avoid the dynamic sql in this situation? I am trying my best, searching, asking and implementing different approaches etc, if anybody can give me some suggestion, link or even code snippet that would very helpful. Hi, I have a table (Table123) that has Columns ColA, ColB, ColC, ColD, ColE, ColF, ColG, I have many rows entered in the same table but want to check for two rows RowA, RowB, if for example I have three columns that are different in these two rows, I want to select those Column Names, like if ColA, ColC and ColE are changed then my query should return me as below TableName, ChangedColumnName, OldValue, NewValue Table123 , ColA, 123 Greetings 123 New Greetings Table123 , ColC, 123 World 123 Hello World Table123 , ColE, 123 Test World 123 New World etc. Is there anyway I can do it, but without using dynamic sql any help would be greatly helpful, thanks in advance. Thanks in advance Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi, I want to avoid dynamic sql but my Table name varies for different run, is there any better way to avoid the dynamic sql in this situation? I am trying my best, searching, asking and implementing different approaches etc, if anybody can give me some suggestion, link or even code snippet that would very helpful. Hi, I have a table (Table123) that has Columns ColA, ColB, ColC, ColD, ColE, ColF, ColG, I have many rows entered in the same table but want to check for two rows RowA, RowB, if for example I have three columns that are different in these two rows, I want to select those Column Names, like if ColA, ColC and ColE are changed then my query should return me as below TableName, ChangedColumnName, OldValue, NewValue Table123 , ColA, 123 Greetings 123 New Greetings Table123 , ColC, 123 World 123 Hello World Table123 , ColE, 123 Test World 123 New World etc. Is there anyway I can do it, but without using dynamic sql any help would be greatly helpful, thanks in advance. Thanks in advance Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
If you have only a few possibilities for the tables and they are known beforehand then you could do a simple case structure to decide the query. But before you jump into solutions, few questions you should consider: - Why do you want to avoid dynamic SQL? When done properly, it has it's place. - Is the table name the only thing that varies in the queries. If it is, I would suggest reconsidering the structure of the database, because in such situation it sounds that the different tables could be replaced with a single table with proper categorizing fields.
-
If you have only a few possibilities for the tables and they are known beforehand then you could do a simple case structure to decide the query. But before you jump into solutions, few questions you should consider: - Why do you want to avoid dynamic SQL? When done properly, it has it's place. - Is the table name the only thing that varies in the queries. If it is, I would suggest reconsidering the structure of the database, because in such situation it sounds that the different tables could be replaced with a single table with proper categorizing fields.
1. I saw Dynamic Sql is taking lot of time for execution, even if I use it with most possible ways when Data becomes little bit, the difference in execution time is too huge. 2. No I saw now that I have use Columns as well randomly - is there any way like compare with sysColumns and then get its type then join with it etc? Anything works for me without using dynamic sql. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
If you have only a few possibilities for the tables and they are known beforehand then you could do a simple case structure to decide the query. But before you jump into solutions, few questions you should consider: - Why do you want to avoid dynamic SQL? When done properly, it has it's place. - Is the table name the only thing that varies in the queries. If it is, I would suggest reconsidering the structure of the database, because in such situation it sounds that the different tables could be replaced with a single table with proper categorizing fields.
Hi, I have a table (Table123) that has Columns ColA, ColB, ColC, ColD, ColE, ColF, ColG, I have many rows entered in the same table but want to check for two rows RowA, RowB, if for example I have three columns that are different in these two rows, I want to select those Column Names, like if ColA, ColC and ColE are changed then my query should return me as below TableName, ChangedColumnName, OldValue, NewValue Table123 , ColA, 123 Greetings 123 New Greetings Table123 , ColC, 123 World 123 Hello World Table123 , ColE, 123 Test World 123 New World etc. Is there anyway I can do it, but without using dynamic sql any help would be greatly helpful, thanks in advance. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi, I have a table (Table123) that has Columns ColA, ColB, ColC, ColD, ColE, ColF, ColG, I have many rows entered in the same table but want to check for two rows RowA, RowB, if for example I have three columns that are different in these two rows, I want to select those Column Names, like if ColA, ColC and ColE are changed then my query should return me as below TableName, ChangedColumnName, OldValue, NewValue Table123 , ColA, 123 Greetings 123 New Greetings Table123 , ColC, 123 World 123 Hello World Table123 , ColE, 123 Test World 123 New World etc. Is there anyway I can do it, but without using dynamic sql any help would be greatly helpful, thanks in advance. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
You lost me with the "three columns that are different in these two rows" - how can you have different columns in specific rows of a single table? You've also mentioned that your dynamic SQL is taking a long time to run - I don't believe that is because it is dynamic SQL. It's more likely to be the way you have constructed the actual query. If you share the code you use to build the dynamic query it might help me to understand what you are trying to achieve (and we might also be able to solve your performance problem)
-
Hi, I have a table (Table123) that has Columns ColA, ColB, ColC, ColD, ColE, ColF, ColG, I have many rows entered in the same table but want to check for two rows RowA, RowB, if for example I have three columns that are different in these two rows, I want to select those Column Names, like if ColA, ColC and ColE are changed then my query should return me as below TableName, ChangedColumnName, OldValue, NewValue Table123 , ColA, 123 Greetings 123 New Greetings Table123 , ColC, 123 World 123 Hello World Table123 , ColE, 123 Test World 123 New World etc. Is there anyway I can do it, but without using dynamic sql any help would be greatly helpful, thanks in advance. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
Chilli is right, dynamic sql is unlikely to be your problem, query design is almost certainly the cause of slow processing. You need to create a temp table that hold all the field from the various sources and feed the records into that table to make the structure the same.
Never underestimate the power of human stupidity RAH
-
Chilli is right, dynamic sql is unlikely to be your problem, query design is almost certainly the cause of slow processing. You need to create a temp table that hold all the field from the various sources and feed the records into that table to make the structure the same.
Never underestimate the power of human stupidity RAH
Yeah Could be but most of the times I used Dynamic Sql if the rows are in like 10000s or 100000s then performance of the Stored Procedure decreased even after enabling and disabling the Query plans in both situations it decreased heavily, but I was able to bring that up 10 or 20 minutes, which is not bad for ETLs. But then the Politics involve and make us inferior just for that reason. That's what happens most of the times. But yes normal queries I ran so far run within couple of minutes but the dynamic sql took 10s or 20s of minutes and I could not able to minimize it more than that. Any help would be greatly helpful - thanks for all help my friends. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Yeah Could be but most of the times I used Dynamic Sql if the rows are in like 10000s or 100000s then performance of the Stored Procedure decreased even after enabling and disabling the Query plans in both situations it decreased heavily, but I was able to bring that up 10 or 20 minutes, which is not bad for ETLs. But then the Politics involve and make us inferior just for that reason. That's what happens most of the times. But yes normal queries I ran so far run within couple of minutes but the dynamic sql took 10s or 20s of minutes and I could not able to minimize it more than that. Any help would be greatly helpful - thanks for all help my friends. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
Have you taken the script of the dynamic query and run a query profile on it to see if there are indexes that can be tuned.
Never underestimate the power of human stupidity RAH
-
Yeah Could be but most of the times I used Dynamic Sql if the rows are in like 10000s or 100000s then performance of the Stored Procedure decreased even after enabling and disabling the Query plans in both situations it decreased heavily, but I was able to bring that up 10 or 20 minutes, which is not bad for ETLs. But then the Politics involve and make us inferior just for that reason. That's what happens most of the times. But yes normal queries I ran so far run within couple of minutes but the dynamic sql took 10s or 20s of minutes and I could not able to minimize it more than that. Any help would be greatly helpful - thanks for all help my friends. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
