question

cprplano2011 avatar image
cprplano2011 asked

Scheduled copying of a table from one server to another

Is there any way I can schedule a job for copying one table from prod server to another every night and delete the old table?
sql-server-2005jobs
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
Just curious, is the "table" recreated daily? I would assume so since you want the job to run nightly. I also think that I'd be cautious about just dropping a table in production. There are a multitude of ways to do this: 1. Using a Linked Server 2. Develop a SSIS package 3. Use SQLCMD 4. Red Gate (to push the schema/data changes) and then a script to drop the table. 5. Use a home grown .NET app Is there a specific problem that you're attempting to solve? Hope this helps!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
I also forgot Powershell...
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
You can also use OPENDATASOURCE like this: SELECT * INTO dbo.MyNewTable FROM OPENDATASOURCE('SQLNCLI','Data Source=NameOfYourServer;Integrated Security=SSPI').YourDatabaseName.YourSchemaName.[YourTableName] AS [d] Replace values to specify your server, database, schema and table name. You can also change the Connection string to not use integrated security by specifying username and password
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
Yes, as JohnM said, there are several ways, and he lists several. A method I have used for somewhat similar tasks is to establish a linked server relationship. Then, write and thoroughly test a stored procedure that does what you want (in this case, copy the data into the destination server and delete the old table). Then you can have a SQL Server Agent job execute the stored procedure on a timer. To agree with John again, I would be cautious about dropping a table in production on a regular basis. If the data is significant, you also probably want to take steps to ensure that the insert was completely successful before the drop command is issued and that any appropriate backups are in place.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.