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

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
888_ryan_888Asked:
Who is Participating?
 
Computer101Connect With a Mentor Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
 
Anthony PerkinsCommented:
>>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
 
Aneesh RetnakaranDatabase AdministratorCommented:


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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
888_ryan_888Author Commented:
Yes, I've tried refreshing.

Can you view a table created through a stored procedure?

0
 
888_ryan_888Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
888_ryan_888Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
try this

SELECT OBJECT_ID('cea.DBO.RYAN_TEMP')
0
 
Anthony PerkinsCommented:
>>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
 
888_ryan_888Author Commented:
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
 
888_ryan_888Author Commented:
hahaha ... as could this, simply the only option available to me right now,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.