Creating a table in SQL Server using SELECT ... INTO

Question

Hi ...

i am creating a table in sqlserver database using query like "SELECT Table1.* INTO Table2 FROM Table1" here Table2 created successfully but it is not showing my database

when i again fire this query than it gives error that Table2 is already created but i can't see this in my database

i am refreshing my database also

so please help me if anyone has solution..

Answer 1

have you tried with the username.Table1, like

  dbo.table

Its very important to append the username for any db object, it enforces the user to select the objects which he got permission to view

Answer 2

@Ramesh has the right idea. In some situations (I think if your user is in the db_owner role?), SELECT INTO tables are created in the schema (the SQL 2005+ terminology) associated with your login. This may be something like YOURDOMAIN\username.Table2. If you go to select again from the same login, it will work fine, but chances are that other users will not be searching in your schema.

When in doubt, explicitly create the table in the dbo schema:

SELECT Table1.*
INTO dbo.Table2
FROM Table1