Modifying Using SQL INSERT, UPDATE, and DELETE
Modifying Using SQL INSERT, UPDATE, and DELETE
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].[Salesforce].[Account] (Industry) VALUES ('Floppy Disks');
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');
Multiple Row operations
To perform multiple row operations, you can run a multiple row INSERT using a SELECT query in place of a VALUES list:
INSERT INTO [Linked Server Name].[CData].[Salesforce].[Account] ([Industry])
SELECT [Industry] from [dbo].[Local_Account]
To run a multiple row UPDATE, you can use JOIN with a local table:
UPDATE [Linked Server Name].[CData].[Salesforce].[Account] SET [Account].[Industry] = CData.dbo.[Local_Account].[Local_Industry]
FROM CData.dbo.[Local_Account] INNER JOIN [Linked Server Name].[CData].[Salesforce].[Account] ON CData.dbo.[Local_Account].[ID] = [Account].[ID]
To do a multiple row DELETE, you can use the IN filter with a sub-SELECT query:
DELETE FROM [Linked Server Name].[CData].[Salesforce].[Account] WHERE [ID] IN (SELECT [ID] FROM CData.dbo.Local_Account)