Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to exchange data between SQL SERVER, ACCESS and EXCEL". In daily operation, I believe many people have doubts about how to exchange data between SQL SERVER, ACCESS and EXCEL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to exchange data between SQL SERVER and ACCESS and EXCEL". Next, please follow the editor to study!

Database administrators who are familiar with SQLSERVER2000 all know that its DTS can import and export data. in fact, we can also use Transact-SQL statements for import and export operations. In the Transact-SQL statement, we mainly use OpenDataSource function and OPENROWSET function. For a detailed description of the function, please refer to the SQL online help. Using the following methods, you can easily achieve SQLSERVER, ACCESS, EXCEL data conversion, details are as follows: 1. SQLSERVER and ACCESS data import and export regular data import and export: use the DTS wizard to migrate your Access data to SQLServer, you can use these steps: ○ 1 on the Tools (tools) menu in SQLSERVER Enterprise Manager, select DataTransformation ○ 2Services (data conversion Service) Then select czdImportData (Import data). ○ 3 Select MicrosoftAccessastheSource in the ChooseaDataSource (Select data Source) dialog box, and then type the file name of your .mdb database (.mdb file extension) or browse for the file. ○ 4 in the ChooseaDestination dialog box, select MicrosoftOLE DBProviderforSQL Server, select the database server, and then click the necessary authentication method. ○ 5 Click Copytables (copy Table) in the SpecifyTableCopy (specify Table copy) or Query (query) dialog box. ○ 6 in the SelectSourceTables (Select Source Table) dialog box, click SelectAll (all selected). Next step, done. Transact-SQL statement for import and export: 1. Query access data in SQLSERVER: SELECT*FROMOpenDataSource ('Microsoft.Jet.OLEDB.4.0','DataSource= "c:\ DB.mdb"; UserID=Admin;Password='). Table name 2. Import access into SQLserver and run it in SQLSERVER: SELECT*INTOnewtableFROMOPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','DataSource= "c:\ DB.mdb"; UserID=Admin;Password='). Table name 3. Insert the data from the SQLSERVER table into the Access table and run it in SQLSERVER: insertintoOpenDataSource ('Microsoft.Jet.OLEDB.4.0','DataSource= "c:\ DB.mdb"; UserID=Admin;Password='). Table name (column name 1, column name 2) select column name 1, column name 2fromsql table instance: insertintoOPENROWSET ('Microsoft.Jet.OLEDB.4.0','C:\ db.mdb';'admin';'',Test) selectid,namefromTest INSERTINTOOPENROWSET (' Microsoft.Jet.OLEDB.4.0','c:\ trade.mdb';'admin' '', table name) SELECT*FROMsqltablename II, SQLSERVER and EXCEL data import and export 1. Query Excel data in SQLSERVER: SELECT*FROMOpenDataSource ('Microsoft.Jet.OLEDB.4.0','DataSource= "c:\ book1.xls"; UserID=Admin;Password=;Extendedproperties=Excel5.0'). [Sheet1 $] is an example of a query that queries Excel spreadsheets through the OLEDB provider for Jet. SELECT*FROMOpenDataSource ('Microsoft.Jet.OLEDB.4.0','DataSource= "c:\ Finance\ account.xls"; UserID=Admin;Password=;Extendedproperties=Excel5.0')... xactions 2. Import Excel data into SQLserver: SELECT*intonewtableFROMOpenDataSource (' Microsoft.Jet.OLEDB.4.0','DataSource= "c:\ book1.xls"; UserID=Admin;Password=;Extendedproperties=Excel5.0')... [Sheet1 $] example: SELECT*intonewtableFROMOpenDataSource ('Microsoft.Jet.OLEDB.4.0','DataSource= "c:\ Finance\ account.xls"; UserID=Admin;Password=) Extendedproperties=Excel5.0'). Xactions 3. Import the data queried in SQLSERVER into an Excel file T-SQL code: EXECmaster..xp_cmdshell'bcp library name .dbo. Table name outc:\ Temp.xls-c-q-S "servername"-U "sa"-P "" Parameter: s is the SQL server name; U is the user P is the password description: you can also export text files and other examples in various formats: EXECmaster..xp_cmdshell'bcpsaletesttmp.dbo.CusAccountoutc:\ temp1.xls-c-q-S "pmserver"-U "sa"-P "sa" 'EXECmaster..xp_cmdshell'bcp "SELECTau_fname,au_lnameFROMpubs..authorsORDERBYau_lname" queryoutC:\ authors.xls-c-Sservername-Usa-Ppassword' apply ADO in VB6 to export EXCEL file code: DimcnAsNewADODB.Connection cn.open "Driver= {SQLServer}; Server=WEBSVR;DataBase=WebMis UID=sa;WD=123; "cn.execute" master..xp_cmdshell'bcp "SELECTcol1,col2FROM library name. DBO. Table name "queryoutE:\ DT.xls-c-Sservername-Usa-Ppassword'" 4. Insert data into Excel in SQLSERVER: insertintoOpenDataSource ('Microsoft.Jet.OLEDB.4.0','DataSource= "c:\ Temp.xls"; UserID=Admin;Password=;Extendedproperties=Excel5.0'). Table1 (A1 Magi A2meme A3) values (1m 2je 3) T-SQL Code: INSERTINTO OPENDATASOURCE (' Microsoft.JET.OLEDB.4.0','ExtendedProperties=Excel8.0 Datasource=C:\ training\ inventur.xls')... [Filiale1 $] (bestand,produkt) VALUES (20 minutes exchange Test') this is the end of the study on "how to exchange data between SQL SERVER, ACCESS and EXCEL". I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.