Make New Daily Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Make New Daily Table

Author

Topic

guerillaunit
Starting Member

1 Post

Posted - 2012-01-16 : 09:26:05

Hi there, I have 2 database servers. I would like to take a table from one of the servers and use that table to make a new table on the 2nd database on a daily basis. This is something I am currently doing in Access (without of course storing the new table on a SQL database), but which I woud like to transfer to SQL Server. I just started using SQL Server so I am not too familiar with what I would have to do to accomplish this. I have the full SQL coding to make the table, I just need some instructions on how to "set up" the template in SQL Server Management Studio. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 10:00:59

you first need to link both the servers using a linked server

see below for the steps

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

Then you need to execute the below query


USE [Server1]
DROP TABLE YourTable
GO

SELECT * INTO YourTable FROM Server2.DBName.dbo.SourceTable
..

then you can add this code in a sql server agent job and schedule it to execute daily

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources