Truncate a table on a remote server in MS SQL Server 2008
I’ve been learning alot since I’ve been in my new job and I must say I love it.
I’m working with a completely different set of tools. For instance, most of the development I will be doing will be in .NET and looking in the occasional Delphi apps.
Over the years I’ve worked with MySQL alot and with MS SQL Server a bit (2000 and 2005).
In this position, I work with MS SQL Server 2008. And most of what I do is using stored procedures which is a switch from most of the solely web based apps I’ve done in the past.
I’ve been learning many aspects of MS SQL Server and how to work with link servers. While I’ve been learning, I’m only scratching the surface of what MS SQL can do.
So working with a linked server, I needed to truncate a table and fill it with data from another server.
I’m not sure this is the most elegant way, but this is what I came up with.
— Truncate the acuheader table EXEC(‘TRUNCATE TABLE [Test_DB].dbo.thisTable’) AT [Link-Server] — insert the data INSERT OPENQUERY([Link-Server],‘SELECT * FROM [Test_DB].dbo.thisTable’) SELECT * FROM [Test_DB].dbo.thisTable
For those of you who haven’t used TRUNCATE, it is a much more efficient way to remove all table rows.
Basically, this combine with a Job allows to keep a table on another server in sync with another. Of course, you wouldn’t necessarily want to replicate your tables this way, the built in replication functionality is what you’d want for that.
But for the case this was used in, it seems to work fine and well.
I look forward to learning a lot more with MS SQL Server. It’s a powerful beast.