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

This article mainly introduces "what are the precautions for ACCESS to SQLSERVER database". In daily operation, I believe many people have doubts about what are the precautions for ACCESS to SQLSERVER database. Xiaobian consulted all kinds of information and sorted out simple and easy to use operation methods. I hope to answer the doubts of "what are the precautions for ACCESS to SQLSERVER database"! Next, please follow the small series to learn together!

For example:

1, for date field fields

Access is represented as #1981-28- 12

SQLSERVER2000 is represented as: '1981-02-12'

2,SQL statement difference, select ,update are similar when operating on a single table,

However, the difference between the update statement in multi-table operation ACCESS and the Update statement in SQLSERVER:

Update statement for updating multiple tables in SQLSERVER:

Update Tab1

SET a.Name = b.Name FROM Tab1 a,Tab2 b Where a.ID = b.ID;

SQL statements of the same function should be in ACCESS

Update Tab1 a,Tab2 b SET a.Name = b.Name Where a.ID = b.ID;

That is, the Update statement in ACCESS has no FROM clause, and all referenced tables are listed after the Update keyword.

When updating a single table: all are:

Update table1 set ab='12',cd=444 where ....

3,delete statement

When deleting in access, use:delete * from table1 where a>2, that is, as long as the select statement is replaced by delete.

Delete from table1 where a>2 means there is no *

4, the difference between the calculated fields after as

In access, select a,sum(num) as kc_num,kc_num*num as all_kc_num means that the field after AS can be used as a database field to participate in the calculation.

sqlserver: select a,sum(num) as kc_num,sum(num)*num as all_kc_num That is, the field after AS cannot be used as a database field to participate in the calculation.

5，[.] with [!] the difference between

For multi-table joint query in access: select tab1! a as tab1a,tab2! b tab2b from tab1,tab2 , the middle AS can be omitted.

In sqlserve: select tab1.a as tab1a, tab2.b tab2b from tab1,tab2 , the middle AS can be omitted.

6. In joint query,

Select a,b from()

select a,b from tab1 where a>3 union select c,d from tab2 ) group by a,b

sqlserve 'select a,b from(

select a,b from tab1 where a>3 union select c,d from tab2 ) tmptable group by a,b i.e. add a virtual table tmptable, table name arbitrary. ---

7, access upgrade to sqlserver,

You can import data with sqlserver's data import tool, but you have to do the necessary processing.

The automatic number in access will not automatically convert the automatic number in SQL. It can only be converted to int type. To manually change it to an identification field, the seed is 1. Remove the n of all imported field types starting with n converted by sqlserver, such as nvarchar->varchar. Change the date field that needs to have a second type to datatime type (SQL will convert all date openings to smalldatetime type).

8,true and 1=1

access where true indicates that the condition is true,

sqlserver where 1=1 indicates that the condition is true

9. Determine the difference between field values that are empty

Normal Empty:

Access is the same as sql server where code is null or where code is nol null

Condition empty:

Access: iif([num] is null,0,[num]) or iif([num] is null,[num1],[num])

SQL Server: isnull([num], 0) or isnull([num],[num1])

10,SQL statement takes the difference between 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

Additional:

Access is different from SQL2000 SQL statements

For example, now() must be changed to getdate() in SQL2000

There are also keywords must be added [], such as ACCESS field name with name SQL20000 must be added [name] or error

database connection word reconfiguration

1. After access is converted to sql database, it is necessary to establish keywords and increment settings for each table. Some data types need to be redefined.

2. The now() function is acceptable, but getdate () is required during date comparison.

3. The key word needs to be added []

4. Single and double quotes require conversion

5. Follow standard sql definitions (the most critical one)

Check out MS SQL Server Books Online.

1. The automatic numbering type in ACCESS's database is not set to automatic numbering type by SQL server when it is converted. We need to add identity to SQL creation statement to indicate automatic numbering!

2. SQL SERVER defaults to smalldatetime when converting date-related fields. It's better to change it to datetime because datetime has a larger scope than smalldatetime. I encountered this situation where the conversion failed with smalldatetime and succeeded with datetime.

3. SQL statements for operating on these two databases are not all the same. For example, delete records in ACCESS database with: "delete * from user where id=10", while delete records in SQL SERVER database with: "delete user where id=10".

4. Date functions are different. In the processing of ACCESS database, date(), time() and other functions can be used, but for

In SQL SERVER database processing, only datediff,dateadd and other functions can be used, and date(), time() and other functions cannot be used.

5. In the ACCESS database processing,sql statements can directly use some VB functions, such as cstr() function, while SQL SERVER database processing, but can not be used.

At this point, on the "ACCESS to SQLSERVER database matters needing attention what" the study is over, I hope to be able to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.