Precautions for transferring ACCESS to SQLSERVER Database

For example, the following...
1. For the Date Field
Access: #1981-28-12 #
SQLSERVER2000: '2017-02-12'
2. Differences in SQL statements: select and update are similar in operations on a single table,
However, the difference between the update statement during multi-Table operations: Comparison between the ACCESS statement and the Update statement in SQLSERVER:
Update statements for updating multiple tables in SQLSERVER:
Update Tab1
SET a. Name = B. Name FROM Tab1 a, Tab2 B Where a. ID = B. ID;
The SQL statement with the same function should be
Update Tab1 a, Tab2 B SET a. Name = B. Name Where a. ID = B. ID;
That is, the Update statement in ACCESS does not have the FROM clause. All referenced tables are listed after the Update keyword.
When updating a single table:
Update table1 set AB = '12', cd = 444 where ....
3. delete statement
Delete * from table1 where a> 2 is used when access is deleted. That is, you only need to replace select in the select statement with delete.
In sqlserve, "delete from table1 where a> 2" means no "*".
4. Differences between calculated fields after
In access, select a, sum (num) as kc_num, and kc_num * num as all_kc_num can be used AS a database field for calculation.
In SQL Server, select a, sum (num) as kc_num, sum (num) * num as all_kc_num cannot be used AS a database field for calculation.
5, [.] and [!] Difference
Select tab1! A as tab1a, tab2! B tab2b from tab1, tab2, the intermediate AS can be left blank.
In sqlserve: select tab1.a as tab1a, tab2. B tab2b from tab1, tab2, the intermediate AS can not.
6. During joint query,
Multi-table joint query in access: 'select a, B from (
Select a, B from tab1 where a> 3 union select c, d from tab2) group by a, B
In sqlserve, 'select a, B from (
Select a, B from tab1 where a> 3 union select c, d from tab2) tmptable group by a, B is to add a virtual table tmptable, the table name is arbitrary. ---
7. When access is upgraded to sqlserver,
You can use SQL Server's data import tool to import data, but necessary processing is required.
The automatic number in access does not automatically convert the automatic number in SQL. It can only be converted to the int type. You need to manually change it to the ID field, and the seed is 1, remove all the n types of fields whose imports have been converted to "n" by sqlserver, such as nvarchar-> varchar. change the Date Field of the second type to the datatime type (SQL converts all the dates to the smalldatetime type)
8, true and 1 = 1
Access uses where true to indicate that the condition is true,
Sqlserver uses where 1 = 1 to indicate that the condition is true.
9. determine the difference between Null Field Values
Normal blank:
Access is the same as SQL server. where code is null or where code is nol null
Condition NULL:
Access: iif ([num] is null, 0, [num]) or iif ([num] is null, [num1], [num])
SQLServer: isnull ([num], 0) or isnull ([num], [num1])
10. Differences between SQL statements and substrings
Access: MID (field, n1, [n2]), LEFT (field, n), RIGHT (field, n)
For example, select left (cs1, 4) + '-' + cs2 as cs3
SQLServer: SUBSTRING (expression, start, length)
For example, select substring (cs1, 1, 2) + substring (cs1, 4, 2) + '-' + cs2 as cs3

Supplement:
ACCESS differs from SQL2000 SQL statements.
For example, now () must be changed to getdate () in SQL2000 ()
Also, the keyword must be added with []. For example, the field name in ACCESS must be added with name SQL20000. Otherwise, an error occurs.
Database Connection word Reconfiguration
1. After access is converted to an SQL database, you need to create keywords for each table and set the Incremental Quantity. Some data types need to be redefined.
2. The now () function is acceptable, but getdate () must be used in the date comparison process ()
3. Add [] for reserved words
4. Single double quotes need to be changed
5. Follow the standard SQL definition (the most critical one)
Check out MSSQLServer books online.
1. 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!
2. 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 larger range than the smalldatetime type. In this case, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful.
3. the SQL statements used to operate these two databases are not all the same. For example, when you delete a record in an ACCESS database, use: "delete * from user where id = 10 ", to delete an SQL SERVER database, use: "delete user where id = 10 ".
4. date functions are different. In the ACCESS database processing, functions such as date () and time () can be used,
In SQL SERVER database processing, only functions such as datediff and dateadd can be used, instead of functions such as date () and time.
5. For ACCESS database processing, some VB functions can be used in SQL statements, such as cstr () functions, but not in SQL SERVER database processing.