Differences in SQL statements in SQL Server and Access (anonymous)

For Date Field fields
Access is expressed as: #1981 -28-12#
SQLSERVER2000 said: ' 1981-02-12 '
2,sql statement Differences, SELECT, update are similar to single-table operations,
However, the difference between the UPDATE statement when a multi-table operation compares access with the UPDATE statement in SQL Server:
Update statements for multiple tables are updated in SQL Server:
Update TAB1
SET A.name = B.name
From TAB1 a,tab2 b
Where a.id = b.id;
SQL statements of the same functionality in Access 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 a FROM clause, and all referenced tables are listed after the UPDATE keyword.
When updating a single table: all:
Update table1 Set ab= ', cd=444 where ....
3,delete statements
Delete in Access: Delete * from table1 where a>2 that is, just replace the select in the SELECT statement with the delete.
Sqlserve is: Delete from table1 where a>2 no * number
The difference between calculated fields after 4,as
You can do this in Access: Select A,sum (num) as kc_num,kc_num*num as all_kc_num the field after as as a database field to participate in the calculation.
In SQL Server, select A,sum (num) as kc_num,sum (num) *num as All_kc_num is not able to participate in calculations as a field after as as a database field.
5,[.] with [!] The difference
Multi-table Federated query in Access: Select Tab1!a as Tab1a,tab2!b tab2b from TAB1,TAB2, middle as can not.
Sqlserve: Select tab1.a as tab1a,tab2.b tab2b from TAB1,TAB2, middle as can not.
6, when Federated queries,
Multi-table Federated query in Access: ' Select A, a from (
Select a b from TAB1 where a>3 union select c,d from TAB2) GROUP by a B
The Sqlserve ' Select A, a from (
Select a, from TAB1 where a>3 union select c,d from TAB2) tmptable the group by A/b to add a virtual table tmptable, any table name. ---
7,access when upgrading to SQL Server,
You can import data using SQL Server's data import tool, but do the necessary processing.
Automatic numbering in Access does not automatically convert the automatic numbering in SQL, only to int, to manually change it to an identity field, seed 1, and remove all imports from SQL Server into the N-beginning field type, such as Nvarchar-> varchar. Change the date field that needs to have a second type to datatime type (SQL converts all dates into the smalldatetime type)
8,true and 1=1
Access uses where true to indicate that the condition is true,
SQL Server uses where 1=1 to indicate that the condition is true
9, determine the difference between the field value is empty
Normal null:
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])
The difference between a 10,sql statement and a substring
Access:mid (field, N1,[n2]), Left (field, N), right (field, N)
such as: Select Left (cs1,4) + '-' +cs2 as CS3
sqlserver:substring (expression, start, length)
such as: Select SUBSTRING (CS1, 1, 2) + substring (CS1, 4, 2) + '-' + CS2 as CS3

Differences in SQL statements in SQL Server and Access (anonymous)