• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

SQL - cannot see the table I create

I use a sql script to create table
At the beginning of  my script, I will always delete the table and then create table and insert data.

When I run the 2nd time, I got the below error and I can not see my table (look as if hidden) in my database from the object explorer panel of SMSS

Msg 2714, Level 16, State 6, Line 2
There is already an object named 'My Table' in the database.

(3306 row(s) affected)

Can anyone know what is happening.  I have been using the same procedure for creating other table.  I try to rename MyTable to other name nothing change.

Here is my SQL script
0
tommym121
Asked:
tommym121
2 Solutions
 
lwadwellCommented:
When you say delete, you mean drop ... right?
As 'My Table' has a space in it ... do you reference it in quotes or square brackets, eg [My Table]?
0
 
tommym121Author Commented:
I finally get it working again by opening a new query editor window, then cut and paste the above script to this new window.  It can repeatedly execute without any error and also 'MyTable' appears under the database in Object Explorer.

I still like to know what is going on if someone can explain what had happened. Thanks
0
 
LIONKINGCommented:
Can we see the T-SQL script?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
tommym121Author Commented:
IF OBJECT_ID('[Database1].[dbo].[MyTable]]') is NOT NULL
      DROP TABLE [Database1].[dbo].[MyTable]      
/****** Object:  Table [dbo].[MyTable]    Script Date: 09/14/2012 07:19:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MyTable](
      [Name] [varchar](200) NULL,
      [Owner] [varchar](200) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

insert into [dbo].[MyTable]
(
      [Name],[Owner]
)
SELECT
       a.Name, '  ' As [Owner]
FROM [Database1].[dbo].[Alternate_Table] a
0
 
Jared_SCommented:
Instead of dropping and recreating the table, consider using the truncate command.

It will delete the record from your table without filling up your transaction log.
0
 
tommym121Author Commented:
Thanks for helping
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now