Select all data into a new table in a different DB

Transact-SQL
1

Hi all,

in SQL Server (2012 at least), is there a way or what ways are there to do the following:

If we have an existing database:

  • create a new database
  • do a select * from the existing database table into the new database where there is NO table created
  • create the same schema, relationships, keys etc...

Is there a way to do this or must I create/script out the tables, views etc... and apply it into the new DB before being able to do a SELECT data from source to dest?

2

That will give you a new database with all schema objects, but no data. You can then populate data as you see fit.

Otherwise, if you need data and schema, backup/restore is the easiest option.

3

Wouldn't just doing a RESTORE do the trick?

4

Hi Jeff
unfortunately not. Trying to do this to a different DB altogether without impacting the original DB due to the fact that there are views and SPROC's that reference other DB's from within it and want to try to "clean" it up correctly as much as possible by creating the same schema but changing the references and then finally copying the data from the old DB to the new DB.

However I still have some doubts. The other way, I suppose in my situation, would be to create a new SQL Server instance and restore the original DB's to there so the main instance does not get impacted at all.

5

Based on your previous post, this option will be your best choice.

I still think you should invest the time in using synonyms, rather than trying to redo all of your DB references, because you'd have to rename them every time you refresh another environment. Using the synonyms only has to be done once, and you refresh/redefine them in a separate step after restoring a backup.

6

It probably won't help you now but we practice one of the most important things for front end code and the database itself... abstraction.

We live by the rule of "Always and only use the 2 part naming convention". If we have, for example, a stored procedure that needs to reference a table in another user database on the instance, it's done through the use of a synonym.

7

I fully agree. This is how the DB was before my time unfortunately...