Data conversion for SQL SERVER with Access, Excel

SQL Server with Access, Excel Data transformation The database administrator who is familiar with SQL Server 2000 knows that its DTS can import and export data, in fact, we can also use Transact-SQL statements for import and export operations. In Transact-SQL statements, we mainly use the OPENDATASOURCE function, the OPENROWSET function, and a detailed description of the function, refer to the SQL online Help. SQL Server, Access, and Excel data transformations can be easily implemented using the following methods: First, data import export of SQL Server and Access General data Import Export: Use the DTS Wizard to migrate your ACCESS data to SQL Server, you can use these steps: 1 in SQL Server Enterprise Manager, on the Tools menu, select Data Transformation 2Services, and then select Czdimport Data (import). 3 in the Choose a Data Source dialog box, select Microsoft Access as the source, and then type the file name of your. mdb database (. mdb file name extension) or browse to find the file. 4 in the Choose a Destination (Select target) dialog box, select Microsoft OLE DB Prov ider for SQL server, select the database server, and then click the necessary authentication method. 5 in the Specify Table copy (specify table copy) or Query dialog box, click Copy Tables (copy table). 6 in the Select Source Tables dialog box, click Select All (selected All). Next, complete. Transact-SQL statements for import and export: 1. Querying 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 to SQL Server--===========================       =========================== runs 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. Add the SQL Data in the SERVER table is inserted into the Access table--====================================================== runs in SQL Server: INSERT INTO OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:\DB.mdb '; User id=admin; Password= ') ... Table 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 inserts into OPENROWSET (' microsoft.jet.oledb.4.0 ', ' C:\trade.mdb '; ' Admin '; ", table name) SELECT * from Sqltablename--------------------------------------------------------------------------------- ----------------ii. Data import and export of SQL SERVER and Excel 1, in SQL Query Excel data in server:--====================================================== SELECT * from OpenDataSource (' Microsoft.Jet.OLEDB.4.0 ', ' Data source= ' C:\Book1.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... [sheet1$] Below is an example of a query that queries an EXCEL spreadsheet by using 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. Import Excel data into 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.XL S "; User id=admin; password=; Extended properties=excel 5.0 ') ... xactions-------------------------------------------------------------------------------------------------3. Convert the data queried in SQL Server into an Excel file--===== ================================================= T-SQL code: EXEC Master. xp_cmdshell ' bcp library name. dbo. Table name out c:\Temp.xls-c-q-s "servername"-U "sa"-P "" parameter: S is the SQL Server name; you are the user; P is the password description: You can also export a variety of format instances such as text files: E XEC Master. xp_cmdshell ' bcp saletesttmp.dbo.CusAccount out c:\temp1.xls-c-q-s "Pmserver"-u "sa"-P "sa" EXEC master. xp_cmdshell ' bcp ' select au_fname, au_lname from pubs.  Authors ORDER by au_lname "Queryout C: \ authors.xls-c-sservername-usa-ppassword ' Application of ADO in VB6 to export Excel file code. Dim cn as New ADODB. Connection cn.open "Driver={sql Server}; Server=websvr;database=webmis; Uid=sa; wd=123; "Cn.execute" Master. xp_cmdshell ' bcp ' select col1, col2 from library name. dbo. Table name "Queryout E:\DT.xls-c-sservername-usa-ppassword '"----------------- -------------------------------------------------------------------------------4. Insert data into Excel in SQL Server:--===== =============================================== = = INSERT INTO OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:\Temp.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... table1 (A1,A2,A3) VALUES (T-SQL code): INSERT into OpenDataSource (' Microsoft.Jet.OLEDB.4.0 ', ' Extended properties=excel 8.0;data source=c:\training\inventur.xls ') ...  [filiale1$] (Bestand, Produkt) VALUES (' Test ')--------------------------------------------------------------------------------------------- ----Summary: Using the above statement, we can easily convert the data in SQL SERVER, Access and Excel spreadsheet software, providing us with great convenience!

Data conversion for SQL SERVER with Access, Excel