February 25, 2013 at 4:06 am
Hi,
Say I have the following
A list of codes in a temp table
‘A’
‘B’
‘P’
‘V’
‘X’
‘Y’
‘Z’
And I have to do for example
SELECT * INTO #Example_Table_A from main_table Where Code Like ‘A’
SELECT * INTO #Example_Table_B from main_table Where Code Like ‘B’
And repeat till you run out of codes. I have forgotten how to do this using a while loop. If any one could remind me how to do this using a while loop, that would be excellent.
Debbie
February 25, 2013 at 4:32 am
You can copy the result of the below and run in a new sql session:
with cte_MyCodes(Code)
as (select 'A' union ALL select 'B' union all select 'P' union all select 'V' union all select 'X' union all select 'Y' union all select 'Z')
select 'SELECT * INTO #Example_Table_' + Code + ' from main_table Where Code Like ''' + Code + ''''
from cte_MyCodes
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 25, 2013 at 4:33 am
To create the number of tables dynamically you will need to use dynamic sql (using sp_executesql or EXEC ()). However, it will not work for you, as temp tables created this way will be persisted only during execution of the dynamic sql which created them.
So, you have two choices: all code relevant to the created temp table should be part of the same dynamic sql or you will need to use permanent tables.
I would go with the second option. Just create dedicated "work area" scheme and create create all tables there.
February 25, 2013 at 4:35 am
Eugene Elutin (2/25/2013)
To create the number of tables dynamically you will need to use dynamic sql (using sp_executesql or EXEC ()). However, it will not work for you, as temp tables created this way will be persisted only during execution of the dynamic sql which created them.So, you have two choices: all code relevant to the created temp table should be part of the same dynamic sql or you will need to use permanent tables.
I would go with the second option. Just create dedicated "work area" scheme and create create all tables there.
I'm really sorry I don't understand this at all.
February 25, 2013 at 4:38 am
Abu Dina (2/25/2013)
You can copy the result of the below and run in a new sql session:
with cte_MyCodes(Code)as (select 'A' union ALL select 'B' union all select 'P' union all select 'V' union all select 'X' union all select 'Y' union all select 'Z')
select 'SELECT * INTO #Example_Table_' + Code + ' from main_table Where Code Like ''' + Code + ''''
from cte_MyCodes
The problem with this is it still involves copying the results and pasting. Im wanting it to run within a stored procedure without me having to do anything.
Also the SELECT WHERE queries are fairly big which is why im wanting to do it where Im only having to create the SQL Once.
At the moment I have a query for every code so the script is pretty long winded and I dont think it has to be
February 25, 2013 at 4:39 am
question: do you want to generate just a code, so you can use it somewhere else or you want it to be a part of some procedure, so temp tables will be created dynamically and then used inside of the same procedure?
If the first, than you can use advice from first reply to your thread...
February 25, 2013 at 4:44 am
OK,
I have a temp table of codes (Specified above)
Then I have a query that uses the code as critera so I am going to have another temp table for each code. These temp tables have other columns in there.
So School, Pupil, MAx_Date, Total
WHERE Code = 'A'
etc etc etc.
In stead of having to have lots of SQL Scripts for each code. I'm hoping to have just one using a loop. I can set the Into temp table using the variable within the Table name for each one I do.
The first script doesn't work in the way I want it to because, I could use it to create the tables all in one go and then paste them all out into my script, but I just want one within a loop.
February 25, 2013 at 4:51 am
Quick and dirty method 😛
create table #SSC(ID INT IDENTITY (1, 1), Code CHAR(1))
insert into #SSC
select 'A' union ALL select 'B' union all select 'P' union all select 'V' union all select 'X' union all select 'Y' union all select 'Z'
DECLARE @SQL VARCHAR(MAX)
DECLARE @looper int = 1
WHILE @looper <= (SELECT MAX(ID) from #SSC)
BEGIN
SET @SQL = ''
SELECT @SQL = 'SELECT * INTO #Example_Table_' + Code + ' from main_table Where Code Like ''' + Code + ''''
FROM #SSC WHERE ID = @looper
PRINT(@SQL)
SET @looper = @looper + 1
END
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 25, 2013 at 4:53 am
Thats looking pretty good already just by looking at it!
Ill set it up and let you know how it goes 🙂
February 25, 2013 at 4:54 am
Sweet, don't forget to change the PRINT to EXEC
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 25, 2013 at 4:56 am
Debbie Edwards (2/25/2013)
OK,I have a temp table of codes (Specified above)
Then I have a query that uses the code as critera so I am going to have another temp table for each code. These temp tables have other columns in there.
So School, Pupil, MAx_Date, Total
WHERE Code = 'A'
etc etc etc.
In stead of having to have lots of SQL Scripts for each code. I'm hoping to have just one using a loop. I can set the Into temp table using the variable within the Table name for each one I do.
The first script doesn't work in the way I want it to because, I could use it to create the tables all in one go and then paste them all out into my script, but I just want one within a loop.
-- I get the following messages
SELECT * INTO #Example_Table_A from main_table Where Code Like 'A'
SELECT * INTO #Example_Table_B from main_table Where Code Like 'B'
SELECT * INTO #Example_Table_P from main_table Where Code Like 'P'
SELECT * INTO #Example_Table_V from main_table Where Code Like 'V'
SELECT * INTO #Example_Table_X from main_table Where Code Like 'X'
SELECT * INTO #Example_Table_Y from main_table Where Code Like 'Y'
SELECT * INTO #Example_Table_Z from main_table Where Code Like 'Z'
--But I cant query the table
Invalid object name '#Example_Table_A'.
Im guessing Im going to have to go for adding them into a permanent table rather than a temp if IDENTITY go this way?
February 25, 2013 at 4:58 am
Id forgotten to do that bit.....
:blush:
Right.... Im going in to see if I can add this to what I already have. Thank you X100
February 25, 2013 at 5:00 am
No worries... have fun! 😀
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 25, 2013 at 5:19 am
Abu Dina (2/25/2013)
Quick and dirty method 😛
create table #SSC(ID INT IDENTITY (1, 1), Code CHAR(1))insert into #SSC
select 'A' union ALL select 'B' union all select 'P' union all select 'V' union all select 'X' union all select 'Y' union all select 'Z'
DECLARE @SQL VARCHAR(MAX)
DECLARE @looper int = 1
WHILE @looper <= (SELECT MAX(ID) from #SSC)
BEGIN
SET @SQL = ''
SELECT @SQL = 'SELECT * INTO #Example_Table_' + Code + ' from main_table Where Code Like ''' + Code + ''''
FROM #SSC WHERE ID = @looper
PRINT(@SQL)
SET @looper = @looper + 1
END
The above code as it is, is equivalent of :
-- do nothing or pause for few seconds
WAITFOR DELAY '00:00:03'
"#Example_Table_' + Code" table will be created will exist only during execution of the @SQL.
Any code after EXEC (@SQL) will not be able to see and use it - you wil get run-time error:
"Invalid object name '#Example_Table_[A]'".
So, the dynamic sql which creates the temp table should use it. Otherwise this is meaningless code.
February 25, 2013 at 5:22 am
Okay fair enough then just make the tempt table into a global temp table and problem is sorted.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply