Inserting, Updating, or Deleting Data from Remote Sources in SQL Server


The same techniques used for reading data from remote data sources from within SQL Server can also be applied to executing INSERT, UPDATE, and DELETE statements on the remote data sources.

Using Ad Hoc Connections for Inserting, Updating, and Deleting Data

Both OPENROWSET and OPENDATASOURCE can be used on the INSERT, UPDATE, and DELETE statements to manipulate data on a remote data source.

When using OPENROWSET, you can specify a query to execute or you can specify a fully qualified name referencing the table you would like to update.

The code below shows how to add a new record into an Excel file. (The code in this section is included in the sample files as UsingAdHocToUpdate.sql in the SqlScripts folder.)

INSERT OPENROWSET(     'Microsoft.Jet.OLEDB.4.0',     'Excel 8.0;DATABASE=C:\Documents and Settings\User\My Documents\         Microsoft Press\Sql2005SBS_AppliedTechniques\         Chapter08\EmployeeList.xls',     'SELECT FirstName, LastName, Title, Region FROM [Employees$]') VALUES ('John',         'Doe',         'Consultant',         'CA')


The following code shows how to insert a new record into a remote SQL Server database.

-- INSERT into a remote SQL Server database INSERT OPENROWSET(     'SQLOLEDB',     'Server=Sales; Trusted_Connection=yes;',     'SalesDB.Sales.Orders') VALUES (175642, '2001-10-04', 6500.05) GO


OPENDATASOURCE can be used to INSERT, UPDATE, or DELETE a fully qualified remote table. In this case, you do not need to specify a query. The code below shows this technique when working with a Microsoft Office Excel file.

INSERT OPENDATASOURCE(     'Microsoft.Jet.OLEDB.4.0',     'Excel 8.0;DATABASE=C:\Documents and Settings\User\My Documents\         Microsoft Press\Sql2005SBS_AppliedTechniques\Chapter08\         EmployeeList.xls')...[Employees$] VALUES ('99',         'Doe',         'John',         'Tester',         'Mr.',         '12/06/1964',         '5/1/1995',         '507 20th Ave. S.',         'Seattle',         'WA',         '98122',         'USA',         '(206) 555-9857',         '5467',         'testing',         '2')


Using Linked Servers for Inserting, Updating, and Deleting Data

After you create a linked server using the sp_addlinkedserver stored procedure, you can use that reference to execute INSERT, UPDATE, and DELETE statements on a fully qualified remote table.

The code below shows this technique when working with a remote SQL Server Database. (The code in this section is included in the sample files as UsingLinkedServerToUpdate.sql in the SqlScripts folder.)

EXEC sp_addlinkedserver @server = 'SalesServer',     @srvproduct=N'SQL Server' GO DELETE SalesServer.SalesDB.Sales.Orders WHERE Quarter = 3


To use the OPENQUERY function, instead of specifying a query to execute, you need to provide the name of the table to INSERT, UPDATE, or DELETE.

The following code shows this technique when working with an Excel file.

EXEC sp_addlinkedserver     @server = 'MyEmployees',     @srvproduct = 'Jet 4.0',     @provider = 'Microsoft.Jet.OLEDB.4.0',     @datasrc = 'C:\Documents and Settings\User\My Documents\         Microsoft Press\Sql2005SBS_AppliedTechniques\         Chapter08\EmployeeList.xls',     @provstr = 'Excel 8.0' GO UPDATE OPENQUERY(MyEmployees, 'SELECT * FROM [Employees$]') SET LastName = 'Newname' WHERE Region = 'CA'





Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net