Link to home
Start Free TrialLog in
Avatar of mustish1
mustish1

asked on

Copy a view

SSMS 19.0

There is a view name "Bks_DWH_TrainingProgramsBasicData" stored in the LMS-DEV database. I want to copy the structure and data into a different database, "Sandbox". Can you please tell me the SQL statement or the stored procedure?
User generated image

User generated imageUser generated image

Avatar of Qlemo
Qlemo
Flag of Germany image

A view does not contain data, it just has a SQL definition. Do you want to transfer the definition of the view, so it reads data from the other database, or really want to transfer the content?

Hi,

the easiest way is to use backup, create a full backup as "copy only" backup, then restore it and in the restore dialog just give it a new database name (file names are automatically adapted).

Another method, if you do not have access to backup location, right click on database and use "Generate Script", where you can script the whole schema of a database and also the data.

A third method: Use Visual Studio with installed SQL Server Data Tools. Here you can use a compare between two databases and deploy the differences. So if you create an empty database first and compare it you get everything deployed. A second tool, data compare, does the same with the data so your tables get filles after deploying the schema.

Cheers,

Christian

Avatar of mustish1
mustish1

ASKER

Contents

ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia 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

I don't have a table in the database Sandbox. Is it possible to copy the structure too from that view? 

SELECT INTO automatically creates the table structure (but only fields/datatypes, no indexes or anything else).

User generated image

User generated image


Unless you have linked the databases you cannot select across them.

Unless you have a schema called 'schema' that won't work.  It was an example showing <put your schema here>.

I believe the suggestion you accepted was meant to:
In the remote database, LMS-DEV, create a physical table from the view.  THEN export that table to import into Sandbox.

Another option is to export the data from they query of the view to say a CSV file, then import the data into Sandbox.

@slightwv, if its the same MSSQL instance, the accepted solution works (if using correct DB, schema, and view name, of course).

True.

From a previous question I remembered Sandbox was local.  Wasn't sure if he pulled the entire remote database down locally as well.