Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL SERVER MANAGEMENT STUDIO: Create Table in Stored Procedure and view in object brower

Posted on 2007-03-27
12
Medium Priority
?
763 Views
Last Modified: 2008-06-28
Hello,

How do I create a table in a stored procedure and view it in management studio?

I've tried a create table command and a select into command. Both execute without errors. However the Object Explorer does not seem to register that a table exists in the selected database. What's the deal? When I create a table in a stored procedure where does it go? Is it a temporary table? How do I verify the table has been correctly populated?

Any help is appreciated,

Ryan

CREATE TABLE CEA.DBO.RYAN_TEMP
      (POLICY_ID INT)
      
      CREATE TABLE CEA.DBO.CEA_TRANS
      (POLICY_ID INT)
INSERT INTO RYAN_TEMP
                      (POLICY_ID)
VALUES     (96)
            SELECT policy_id
      INTO CEA.DBO.RYAN_TEMP
      FROM POLICY
      WHERE policy_id = 96
0
Comment
Question by:888_ryan_888
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18801538
>>However the Object Explorer does not seem to register that a table exists in the selected database. What's the deal? <<
Have you tried refreshing?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18801554


CREATE TABLE cea.DBO.RYAN_TEMP
      (POLICY_ID INT)
CREATE TABLE cea.DBO.CEA_TRANS
      (POLICY_ID INT)

-- try to use fully qualified names in the insert/select part too

INSERT INTO cea.DBO.RYAN_TEMP (POLICY_ID)
VALUES     (96)

SELECT * FROM cea.DBO.RYAN_TEMP
0
 

Author Comment

by:888_ryan_888
ID: 18801555
Yes, I've tried refreshing.

Can you view a table created through a stored procedure?

0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:888_ryan_888
ID: 18801560
I should be able to view the table regardless of values that are present in the table.

Can you view a table created in a stored procedure?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18801576
you can check something like this immediately after createing

create table test( i int)
If OBJECT_ID('test') IS NOT NULL
  Print  'table created'
Else
  Print 'No table available'
0
 

Author Comment

by:888_ryan_888
ID: 18801667
hmmm i can't get the object_id code to work, also  I've searched the drives for anything named ryan_temp and nothing comes up.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18801742
try this

SELECT OBJECT_ID('cea.DBO.RYAN_TEMP')
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18801756
>>I've searched the drives for anything named ryan_temp and nothing comes up. <<
That could be viewed by some as a wild goose chase.
0
 

Author Comment

by:888_ryan_888
ID: 18801804
yeah, the select doesn't fail, so it exists somewhere as something, but evidently not a table viewable in the object explorer. The idea here is to populate a table for export to text as a flat file. I'll need to view the table and may export it through ms access in which case the table will actually need to real in the same way that all of the other dbos in the database exist.

0
 

Author Comment

by:888_ryan_888
ID: 18801805
hahaha ... as could this, simply the only option available to me right now,
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 21890440
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

636 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