[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating Temporary tables in MsSQL

Posted on 2006-05-31
6
Medium Priority
?
640 Views
Last Modified: 2012-08-13
I have an application that uses mYsql, that we are converting to support mSsql (2000).
It creates temporary tables and uses them to access data for things like a user privilege system.

How do you create temporary tables in mSsql?

The syntax I have in mYsql is:

CREATE TEMPORARY TABLE `effective_privileges`
(
      `site` int (11) DEFAULT  '0' NOT NULL ,
      `com_respondent` tinyint (1) DEFAULT  '0' NOT NULL ,
      `com_complianceadmin` tinyint (1) DEFAULT  '0' NOT NULL ,
      `com_author` tinyint (1) DEFAULT  '0' NOT NULL ,
      `man_documentcontrol` tinyint (1) DEFAULT  '0' NOT NULL ,
      `man_actionplans` tinyint (1) DEFAULT  '0' NOT NULL ,
      `man_riskanalysis` tinyint (1) DEFAULT  '0' NOT NULL ,
      `man_businessimpact` tinyint (1) DEFAULT  '0' NOT NULL ,
      `man_businesscontinuity` tinyint (1) DEFAULT  '0' NOT NULL ,
      `man_sites` tinyint (1) DEFAULT  '0' NOT NULL ,
      `man_assets` tinyint (1) DEFAULT  '0' NOT NULL ,
      `man_roles` tinyint (1) DEFAULT  '0' NOT NULL ,
      `man_incidents` tinyint (1) DEFAULT  '0' NOT NULL ,
      `man_reporting` tinyint (1) DEFAULT  '0' NOT NULL ,
      `ent_compliancemanager` tinyint  
)


The syntax I have so far failed to use in mSsql is :

CREATE TABLE effective_privileges
(
      site NUMERIC (11) DEFAULT  '0' NOT NULL ,
      com_respondent NUMERIC (1) DEFAULT  '0' NOT NULL ,
      com_complianceadmin NUMERIC (1) DEFAULT  '0' NOT NULL ,
      com_author NUMERIC (1) DEFAULT  '0' NOT NULL ,
      man_documentcontrol NUMERIC (1) DEFAULT  '0' NOT NULL ,
      man_actionplans NUMERIC (1) DEFAULT  '0' NOT NULL ,
      man_riskanalysis NUMERIC (1) DEFAULT  '0' NOT NULL ,
      man_businessimpact NUMERIC (1) DEFAULT  '0' NOT NULL ,
      man_businesscontinuity NUMERIC (1) DEFAULT  '0' NOT NULL ,
      man_sites NUMERIC (1) DEFAULT  '0' NOT NULL ,
      man_assets NUMERIC (1) DEFAULT  '0' NOT NULL ,
      man_roles NUMERIC (1) DEFAULT  '0' NOT NULL ,
      man_incidents NUMERIC (1) DEFAULT  '0' NOT NULL ,
      man_reporting NUMERIC (1) DEFAULT  '0' NOT NULL ,
      ent_compliancemanager tinyint  
)

I dont know how to make this work, as the syntax above is accepted but later queries to the table 'effective_privileges' are failing with the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'effective_privileges'.

This would suggest the table is not being created.

Please help - very urgent!

Thanks

Ashley ;-)
0
Comment
Question by:jbclelland
  • 2
4 Comments
 
LVL 3

Expert Comment

by:Titan522
ID: 16801901
To create a temp table you need to put a # or @ as at the beginning of the table name so sql knows its a temp table.

CREATE TABLE #effective_privileges
(
     site NUMERIC (11) DEFAULT  '0' NOT NULL ,
     com_respondent NUMERIC (1) DEFAULT  '0' NOT NULL ,
     com_complianceadmin NUMERIC (1) DEFAULT  '0' NOT NULL ,
     com_author NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_documentcontrol NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_actionplans NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_riskanalysis NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_businessimpact NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_businesscontinuity NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_sites NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_assets NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_roles NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_incidents NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_reporting NUMERIC (1) DEFAULT  '0' NOT NULL ,
     ent_compliancemanager tinyint  
)

CREATE TABLE @effective_privileges
(
     site NUMERIC (11) DEFAULT  '0' NOT NULL ,
     com_respondent NUMERIC (1) DEFAULT  '0' NOT NULL ,
     com_complianceadmin NUMERIC (1) DEFAULT  '0' NOT NULL ,
     com_author NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_documentcontrol NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_actionplans NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_riskanalysis NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_businessimpact NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_businesscontinuity NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_sites NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_assets NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_roles NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_incidents NUMERIC (1) DEFAULT  '0' NOT NULL ,
     man_reporting NUMERIC (1) DEFAULT  '0' NOT NULL ,
     ent_compliancemanager tinyint  
)
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16803877
Not quite... you're talking about a tble variable:

And I'm guessing you actually need a bit field instead of the numeric(1) fields...

DECLARE  @effective_privileges TABLE
(
     site NUMERIC (11) DEFAULT  0 NOT NULL ,
     com_respondent NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     com_complianceadmin NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     com_author NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     man_documentcontrol NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     man_actionplans NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     man_riskanalysis NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     man_businessimpact NUMERIC (1,0) DEFAULT 0 NOT NULL ,
     man_businesscontinuity NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     man_sites NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     man_assets NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     man_roles NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     man_incidents NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     man_reporting NUMERIC (1,0) DEFAULT  0 NOT NULL ,
     ent_compliancemanager tinyint  
)
0
 

Author Comment

by:jbclelland
ID: 16815266
Do temporary tables in mSsql work the same as mYsql apart from this syntax?
0
 
LVL 13

Accepted Solution

by:
Atlanta_Mike earned 2000 total points
ID: 16817300
Most likely.

The # Temp table will exist until the session is released. (keep in mind query analyzer holds a session until the query window is closed).

##Temp table keep the table until the server is rebooted.

@ table variable is dropped after the execution.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question