SQL Server and Access, Excel's data conversion is familiar with SQL Server 2000 database administrators know that DTS can be exported to the import of data, in fact, we can also use the Transact-SQL statement to import export operations. In the Transact-SQL statement, we mainly use the OpenDataSource function, the OpenRowSet function, for details about the function, please refer to the SQL online help. With the following method, SQL Server, Access, Excel data conversion can be easily implemented, and the detailed description is as follows: 1. SQL Server and Access data import export: Use the DTS wizard to migrate your Access data to SQL Server You can use these steps: 1 On the Tools menu in the SQL Server Enterprise Manager, select Data Transformation 2Services (Data Conversion Service), then select CZDIMPORT DATA. 3 Select Microsoft Access As the Source in the Choose A Data Source dialog box, then type your .mdb database (.mdb file extension) file name or by browse looking for this file. 4 In the Choose A Destination dialog box, select Microsoft Ole DB ProvIdeer for SQL Server, select the Database Server, and then click the necessary verification mode. 5 In Specify Table Copy, or Query dialog box, click Copy Tables. 6 In the SELECT SOURCE TABLES dialog box, click SELECT All. Next, complete.
Transact-SQL statement for import: 1. Query Access data in SQL Server: - =============================== ======================== SELECT * from OpenDataSource ('Microsoft.jet.OleDb.4.0', 'Data Source = "C: /Db.mdb "; User ID = admin; password = ') ... Table name ---------------------------------- -------------------------------------------------- ------------- 2. Import Access into SQL Server - ============================ ========================== run in SQL Server: Select * INTO newTable from OpenDataSource ('Microsoft.jet.OleDb.4.0', ' Data Source = "c: /db.mdb"; user ID = admin; password = ') ... Table name ------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------- ----------------------- 3. Insert the data in the SQL Server table into the Access table - =========== ============================================== in SQL Server: Insert INTO OpenDataSource ('microsoft.jet.oledb.4.0', 'data source = "c: /db.mdb"; user ID = admin; password =') ... Name (column name 1, column name 2) SELECT Column name 1, column name 2 from SQL Table Instance:
INSERT INTO OpenRowSet ('Microsoft.jet.OleDb.4.0', 'c: /db.mdb'; 'Admin'; '', Test) Select ID, Name From Test Insert Into OpenRowSet ('Microsoft.jet.OleDb.4.0 ',' C: /trade.mdb ';' admin ';' ', denoted Select * from sqltablename ------------------------- -------------------------------------------------- ---------------------- Data Import of SQL Server and Excel 1. Query Excel data in SQL Server: - ====== ================================================ SELECT * From OpenDataSource ('Microsoft.jet.OleDb.4.0', 'data source = "c: /book1.xls"; user ID = admin; password =; extended profits = excel 5.0') ... [Sheet1 $] below is The example of the query, which ques query Excel spreadsheet by the OLE DB provider for Jet.
Select * from OpenDataSource ('microsoft.jet.OleDb.4.0', 'data source = "c: /finance/account.xls"; user ID = admin; password =; extended Properties = Excel 5.0') ... xactions -------------------------------------------------- -------------------------------------------- 2, put Excel Data Import SQL Server: - ============================================ ============ Select * INTO newTable from OpenDataSource ('microsoft.jet.OleDb.4.0', 'data source = "c: /book1.xls"; user ID = admin; password =; Extended Properties = Excel 5.0 ') ... [Sheet1 $] Instance: Select * INTO NewTable from OpenDataSource (' Microsoft.jet.OleDb.4.0 ',' Data Source = "C: /FINance/account.xls"; user ID = Admin; Password =; extended Properties = Excel 5.0 ') ... xactions ---------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------- 3, guide the data queried in SQL Server into an Excel file - =================== ============================================= T-SQL code: Exec master..xp_cmdshell 'BCP library name. DBO. Name OUT C: /TEMP.XLS -C -Q -S "ServerName" -u "sa" -p "" "parameter: s is SQL Server name; u is user; P is password description: You can also export a variety of formats such as text files: exec master..xp_cmdshell 'bcp saletesttmp.dbo.cusaccount out c: /temp1.xls -c -q -s "
