Using table just after creating it: object does not exist

Question

I have a script in T-SQL that goes like this:

create table TableName (...)
SET IDENTITY INSERT TableName ON

And on second line I get error:

Cannot find the object "TableName" because it does not exist or you do not have permissions.

I execute it from Management Studio 2005. When I put "GO" between these two lines, it's working. But what I would like to acomplish is not to use "GO" because I would like to place this code in my application when it will be finished.

So my question is how to make this work without using "GO" so that I can run it programmatically from my C# application.

Answer 1

Without using GO, programmatically, you would need to make 2 separate database calls.

Answer 2

Run the two scripts one after the other - using two calls from your application.

You should only run the second once the first has successfully run anyway, so you could run the first script and on success run the second script. The table has to have been created before you can use it, which is why you need the GO in management studio.

Answer 3

Consider writing a stored proc that creates a temporary table and does whatever it needs to with that. If you create a real table, your app won't be able to run the script more than once, unless it also drops the table -- in which case, you have exactly the functionality of a temp table.

Answer 4

From the BOL: "SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server". Therefore, as Jose Basilio already pointed out, you have to make separate database calls. If this can help, I was faced with the same problem and I had to write a little (very basic) parser to split every single script in a bunch of mini-script which are sent - one at a time - to the database.

Answer 5

something even better than tpdi's temp table is a variable table. they run lightning fast and are dropped automatically once out of scope.

this is how you make one

declare @TableName table (ColumnName int, ColumnName2 nvarchar(50))

then to insert you just do this

insert into @TableName (ColumnName, ColumnName2)
select 1, 'A'