CODEWITHSUNDEEP
sql-serverdatabasedatabase-table
Gork. O
Gork. O

Reputation: 65

Where did my new table go

I have a database called mbt. I wanted to write some data from temporary table to real table. 

--I used this query.

SELECT *  INTO new_table  FROM #tmp

when i runned the query it returned normal message.

15813 row(s) affected

After that i checked my tables in mbt database, but i couldn't see 'new_table'

how could such a thing be, where the table might have gone.

I may have forgotten to use 'use MBT' statment at the beginning of the query. Does it make problem

I'm using ms sql server 2014(SP2)(KB3171021)-12.0.5000.0(X64)

ANSWER

It gone to Master DB

select 'master' as DatabaseName, 
       T.name collate database_default as TableName 
from master.sys.tables as T

Upvotes: 1

Views: 90

Answers (4)

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Run the query below to find databases which have the object new_table:

sp_MSForEachDB 'Use [?] IF EXISTS (SELECT 1 FROM sys.objects WHERE name= ''new_table'')
SELECT DB_NAME()'

Upvotes: 1

Aminur Rahman
Aminur Rahman

Reputation: 410

It Will create a new table on your database. but you did not use so it will store in master database on your server.

Upvotes: 1

Dharmadas Mohite
Dharmadas Mohite

Reputation: 1

Always use command "USE db_name" to make sure that you are querying right database.

Below command will show all databases available on the server. SHOW DATABASES;

If you are using GUI tool to connect DB server, there is a possibility that at the time of connection you got connected to different DB. If you executed the query to create table and inserted record. These records are inserted in new table in different DB than mbt.

Upvotes: 0

rsp
rsp

Reputation: 13

I had the same problem. What i did is, I rewrite the statement of use Database and then refresh the database browser after that i got Result. You can try it. may be it will help you.

Upvotes: 0

Related Questions