Click here to Skip to main content
14,665,452 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am trying to create to Temp Table in SQL where table name and column name are diff like:


DECLARE @Index1     INT,@Sql VARCHAR(max);
SET @Index1 = 1;

    WHILE @Index1 <= 5
    BEGIN

        DECLARE @tab VARCHAR(max);
        SELECT @tab = 'Tab' + CAST(@Index1 AS VARCHAR)

        DECLARE @col0 VARCHAR(50);
        SELECT @col0 = 'aa' + CAST(@Index1 AS VARCHAR)

        DECLARE @col1 VARCHAR(50);
        SELECT @col1 = 'bb' + CAST(@Index1 AS VARCHAR)

        DECLARE @col2 VARCHAR(50);
        SELECT @col2 = 'cc' + CAST(@Index1 AS VARCHAR)

        DECLARE @col3 VARCHAR(50);
        SELECT @col3 = 'dd' + CAST(@Index1 AS VARCHAR)

        SET @Sql = '
        CREATE TABLE ' + @tab + '
        (
            ' + @col0 + '   CHAR(2),
            ' + @col1 + '   VARCHAR(50),
            ' + @col2 + '   DATETIME,
            ' + @col3 + '   INT,
        )'

        EXEC(@Sql)

        SET @Index1 = @Index1 + 1;

    END


    select * from Tab1
    select * from Tab2
    select * from Tab3
    select * from Tab4
    select * from Tab5


    drop table Tab1
    drop table Tab2
    drop table Tab3
    drop table Tab4
    drop table Tab5

It is creating physical table. If, i am putting # sign then facing error like ‘Invalid Object’ that means unable to create Temp Table. :(
Posted

Rate this:
Please Sign up or sign in to vote.

Solution 1

2 notes:
1) code context[^] has been missed
2) check for existing table[^] has been missed

Ad 1)
On the top of your code, add this line:
USE DatabaseName;


Ad 2)
IF OBJECT_ID('#TableName') IS NOT NULL DROP TABLE #TableName
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

The problem here is the scope of the session. When we execute dynamic sql via EXEC or sp_executesql a new scope is created for a child session. Any objects created in that session are dropped as soon as the session is closed.

what you can do is put both create and select statement is single SQL string and execute that one.

http://stackoverflow.com/questions/8040105/execute-sp-executesql-for-select-into-table-but-cant-select-out-temp-table-da[^]
http://blog.shkedy.com/2007/01/temporary-tables-and-dynamic-sql.html[^]
   
v3
Comments
chetna2810 2-May-14 2:15am
   
thnx Sir .. :)
Rate this:
Please Sign up or sign in to vote.

Solution 3

Solution 2 is Right,So You can Use Global Temporary Table

DECLARE @Index1     INT,@Sql VARCHAR(max);
SET @Index1 = 1;
 
    WHILE @Index1 <= 5
    BEGIN
 
        DECLARE @tab VARCHAR(max);
        SELECT @tab = '##Tab' + CAST(@Index1 AS VARCHAR)
 
        DECLARE @col0 VARCHAR(50);
        SELECT @col0 = 'aa' + CAST(@Index1 AS VARCHAR)
 
        DECLARE @col1 VARCHAR(50);
        SELECT @col1 = 'bb' + CAST(@Index1 AS VARCHAR)
 
        DECLARE @col2 VARCHAR(50);
        SELECT @col2 = 'cc' + CAST(@Index1 AS VARCHAR)
 
        DECLARE @col3 VARCHAR(50);
        SELECT @col3 = 'dd' + CAST(@Index1 AS VARCHAR)
 
        SET @Sql = '
        CREATE TABLE ' + @tab + '
        (
            ' + @col0 + '   CHAR(2),
            ' + @col1 + '   VARCHAR(50),
            ' + @col2 + '   DATETIME,
            ' + @col3 + '   INT,
        )'
 
        EXEC(@Sql)
 
        SET @Index1 = @Index1 + 1;
 
    END
 

    select * from ##Tab1
    select * from ##Tab2
    select * from ##Tab3
    select * from ##Tab4
    select * from ##Tab5


Edited:
Changing Format
   
v2
Comments
chetna2810 2-May-14 2:16am
   
thnx Sir..
I got the Error and Solution :)
King Fisher 2-May-14 2:19am
   
;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100