Limiting the scope of a Table variable
-
Hi All, I few SQL scripts and in them I am using Table variables to store values from select queries then loop through that table variable to avoid duplicate entries in the tables. Like
Declare @TabA Table(Id identity, Name varchar, Description varchar)
Insert into @TabA SELECT Name, Description FROM XXXXLoop through Table Variable using Id and check if that Name already doesn't exist in the TabB then insert else don't.
Now I have interesting question, I am using many inserts in different blocks within same sql script. When I try to declare same Table Variable name again @TabA, it says, it already exists. I don't want to use Drop statement in my script, 1. is there any way that I can make the table variable drop automatically within same script, otherwise do I need to declare different table variable for each insert, if that's the case then wouldn't it be more stress on the RAM as many table variables would exist in the RAM until that whole script runs? 2. I am afraid to use delete on the same Table variable to use it in next insert, For example, if I have to insert into TablC table which has same set of Columns Name and Description, how does Delete works on the table variable would it create Ids from where it left in the before insert or would it create Ids from 1st again? 3 And another question is, if we are running multiple sql files at the same time, do we need to use different table variable names in all of those sql files or the scope of the Table Variable dies soon after each sql file runs? Please answer these questions any suggestions, links and even code snippets would help me a lot, thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi All, I few SQL scripts and in them I am using Table variables to store values from select queries then loop through that table variable to avoid duplicate entries in the tables. Like
Declare @TabA Table(Id identity, Name varchar, Description varchar)
Insert into @TabA SELECT Name, Description FROM XXXXLoop through Table Variable using Id and check if that Name already doesn't exist in the TabB then insert else don't.
Now I have interesting question, I am using many inserts in different blocks within same sql script. When I try to declare same Table Variable name again @TabA, it says, it already exists. I don't want to use Drop statement in my script, 1. is there any way that I can make the table variable drop automatically within same script, otherwise do I need to declare different table variable for each insert, if that's the case then wouldn't it be more stress on the RAM as many table variables would exist in the RAM until that whole script runs? 2. I am afraid to use delete on the same Table variable to use it in next insert, For example, if I have to insert into TablC table which has same set of Columns Name and Description, how does Delete works on the table variable would it create Ids from where it left in the before insert or would it create Ids from 1st again? 3 And another question is, if we are running multiple sql files at the same time, do we need to use different table variable names in all of those sql files or the scope of the Table Variable dies soon after each sql file runs? Please answer these questions any suggestions, links and even code snippets would help me a lot, thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
1. Use a different name for each table variable or delete the content and reuse the existing variable 2. Why are you scared to use Delete! 3. Table variables are limited in scope to the current procedure. Performance is unlikely to be an issue unless you are processing serious volumes.
Never underestimate the power of human stupidity RAH
-
1. Use a different name for each table variable or delete the content and reuse the existing variable 2. Why are you scared to use Delete! 3. Table variables are limited in scope to the current procedure. Performance is unlikely to be an issue unless you are processing serious volumes.
Never underestimate the power of human stupidity RAH