When we build a database, when the database that and think the database of the different type of will dispersive throughout classifies collect builds in this is medium, undertaking data examines especially, when purify and be being changed, face meeting very big challenge. Fortunately SQL Server offerred powerful, rich data to guide for us derive function, and be in guide derive while can undertake agile processing to data.
There basically are 3 kinds of meanses to guide in SQL Server derive data: Use Transact-SQL undertakes handling to data; Call BCP of command travel tool to process data; Service data changes a service (DTS) undertakes handling to data. These 3 kinds of methods have its characteristic each, the main characteristic with respect to them undertakes comparative below.
One, the comparison of use means
1. Use Transact-SQL undertakes counting occupying guiding deriving
We see very easily, transact-SQL method carries SQL statement kind namely the data in the database of will identical or different type guides each other derive or the method that the assemble is in. If be,undertake counting occupying guiding deriving between different SQL Server database, that will be accomplished very easily. Can use SELECT INTO FROM and INSERT INTO commonly. The watch of heel of the INTO when use SELECT INTO FROM must exist, that is to say its function is build an empty table first before derivative is occupied, in expressing the source medium data to guide newly-built sky is expressed again next, this duplicates with respect to what be equivalent to a watch (can not duplicate the information such as the index of the watch) . And the function of INSERT INTO is in inserting source data the watch that has existed, can use it to undertake data incorporates, if want to update the record that has existed, can use UPDATE.
SELECT * INTO Table2 FROM Table1
- - the watch structure of Table1 and Table2 is identical
INSERT INTO Table2 SELECT * FROM Table3
- - the watch structure of Table2 and Table3 is identical
Should be in between different compose database when undertaking counting occupying guiding deriving, the situation can become complex much. What should solve above all is the issue that how opens database of blame SQL Server.
Two function were offerred to the OLE DB Provider according to all sorts of type databases is opened and can operate these databases in SQL Server, these two function are OPENDATASOURCE and OPENROWSET. Their function is basically same, different point basically has at 2 o'clock.
(1) calls means to differ.
The parameter of OPENDATASOURCE has two, it is OLE DB Provider and join string respectively. Use OPENDATASOURCE is equivalent to citing only the database perhaps serves (to the database such as SQL Server, Oracle) . Want to cite among them data table or view, must be in OPENDATASOURCE(. . . ) junior travel cites.
Inquire through OPENDATASOURCE in SQL Server the Table1 in Access database Abc.mdb is expressed
Previous12 Next
Hot Tags: