Execute Queries on a Linked Server

This section describes how to execute queries to the Bugzilla linked server from any tool that can connect to SQL Server.

When executing queries from a linked server, you must use a four-part name format, as shown below.

[<Linked Server Name>].[<DSN Name>].[<Schema Name (normally but not always the service name)>].[<Table Name>]

Where:

• Linked Server Name is the name of the linked server that you created.
• DSN Name is the name of the data source.
• Schema Name is the name of the system schema or user-defined schema.
• Table Name is the name of the table you are copying data from.

SELECT Statements

To create new records:

SELECT * FROM [Linked Server Name].[CData Bugzilla Sys].[Bugzilla].[SampleTable_1] WHERE Column2 = 'Bob'

You can run a bulk INSERT using a SELECT query in place of a VALUES list.

INSERT INTO [Linked Server Name].[CData Bugzilla Sys].[Bugzilla].[SampleTable_1] ([Column2])
SELECT [Column2] from [CData].[dbo].[Local_SampleTable_1]

You can run a bulk UPATE using a JOIN with a local table.

UPDATE [Linked Server Name].[CData Bugzilla Sys].[Bugzilla].[SampleTable_1] SET [SampleTable_1].[Column2] = CData.dbo.[Local_SampleTable_1].[Local_Column2]
FROM CData.dbo.[Local_SampleTable_1] INNER JOIN [Linked Server Name].[CData Bugzilla Sys].[Bugzilla].[SampleTable_1] ON CData.dbo.[Local_SampleTable_1].[Id] = [SampleTable_1].[Id]

You can run a bulk DELETE using the IN filter with sub-SELECT query.

DELETE FROM [Linked Server Name].[CData Bugzilla Sys].[Bugzilla].[SampleTable_1] WHERE [Id] IN (SELECT [Id] FROM CData.dbo.Local_SampleTable_1)