Could not select additional column names if dropped temporary table is created again with additional column names

Question

Though I have dropped the temporary table, it does not let me select the new column names if the temporary table with same table name with additional column names will be created.

I have been using SQL Server 2008.

Try this code for instance:

IF (object_id('tempdb..#xyz') IS NOT NULL)
    DROP TABLE #xyz;

CREATE TABLE #xyz(
     [a] [datetime] NULL,
     [b] [nvarchar](255) NULL,
     [c] [nvarchar](255) NULL
) ON [PRIMARY]
GO

IF (object_id('tempdb..#xyz') IS NOT NULL)
    DROP TABLE #xyz;

CREATE TABLE #xyz(
     [a] [datetime] NULL,
     [b] [nvarchar](255) NULL,
     [c] [nvarchar](255) NULL,
     [d] [nvarchar](255) NULL
) ON [PRIMARY]

SELECT [d] FROM  #xyz

This says:

Msg 207, Level 16, State 1, Line 13
Invalid column name 'd'.

Again, if we try like selecting * as mentioned by Martin Parkin, it gives result with column name d.

I could not select * for my dynamic columns and it doesn't let me to select additional columns.

Why?

Fiddle

Are you getting an error message, or you determining it's not allowing you to create the table by some other means? If you receive an actual SQL Server error message, that sometimes gives a good indication of the actual problem. — spencer7593, Feb 26 '14 at 22:15
You probably need to separate your queries with `GO`: put a `GO` after each `DROP TABLE` (outside the `if` though). — Blorgbeard, Feb 26 '14 at 22:15
Also, you are not actually creating a temp table in the usual sense. Temp tables are stored in tempdb, and their names start with `#`. You are creating a real table, but using it as a temp table. — Blorgbeard, Feb 26 '14 at 22:18
Put a GO between them. The whole chunk of SQL is being compiled there at once, and the table definition for `temp_process.dbo.xyx` is being inferred based on the first select into. The compilation is very basic and not advanced enough to notice that you drop and recreate the table. Using GO separates compilation units. — John Gibb, Feb 26 '14 at 22:20
Those tables are for temporary purpose, not exactly temporary tables. — hsuk, Feb 26 '14 at 22:20
I have run this code (replacing the source tables with valid ones from my own databases) and am unable to reproduce your error. Adding a `GO` statement will not make a difference as I did not need one in my code for it to work. — Martin, Feb 26 '14 at 22:27
@MartinParkin : Will you please try to execute those chunks of code separately. — hsuk, Feb 26 '14 at 22:28
Also, when you use `Person.Person` and `Person.address`, what tables are these referring to - a database named `Person` with a table named `Person` and `address` in it, or a schema named `Person` with the same?` — Martin, Feb 26 '14 at 22:29
@hsuk I have executed them individually as 3 (first 2, second 2 and `select`) and 5 separate statements - it all works without issue and without adding `GO` anywhere. — Martin, Feb 26 '14 at 22:31
Person represents schema name, its not DB name, I have been using Adverntureworks DB. — hsuk, Feb 26 '14 at 22:32
@hsuk I have just downloaded AdventureWorks 2008R2, created a new `temp_process` database and copied and run your code without any changes. It works for me without any error. — Martin, Feb 26 '14 at 22:38
@hsuk How can the error in your screenshot be occurring on line 9, when in your pasted code the `SELECT` statement is on 18? — Martin, Feb 26 '14 at 22:45
@hsuk I can now reproduce this using your updated code, thank you. — Martin, Feb 26 '14 at 22:53
Interestingly, if you `SELECT *...` instead of `d` it shows all four columns. — Martin, Feb 26 '14 at 22:55

Szymon · Answer 1 · 2014-02-26T22:52:54.743

2

You need GO to separate DROP and CREATE statements. They need to be in separate batches.

IF (object_id('tempdb..#xyz') IS NOT NULL)
DROP TABLE #xyz;

CREATE TABLE #xyz(
     [a] [datetime] NULL,
     [b] [nvarchar](255) NULL,
     [c] [nvarchar](255) NULL
) ON [PRIMARY]
GO

IF (object_id('tempdb..#xyz') IS NOT NULL)
DROP TABLE #xyz;

GO

CREATE TABLE #xyz(
     [a] [datetime] NULL,
     [b] [nvarchar](255) NULL,
     [c] [nvarchar](255) NULL,
     [d] [nvarchar](255) NULL
) ON [PRIMARY]

SELECT [d] FROM  #xyz

edited Feb 26 '14 at 22:52

answered Feb 26 '14 at 22:26

Szymon

42,577
16
96
114

@hsuk I updated my answer and tested it. But please don't change your question that much as it makes all the comments and possibly answers obsolete. – Szymon Feb 26 '14 at 22:53
Oh, I am sorry for that, I just had to change the question to replicate the problem over here. Previous example was working for everyone. Anyway, noted ... – hsuk Feb 26 '14 at 23:04

Could not select additional column names if dropped temporary table is created again with additional column names

1 Answers1