1

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
Gork. O
  • 65
  • 7
  • 1
    If you phrase the question as `how do I find which databases contain a particular table?` (which is surely what you're asking now, rather than expecting us to be psychic) you should find plenty of existing Q&A on this site. – Damien_The_Unbeliever Feb 06 '18 at 11:42
  • 1
    How did you check that "table does not exist"? Did you try to select from it? – sepupic Feb 06 '18 at 11:59

4 Answers4

1

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

Aminur Rahman
  • 400
  • 1
  • 6
  • 14
1

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()'
Denis Rubashkin
  • 2,151
  • 1
  • 9
  • 14
0

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.

rsp
  • 13
  • 6
0

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.

Dharmadas Mohite
  • 1