Dynamic DDLs
-
Hi,
Code snippet:
DECLARE @COUNT INT
DECLARE @VAR NVARCHAR(200)
BEGIN
SET @COUNT = 1
WHILE @COUNT <= 3
BEGIN
SET @VAR = ('CREATE TABLE #INT_RSLT_'+CAST(@COUNT AS VARCHAR(5))+' '+'( ROW_ID INT IDENTITY(1,1), TBL_UID_'+CAST(@COUNT AS VARCHAR(5))+' '+'BIGINT );')
EXEC sp_executeSQL @VAR
SET @COUNT = @COUNT + 1
END
But I would also require to access the tables that I just created in the loop. So is there a way I can do SELECT * FROM #INT_RSLT_1 outside the loop ???
-
ok it looks like you want to keep track of multiple results, but you wanted separate tables...
rethink the logic a bit;
why not create ONE table, and add one column CalledResultSet, and update that table with the @COUNT instead?
CREATE TABLE #INT_RSLT(
ROW_ID INT IDENTITY(1,1),
TBL_UID BIGINT,
ResultSet INT);
i think it might be easier, and then you don't have dynamic DDL. then your code can do whatever it was doing, but just add the int value from the resultset
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yeah you got it right, BUT the multiple results will come from different tables. It is something like I have a table (say table_1) that loads all the parent tables of a particular table say 'MyTable'.
And table_1 is having many _UIDs from different/unrelated tables that MyTable references. So I wanted to create separate tables for separate _UIDs, so that later when I populate that particular _UID in MyTable, I can do it from the exclusive table I just created for storing that _UID.
So I guess I can't create one single table right from start because I dunno how many UIDs will be there.
I wanna basically create some undetermined number of tables, and then later put some values in each of them.
Show me the way!
Thanks,
Sayan
-
If you want to do it this way, you must not use temp tables. They exist only in scope they were created in, in this case within context of your dynamic SQL. If you are sure that you will not run into concurency problems, you may create (with dynamic sql) permanent tables and drop them after the whole process is finished.
...and your only reply is slàinte mhath
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply