Link to home
Start Free TrialLog in
Avatar of tommym121
tommym121Flag for Canada

asked on

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
SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tommym121

ASKER

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
Can we see the T-SQL script?
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for helping