Inserting Records from table on one server into table on another

[earthandfire]

The following code:

TRUNCATE TABLE SVUKSWIPC002513.DealerAuditData.dbo.F5W03

generates the error:

The object name 'SVUKSWIPC002513.DealerAuditData.dbo.' contains more than the maximum number of prefixes. The maximum is 2.


If I remove .dbo, it complains that it can't find a database called 'SVUKSWIPC002513, which is obviously correct since it is a server.

I've linked the server using:

EXEC sp_addlinkedserver 'SVUKSWIPC002513', N'SQL Server'

DealerAuditData is the database and F5W03 is the table.

There are around a dozen tables that I need to Truncate then rebuild using Insert Into. The code works perfectly without the linking - by saving the data in the main server.

Any advice would be greatly appreciated.

 

[SQLDenis]

you can not use that on truncate, see example

use master
go
create table testtruncate (id int)
go
-- works
select * from yourServername.master.dbo.testtruncate
-- error
truncate table yourServername.master.dbo.testtruncate

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[maswien]

Instead of using truncate, you can use delete to do the same thing.

 

[SQLDenis]

1 difference, truncate resets the identity while delete does not
And course truncate is much faster
Also you can't truncate tables referenced by foreign keys

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[earthandfire]

Thanks for the explanation - I couldn't understand why SELECT and INSERT worked but TRUNCATE didn't.

 

[maswien]

I guess command 'truncate' is not counted as a command from SQL Data Manipulation Language (DML), in DML you can refer to a remote table otherwise you can't.