SQL Server, access, Excel data conversion and precautions

Database Administrators familiar with SQL Server 2000 know that DTS can import and export data. In fact, we can also use Transact-SQL statements to import and export data.

In a Transact-SQL statement, we mainly use the OpenDataSource function and OpenRowset function. For more information about functions, see SQL online help. Using the following methods,

SQL Server, access, and Excel Data conversion can be easily implemented. The details are as follows:

I. Import and export data from SQL Server and access
General data import and export:
Use the DTS wizard to migrate your access data to SQL Server. You can use these steps:
○ 1. On the Tools menu in SQL Server Enterprise Manager, select data transformation
○ 2 Services (data conversion service), and then select czdimport data (import data ).
○ 3 select Microsoft Access as the source in the choose a data source dialog box, and then type your. mdb database (. MDB File 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 database server, and then click

Click the required verification method.
○ 5. In the specify table copy (specifying table copy) or query dialog box, click Copy tables ).
○ 6 in the select Source tables dialog box, click Select All ). Next, complete.

Use a Transact-SQL statement to import and export data:
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 to 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 data in the SQL Server table to the Access Table
Run 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

Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'c: \ trade. mdb'; 'admin'; '', table name)
Select *
From sqltablename

Ii. Import and export data from 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 properties = Excel 5.0 ')... [sheet1 $]

The following is an example of a query, which is provided by ole db for jet.ProgramQuery an Excel worksheet.
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 to 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

If the following method is used, change the parameter check to false before adoquery. Close. adoquery1.paramcheck: = false;
Adoquery1. SQL. Add ('select * into perimport from OpenDataSource ('Microsoft. Jet. oledb.4.0 ', ''data source = "C: \ book1.xls"; User

Id = admin; Password =; extended properties = Excel 8.0 '')... [sheet1 $] ');

3. Export the data queried in SQL Server into an Excel file
T-SQLCode:
Exec master.. xp_mongoshell 'bcp database name. DBO. Table name out c: \ temp.xls-C-Q-s "servername"-U "sa"-P ""'
Parameter: s indicates the SQL server name, u indicates the user, and P indicates the password.
Note: You can also export text files and other formats.

Instance: exec master .. xp_mongoshell 'bcp saletesttmp. DBO. cusaccount out c: \ temp1.xls-C-Q-s "pmserver"-U "sa"-P "sa "'

Exec master .. xp_mongoshell 'bcp "select au_fname, au_lname from pubs .. authors order by au_lname" queryout c: \ authors.xls-C

-Sservername-USA-ppassword'

Use ADO to export the Excel file code in VB6:
Dim cn as new ADODB. Connection
CN. Open "driver = {SQL Server}; server = websvr; database = webmis; uid = sa; WD = 123 ;"
Cn.exe cute "master .. xp_mongoshell 'bcp" select col1, col2 from database name. DBO. Table Name "queryout E: \ dt.xls-C-sservername-USA-

Ppassword '"

4. insert data to 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 (1, 2, 3)

T-SQL code:
Insert
OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Extended properties = Excel 8.0; Data Source = c: \ training \ inventur.xls ')... [filiale1 $]
(Bestand, Produkt) values (20, 'test ')

Summary: with the above statements, we can easily convert data in SQL Server, access, and Excel spreadsheet software, which provides us with great convenience!
========================================================== ========================================================

What should I pay attention to when changing access to SQL?

 
After the database is imported, fields that need to be automatically added must be overwritten, and the length of all numeric types must be increased. It is best to use decimal.

All default values are lost. It mainly belongs to the numeric type and date type.

All now (), time (), date () must be changed to getdate ().

Change all datediff ('D', time1, time2) to datediff (day, time1, time2)

Some types of true/false may not be available and must be changed to 1/0.

The remarks type must be cast (column as varchar.

The value of cursortype must be changed to 1, that is, when the database is opened, the first numeric parameter must be set to 1. Otherwise, the record may be incomplete.

Change isnull (rowname) to rowname = NULL

When the automatic numbering type in the ACCESS database is converted, SQL server does not set it to the automatic numbering type. We need to add the identity in the SQL creation statement to indicate the automatic numbering!

During conversion, SQL Server defaults to the smalldatetime type for date-related fields. We recommend that you change it to the datetime type because the datetime type has a better range than the smalldatetime type.

Large. Sometimes, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful.

The SQL statements used to perform operations on these two databases are not the same. For example, when you delete a record on an Access database, use the following statement: "delete * from user where id = 10 ".

The server database is deleted using: "delete user where id = 10 ".

Date functions are different. functions such as date () and time () can be used in Access database processing. However, functions such as datediff and dateadd can only be used in SQL Server database processing,

Instead, functions such as date () and time () cannot be used.

In the process of Access database, some VB functions can be used in SQL statements, such as the CSTR () function, but cannot be used in the process of SQL Server database.