CData ODBC Driver for Amazon Marketplace 2019 - Online Help
Questions / Feedback?

Execute Queries on a Linked Server

CData ODBC Driver for Amazon Marketplace 2019 - Build 19.0.7354

This section describes how to execute queries to the Amazon Marketplace 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 AmazonMarketplace Sys].[AmazonMarketplace].[SampleTable_1] WHERE Column2 = 'Bob'

INSERT, UPDATE, and DELETE Statements

To insert, update, or delete a single row of data (that is, not a batch of records), you need to use the four-part name. For example:

INSERT INTO [Linked Server Name].[CData AmazonMarketplace Sys].[AmazonMarketplace].[SampleTable_1] (Column2) VALUES ('Bob');

To retrieve newly created IDs from an INSERT operation, you can query the sys_identity system table. For example:

SELECT * FROM OPENQUERY([Linked Server Name], 'SELECT * FROM sys_identity');

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

INSERT INTO [Linked Server Name].[CData AmazonMarketplace Sys].[AmazonMarketplace].[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 AmazonMarketplace Sys].[AmazonMarketplace].[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 AmazonMarketplace Sys].[AmazonMarketplace].[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 AmazonMarketplace Sys].[AmazonMarketplace].[SampleTable_1] WHERE [Id] IN (SELECT [Id] FROM CData.dbo.Local_SampleTable_1)

 
 
Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 19.0.7354.0