Copying views from one database server to another

SQL Server expert Joe Toscano tells explains how to use a stored procedure to copy a view in one database to another.

I need to copy a simple view in one database on one server to a table in a separate database on a separate server nightly. I would like to do this with a stored procedure on the server where the view lives. I can drop the target table and just recreate it.

What is the easiest way to accomplish this? Can/should I do this from a stored procedure or should I use DTS or...


Both machines are running version 8. One server is local, the other is accessed via TCP and lives in a data center off site. The size of the view can become quite large over time. But the copy would be done nightly with no users online. Can you give me some advice on direction here?

I would recommend that you create a DTS Package to transfer the object. There was a 'Copy SQL Server Object Task' that was made exactly for this purpose. In this task you specify the source and destination servers, the source and destination databases along with the object(s) you wish to transfer. You will have to specify the exact database object(s) you wish to transfer in the copy tab of this task.

Create the DTS Package and verify that you can execute it in DTS Design Mode. Verify the view was indeed copied to the remote server. You may have to drop the destination object each time the package is executed. Save the package and then you can have the SQL Server Job scheduler automatically kick-start the package by right-clicking on the newly created package and choosing Schedule Package option. From here, you can provide the job scheduling details.

One additional point I'd like to make. I'm confused by your stating that the size of the view can be quite large over time. Views, unlike tables, do not hold data. They are simply named SQL statements we create to perhaps simplify the end-users perception of data in our database. I've also used views for security purposes to hide or exclude sensitive columns. Views, therefore, are not large because they are simply database objects that hold SQL Statements. Granted, the table(s) that view 'hits' can certainly be large.

Reader's follow-up question

What I'm actually trying to do is to create a table on the target server based on the view and the data portrayed in the view. In other words I need to use the structure of the view in one server to create a real table in the target server, then populate it with the data that the view was created to portray on the source server.

I'm looking for the best solution to this scenario. I was hoping there was such a package or procedure. I'm actually integrating a normalized database with five tables into a hierarchical table structure on the target server. I use a union view on the source server to get the data looking right, i.e. matching the structure of the target hierarchical table on the target server. Then I need to drop or truncate the hierarchical table on the target server nightly and re-populate it from the view. The source server is local to me, the target server is remote hosted in a data center. The data transfer is via TCP/IP.

Joe's response

You have a view on a source server. On the target server you need to create a table based on the schema of this view and you need to populate this new table with all of the corresponding source data. Finally, you need to be done on a daily basis.

Here are a couple options you may want look into:

Option 1: Use a linked server

First, you can create a linked server on your source server that points to the target server. Look under the security tab in Enterprise Manager so do this. Once this is done, you can access the target servers' database objects using the fully qualified path name as follows:


If you choose to go this route, you can create and schedule a SQL Server Job on the source server that has the following Transact-SQL Steps:

Step 1: Drop table TARGETSERVER.databasename.dbo.targettable

Step 2: Select * into TARGETSERVER.databasename.dbo.targettable

From database.dbo.v_vewname

Here, the select into will automatically create the new table every time the job is executed. The schema of this newly created table will match the result set produced by selecting from the view. You can place these Transact-SQL Steps in a stored procedure that resides on the source server if you'd like and have a job automatically execute the procedure on a scheduled basis.

Here's a similar approach that also uses linked servers. The difference is that the second link-server approach assumes that you already have created the target table. This second approach uses a insert / select which is a logged statement while the first uses a 'select into,' which is non-logged. The nonlogged statement should run real fast while the insert /select approach is logged and will be slower.

Step 1: truncate table TARGETSERVER.databasename.dbo.targettable

Step 2: insert TARGETSERVER.databasename.dbo.targettable (column listing)

Select column listing ... from database.dbo.v_viewname

Option 2: Use a DTS package with a data pump

If you don't want to create a linked server definition, you can create a DTS Package that accomplishes the same thing. The DTS Package should have two connections that I will call SOURCESERVER and TARGETSERVER. It is assumed that you will manually create the target table on the target server. The DTS Package then must perform the following steps:

  1. Truncate the targettable on the TARGETSERVER. This step must be done BEFORE the data pump step.
  2. Create a datapump step that specifies the SOURCESSERVER as the source and the TARGETSERVER as the destination.
  3. Configure the Data Pump as follows
  • The actual source specified in the data pump definition will be the source servers's view
  • The actual destination specified in the data pump definition should be the target server's target table.
  • Click on the transformations tab to verify that the source view columns and the destination target table columns map correctly.

Dig Deeper on SQL Server Stored Procedures