Access and sqlserver Data Conversion

1. When the ID in MDB reaches access, it will become a normal int, and you need to reset it to "ID ".
2. Change now () in the SQL statement to getdate ().
3. In SQL statements, replace '*' with the character string '% '.
4. The data type "yes/no" in the Access Table is bit (not null) in the SQL Server table)
5. Access query uses cdate to construct a date. In SQL server, it is convert (datetime ,...)
6. in SQL Server, my experience may be: '2017-5-29 '=
Convert (datetime, '2017-5-29 ')
1. After the database is imported, it is recommended that fields be automatically added and the length of all numeric types be increased.
Decimal.
2. All default values are lost. It mainly belongs to the numeric type and date type.
3. Change all now (), time (), date () to getdate ().
4. Change all datediff ('D', time1, time2) to datediff (day, time1, time2)
5. Some values of the true/false type may not be used. The value must be 1/0.
6. Cast (column as varchar) is used for the remarks type.
7. Change cursortype to 1, that is, when the database is opened, the first numeric parameter must be set to 1. Otherwise, the record may
Incomplete display.
8. Change isnull (rowname) to rowname = NULL
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.
6: it is best to use "[" and "]" to pack it wherever possible and with system keywords.
To avoid running errors during transplantation.