Create Table within an IF statement causes error

This doesn't make any sense to me. I am trying to create a stored procedure that creates a temp table using T-SQL. The table will be created differently depending on the arguments passed. Here is an example of what I am trying to do:

DECLARE @.Switch bit

SET @.Switch = 0

IF @.Switch = 0

BEGIN

PRINT @.Switch

CREATE TABLE #DontWork (Zero int)

END

ELSE

BEGIN

PRINT @.Switch

CREATE TABLE #DontWork (One int)

END

SELECT * FROM #DontWork

If you run this as is, it fails stating that "There is already an object named '#DontWork' in the database." However if you comment out one of the CREATE TABLE statements (either one of them), it works fine. The PRINT @.Switch line will prove that the IF ELSE statement is evaluating properly if you change the value of @.Switch. My guess is that the parsing engine is throwing the error before it even tries to run the code. Is there any way to make this work the way it should? Or do I have to resort to creating 2 different tables and modifying the rest of my code to compensate for the change?

This is usually caused because you have ran a CREATE TABLE statement in a previous development iteration. Try appending this to the end of your code:

Code Snippet

go

drop table #DontWork

The temp table stays in scope after you run through one time so the next time through you get the error. Try hiliting the code I've given you and execute just the DROP TABLE. Then un-hilite the code and rerun query. It should run correctly once you have dropped the table.

OK, I'm all wet... Hang on.

You can alter it:

Code Snippet

create table #what (one int)

alter table #what
add two int

alter table #what
drop column one

select * from #what

go

drop table #what

/*
two
--
*/

|||

The code doesn't execute.

The parsing engine is attempting to resolve the objects, and (incorrectly, in my opinion) assumes that the second instance of the create table is attempting to make a second object with the same name. The parsing engine is resolving objects, not checking logic and code flow.

To test, comment out EITHER CREATE statement and the code executes.

Your options include creating the #Temp table before the IF statement, or using a different #Table name in the second instance.

Or you could have both switched locations call out to another procedure that creates the #Temp table.

|||My code already involves altering the table. I was just trying to use an IF statement because one scenario creates a predictable table structure, and the other side requires that the field names be calculated at run time. I was trying to save myself some effort by simply having that CREATE TABLE command in there twice, but it seems that because of what Arnie said about the parsing engine resolving objects, not checking logic and code flow, I'm going to have to do things the complicated way. I wish there was a way to communicate things like this to the powers that be at Microsoft. Any idea how to do that, if at all possible?|||

They do pay attention to the suggestions.

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

|||MS SQL has always had a problem with this construct. The solution, as mentioned, is to create the table once, and then use alter table to change the table to what you want. Or just create 2 tables of different names.

|||

SQL Server compiles the entire batch (SP, trigger, function or ad-hoc) and compilation doesn't take into account run-time information (variable values, control of flow etc). This gets tricky for temporary tables because of the way they are scoped. For best performance and manageability, you should put the creation logic for the different conditions in their own SPs and the execution logic too. This provides better reusability. You could use the ALTER TABLE approach but that will give bad performance in SQL Server 2005 since it negates the caching that we do automatically on temporary tables (metadata & 1 page of allocation which can get reused). Of course, if you can remove the temporary tables altogether.

Btw, your code will work if you were creating a permanent table conditionally.

CREATE TABLE (starting at row ?)

Essentially what I want to do is...

Copy a table from my main SQL server database to Temp, starting at a particular row. Ex: Only include row 1,000 to 1,999 (end row.)

I've been using DTS Wizard to CREATE TABLE, and it's working fine, but I searched and searched in google and this forum and I can't find how to "start at particular row" when creating table.

Thanks,

Bill

hi Bill,

tables are created "without" rows... tables are defined by attributes implemented as columns..

rows usually are not numbered, so you can not say "start at row 1000 and go on until row 1999"... you can select data and insert it into other tables via the INSERT .. SELECT statement but, for your "requirement", you have to perform sort of paging.. that's to say skip the first "n" rows and proceed with the remaining..

a simple "solution" to get this kind of "paging" can be performed via ROW_NUMBER() new Transact-SQL 2005 function..

you project the underlying table's data adding a monotonically increasing new integer row number value..

if you write

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, dataValue varchar(10) NOT NULL ); GO DECLARE @.i int; SET @.i = 1; WHILE @.i <= 1000 BEGIN INSERT INTO dbo.TestTB VALUES ( @.i * 10 , CONVERT(varchar, @.i) + 'abc' ); SET @.i = @.i +1 END; GO WITH CTE AS ( SELECT ROW_NUMBER() OVER( ORDER BY Id ) AS rnum, Id, dataValue FROM dbo.TestTB ) SELECT rnum, Id, dataValue FROM CTE WHERE rnum > 50 AND rnum < 100; GO DROP TABLE dbo.TestTB;

you get all the rows with rnum > 50 and < 100..

and you can even project+insert that result to another destination table like

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, dataValue varchar(10) NOT NULL ); CREATE TABLE dbo.TestTB2 ( Id int NOT NULL PRIMARY KEY, dataValue varchar(10) NOT NULL ); GO DECLARE @.i int; SET @.i = 1; WHILE @.i <= 1000 BEGIN INSERT INTO dbo.TestTB VALUES ( @.i * 10 , CONVERT(varchar, @.i) + 'abc' ); SET @.i = @.i +1 END; GO WITH CTE AS (SELECT ROW_NUMBER() OVER( ORDER BY Id ) AS rnum, Id, dataValue FROM dbo.TestTB ) INSERT INTO dbo.TestTB2 SELECT Id, dataValue FROM CTE WHERE rnum > 50 AND rnum < 100; SELECT * FROM dbo.TestTB2; GO DROP TABLE dbo.TestTB, dbo.TestTB2;

regards

|||

Hi Andrea,

I was gone all day, sorry I took so long to respond. I appreciate your reply to my question.

I'm extremely new at this, and this looks like a very long statement. What saying is it's kind of over my head. Would I have to change any other parameters other than the Table names? Please excuse my lack of knowledge.

By the way, I like the name of your company... Insulin Power.

Thanks,

Bill

|||

hi Bill

Car54 wrote:

I'm extremely new at this, and this looks like a very long statement. What saying is it's kind of over my head. Would I have to change any other parameters other than the Table names? Please excuse my lack of knowledge.

as you already have "your own" tables, yes, you have to modify them..

the actual statement you have to modify only is

WITH CTE AS (SELECT ROW_NUMBER() OVER( ORDER BY [Id] ) AS rnum, -- modify the eventual order by column [Id], [dataValue] -- modify the returned columns FROM [dbo].[TestTB] -- modify the original table name ) INSERT INTO [dbo].[TestTB2] -- modify the destination table name SELECT [Id], [dataValue] -- modify the columns (returned by the previous Common Table Expression result) FROM CTE WHERE rnum > 50 AND rnum < 100; -- modify the "range" as required

By the way, I like the name of your company... Insulin Power.

I do just hope you do not suffer the same problem

regards

|||

Hi Andrea, thank you for posting this. I'm really new at this and I don't know where I would put the name of the table I'm copying, and I'm not sure where to put the number of the row to start at. I apologize for my lack of knowledge. Can you post where I need to enter the rows numbers or anything else I might have to do?

By the way, I didn't realize you were a diabetic and that was the reason for using that name for your company. I'm very sorry to hear that, I have friends that are diabetic.

Thanks,

Bill

|||

hi Bill,

Car54 wrote:

Hi Andrea, thank you for posting this. I'm really new at this and I don't know where I would put the name of the table I'm copying, and I'm not sure where to put the number of the row to start at. I apologize for my lack of knowledge. Can you post where I need to enter the rows numbers or anything else I might have to do?

WITH CTE AS (SELECT ROW_NUMBER() OVER( ORDER BY [Id] ) AS rnum, [Id], [dataValue] FROM [dbo].[TestTB] ) INSERT INTO [dbo].[TestTB2] SELECT [Id], [dataValue] FROM CTE WHERE rnum > 50 AND rnum < 100;

[Id] is the column by which you will order the resultset of the CTE you can modify accordingly to your need;

[Id], [dataValue] are the columns you need to select in the CTE to be inserted in the destination table; modify that colum list accordingly to your needs

[dbo].[TestTB] is the original table you need to get data from;

[dbo].[TestTB2] is the destination table;

50 and 100 are the "boundaries" starting from and ending to you like to export..

By the way, I didn't realize you were a diabetic and that was the reason for using that name for your company. I'm very sorry to hear that, I have friends that are diabetic.

fortunately I do have to admit I'm quiet "happy"

regards|||

Thank you Andrea, and I hope you have a great weekend.

Bill

|||

hi Bill,

Car54 wrote:

Thank you Andrea, and I hope you have a great weekend.

Bill

you too

Create SQL INSERT STATEMENT WITHOUT KNOWING THE TABLE NAME... in a tirgger

I have a process I have inherited that requires me to create an insert statement, but the kicker is that I will not know the temp table's name ahead of time until it has been created. Now my code works as is, but it complains about the syntax and I was hoping there might be a better way of doing this. The code I would like to change is in orange.

Anyway, any advice would be greatly appreciated...

Here is the basic trigger:

Code Snippet

ALTER TRIGGER [dbo].[trg_DownloadDataTypes_ins] ON [dbo].[DownloadDataTypes]

INSTEAD OF INSERT

AS

SET NOCOUNT OFF

DECLARE @.TableName varchar(100),

@.FileType varchar(100),

@.FileName varchar(100),

@.CampusID varchar(15),

@.DateCreated DateTime,

@.ParentID int

IF (SELECT COUNT(FileName) FROM INSERTED) = 1

BEGIN

SELECT @.TableName = (SELECT [FileName] FROM INSERTED)

SELECT @.FileName = (SELECT [FileName] FROM INSERTED)

SELECT @.FileType = (SELECT FileType FROM INSERTED)

SELECT @.CampusID = (SELECT CampusID FROM INSERTED)

SELECT @.DateCreated = (SELECT DateDownLoaded FROM INSERTED)

IF @.TableName = 'SyncTest-Deleteme'

BEGIN

DELETE FROM DownloadDataTypes WHERE [FileName] = 'SyncTest-DeleteMe'

END

ELSE

BEGIN

/*Inserting an IF/ELSE statement here dependant on @.FileType to seperate the handling of 'CAMPUS INVENTORY' file types.

IF @.FileType = 'CAMPUS INVENTORY'

BEGIN

DECLARE @.Qry varchar(4000)

--First, insert the record into tblSyncedInventory.

INSERT INTO tblSyncedInventory([FileName], FileType, CampusID, DateCreated)

SELECT [Filename], FileType, CampusID, DateDownloaded FROM INSERTED

SET @.ParentID = @.@.IDENTITY

--Now, go out and get the child records from their temp table, insert them into tblSyncedInventoryDetails, then drop the temp table.

SET @.Qry = 'INSERT INTO tblSyncedInventoryDetails (FileID, ISBN, Copies, Accession, DateCreated, FileName) SELECT ' + str(@.ParentID) + ', dbo.[' + @.TableName + '].ISBN, dbo.[' + @.TableName + '].Copies, NULL, GETDATE(), dbo.[' + @.TableName + '].FileName FROM dbo.[' + @.TableName + ']'

EXEC (@.Qry)

--Now, drop the temp table.

SET @.Qry = 'DROP TABLE [' + @.TableName + ']'

EXEC (@.Qry)

END

ELSE

BEGIN

INSERT INTO dbo.tblSyncedInventory([FileName],[FileType],[CampusID],[DateCreated]) VALUES (@.TableName, @.FileType, @.CampusID, @.DateCreated)

INSERT INTO tblDownloadDataTypes ([FileName], FileType, CampusID, DateCreated) SELECT [Filename], FileType, CampusID, DateDownloaded FROM INSERTED

SET @.ParentID = @.@.IDENTITY

EXEC stpro_ProcessPDAFileFromTrigger @.TableName, @.FileType, @.CampusID, @.ParentID

END

What error(s) are you receiving?

|||

I think you're missing one or more END statements. Each BEGIN should have a matching one

ALso, you can grab all the variables from INSERTED In one query:

SELECT @.TableName =[FileName],

@.FileName = [FileName],

@.FileType = FileType,

@.CampusID = CampusID,

@.DateCreated = DateDownLoaded

FROM INSERTED

Not sure about how temporary these temp tables are either, but without knowing the app or the rest of the code its difficult to suggest anything constructive.

HTH

sql

Create sequential numbers in a column

I have a temp table that's populated with an insert query in as tored
procedure. The temp table has a uniqueID as the primary key.
In that table I have a column SortOrder.
What I want to do is to create a sequential number in SortOrder but
only for records matching a WHERE statement, for example:
(pardon the shorthand...)
Insert *.tblPermanent into tblTemp
If myField = 1 then
SortOrder = 1(2,3,4,5,....etc.)
else
SortOrder = 0
Thanks
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a temp table that's populated with an insert query in as tored
> procedure. The temp table has a uniqueID as the primary key.
> In that table I have a column SortOrder.
> What I want to do is to create a sequential number in SortOrder but
> only for records matching a WHERE statement, for example:
> (pardon the shorthand...)
> Insert *.tblPermanent into tblTemp
> If myField = 1 then
> SortOrder = 1(2,3,4,5,....etc.)
> else
> SortOrder = 0

There are a lot of things that I don't know about, so I have to make
a guess. First, I make the guess that the tblPermanent has a primary-
key column called id. In such case, you can do:

INSERT tblTemp(id, sortorder, ....)
SELECT id, (SELECT COUNT(*)
FROM tblPermanent b
WHERE b.id >= a.id
AND b.myfield = 1
AND a.myfield = 1), ...
FROM tblPermanent

If this does answer your question, please provide the following:

o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired result from the sample data.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Create permission

Is there such granularity in SQL 2K security that might allow a user to
create a temp table (as in a stored proc) but restrict them from creating a
permanent user table?
Message posted via http://www.sqlmonster.com
All users can create temp tables by default.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4b1a04ce369e4b069715c04b3466fdf2@.SQLMonster.c om...
> Is there such granularity in SQL 2K security that might allow a user to
> create a temp table (as in a stored proc) but restrict them from creating
> a
> permanent user table?
> --
> Message posted via http://www.sqlmonster.com

Create permission

Is there such granularity in SQL 2K security that might allow a user to
create a temp table (as in a stored proc) but restrict them from creating a
permanent user table?
Message posted via http://www.droptable.comAll users can create temp tables by default.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:4b1a04ce369e4b069715c04b3466fdf2@.SQ
droptable.com...
> Is there such granularity in SQL 2K security that might allow a user to
> create a temp table (as in a stored proc) but restrict them from creating
> a
> permanent user table?
> --
> Message posted via http://www.droptable.com

Create permission

Is there such granularity in SQL 2K security that might allow a user to
create a temp table (as in a stored proc) but restrict them from creating a
permanent user table?
--
Message posted via http://www.sqlmonster.comAll users can create temp tables by default.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4b1a04ce369e4b069715c04b3466fdf2@.SQLMonster.com...
> Is there such granularity in SQL 2K security that might allow a user to
> create a temp table (as in a stored proc) but restrict them from creating
> a
> permanent user table?
> --
> Message posted via http://www.sqlmonster.com

Create index before or after insert?

I have a number of remote sql servers from which I collect data. Said data i
s
inserted into #temp tables, such as for example:
CREATE TABLE dbo.#tmp
(
x int not null primary key
, y int not null
)
INSERT INTO dbo.#tmp (x, y)
SELECT x, y FROM server.db.dbo.table
For sync'ing this data with local tables it is adventageous to have an index
on y, x:
CREATE NONCLUSTERED INDEX IX_tmp
ON dbo.#tmp (y, x)
Does anyone see any reason to create that index before or after inserting
the records? I.e. is scenario (A) or (B) below better?
(A) 1. Create temp table; 2. Define index; 3. Insert records
(B) 1. Create temp table; 2. Insert records; 3. Create index
My gut tells me that it's a wash, and keeping the index definition with the
table (in code) is better for maintainability, but there's probably no
performance benefit either way. Or maybe there is a performance benefit to
one that I can't think of?
Comments?
Thanks - KenKH wrote:
> I have a number of remote sql servers from which I collect data. Said
> data is inserted into #temp tables, such as for example:
> CREATE TABLE dbo.#tmp
> (
> x int not null primary key
> , y int not null
> )
> INSERT INTO dbo.#tmp (x, y)
> SELECT x, y FROM server.db.dbo.table
> For sync'ing this data with local tables it is adventageous to have
Temp table usage in stored procedures can be a source of recompilation.
To avoid recompiles (which are costly), you should try to avoid
interleaving DML and DDL statements related to temp tables. Therefore,
you are better off defining all your temp tables up front and creating
indexes on them before inserting or otherwise manipulating data in the
tables.
David Gugick
Imceda Software
www.imceda.com|||If you are doing Bulk Insert of a large number of records at once...
Then drop and recreate the indices. I quote from Books OnLine, From the
Bulk Insert Entry.
"If nonclustered indexes are also present on the table, drop these before
copying data into the table. It is generally faster to bulk copy data into a
table without nonclustered indexes, and then to re-create the nonclustered
indexes, rather than bulk copy data into a table with the nonclustered
indexes in place."
The only exception is when you have a clustered Index on teh table, AND you
have the luxury of pre-sorting the data in in the same Order as they will be
in the Clusterd Index (Therefore Inserting the records in Clustered Index
Order). In THis special case, leave the CLustered Index on the table during
the Insert.
"David Gugick" wrote:

> KH wrote:
> Temp table usage in stored procedures can be a source of recompilation.
> To avoid recompiles (which are costly), you should try to avoid
> interleaving DML and DDL statements related to temp tables. Therefore,
> you are better off defining all your temp tables up front and creating
> indexes on them before inserting or otherwise manipulating data in the
> tables.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>