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?
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.
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
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:
- Truncate the targettable on the TARGETSERVER. This step must be done BEFORE the data pump step.
- Create a datapump step that specifies the SOURCESSERVER as the source and the TARGETSERVER as the destination.
- 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
Related Q&A from Joe Toscano
Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer ... Continue Reading
Can I call triggers in stored procedures or vice versa? Continue Reading
We're having a lot of trouble scheduling and editing SSIS jobs in SQL Server 2005. The SA has created a proxy account for us to create SSIS packages.... Continue Reading