Syntax comparison between access and sql server

This article briefly summarizes the syntactic differences between access and sql server. I hope it will be helpful to everyone.

1. Differentiated functions and solutions

The functions in the solution shown below are defined in the method of the TAdoConn class in the untDataBase unit.

2. Database functions and keyword lists that are partially the same between Access and SQLSERVER

1. Functions

2. Keywords

3. The difference in statement syntax between Access and statement SqlServer

1. Inser Into…..Select…From statement:

The following statement in ACCESS

Insert INTO

PubSubJectAccCopys(Copy_id,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index) (Select 200201, Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index FROM PubSubJectAcc Where PubSubJectAcc.co_type='03')

中后"(select 200201******.co_Type=' 03')" must be removed to execute, as follows:

Insert INTO

PubSubJectAccCopys(Copy_id,Acc_id,Acc_Pid,Acc_name,acc_short, Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index) Select 200201,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index FROM PubSubJectAcc Where PubSubJectAcc.co_type='03'

In SQL SERVER Medium All are OK

2. Inner Join statement 1

StrSql:='select a.user_id,a.user_opcode,b.copy_name from sysuser a inner join (syscopysuser c inner join syscopys b on c .copy_id=c.copy_id) on a.user_id=c.user_id where

a.user_opcode=''' EdtUserOpCode.text ''' And copy_name=''' Tmpcopyname '''';

should be changed to

StrSql:='select a.user_id,a.user_opcode,b.copy_name from sysuser a inner join (syscopysuser c inner join syscopys b on c.copy_id=d.copy_id) on a.user_id=c.user_id where

a.user_opcode=''' EdtUserOpCode.text ''' And copy_name=''' Tmpcopyname '''';

Retrieval of this line of code Condition error: C.copy_id=C.Copy_id should be changed to c.copy_id=d.copy_id

Note: Both writing methods can run in SQL-SERVER, but c.copy_id=C.copy_id is Cannot run in ACCESS

3. Inner Join statement 2

StrSql:='select copy_year,copy_name,a.copy_id from SysCopys a inner join SysCopysUser b on a.curcopy_flag=1 and a. copy_id=b.copy_id where b.user_id=' '''' TmpPubUserID '''';

This is

StrSql:='select copy_year,copy_name,a.copy_id from SysCopys a inner join SysCopysUser b on a.copy_id=b.copy_id where a.curcopy_flag=''1'' and b.user_id=' '''' TmpPubUserID '''';

Note: Both writing methods are It can run in SQL-SERVER, but the first one cannot run in ACCESS

4. Inner Join statement 3

The following statement can be executed in SQl server

'Select distinct sysoption.opti_id,sysoption.opti_name,sysoption.opti_code,sysroleoption.opti_sort From sysoption inner join sysroleoption ON sysoption.opti_id=sysroleoption.opti_id AND sysroleoption.role_id=:roleid'

But not in ACCESS, only

'Select distinct sysoption.opti_id,sysoption.opti_name,sysoption.opti_code,sysroleoption.opti_sort From sysoption inner join sysroleoption ON sysoption.opti_id=sysroleoption.opti_id Where sysroleoption.role_id=:roleid'

5. Update statement

Can be executed in Sql SerVer but not in Access

'Update sysuserrole SET sysuserrole.role_sort = (Select sysrole.role_sort FROM sysrole Where sysuserrole.role_id = sysrole.role_id and sysuserrole.user_id='01')'

6. Date comparison

Used in SQL SERVER

StrSql:='select copy_year,Start_month,Cur_month,Start_Flag,Start_date, End_date '

'From SysCopys '

'where copy_id=''' LoginCopyID ''' '

'and start_date

'and end_date>=''' datetostr(LoginDate) '''';

Used in ACCESS

StrSql:='select copy_year,Start_month,Cur_month ,Start_Flag,Start_date,End_date '

'From SysCopys '

'where copy_id=''' LoginCopyID ''' '

'and start_date

'and end_date>=#' datetostr(LoginDate) '#'

Refer to the 10th function "GetDateStr" above

7. Maximum value acquisition statement

StrSql:='insert into sysRoleOption '

'select ''' fidRoleId ''' as Role_ID,opti_id,'

'convert(numeric,opti_id)-(convert(numeric,opti_parentid)*100) ' MaxOptiSort

' as opti_Sort from sysoption where opti_parentid= '''

PCoTypeID(self.trvRoles.Selected.data)^.StrCoTypeID

''' and opti_bottom=''1' '''';

Change for

StrSql:='insert into sysRoleOption '

'select ''' fidRoleId ''' as Role_ID,opti_id,'

'opti_id-opti_parentid*100 ' MaxOptiSort

' as opti_sort from sysoption where opti_parentid='''

PCoTypeID(self.trvRoles.Selected.data)^.StrCoTypeID

''' and opti_bottom='' 1' ''''

Note: Both writing methods can be run in SQL-SERVER, but the first one cannot be run in ACCESS

But consider that Null values ​​and statements will appear For versatility, you can use the 07th function "GetNullStr" and the 09th function "GetConvertStr" above to complete the conversion of strings to numbers, null values ​​and 0 numbers: refer to the GetNextNumStr code.

1. As cannot be omitted in Sql
2. Only one Sql can be executed at a time
3. There are no substring, cast and other functions
4. Strictly distinguish between integer and character types in SQL
5. No stored procedures or triggers
6.! = is replaced with
7. Add # signs on both sides of the time string
8. In the sql statement with parameters, replace @ with ? sign

Related recommendations: access database tutorial

The above is the detailed content of Syntax comparison between access and sql server. For more information, please follow other related articles on the PHP Chinese website!