ACCESS 使用INSERT INTO语句,一次性插入多行记录

INSERT INTO MessageUserAssociation (MessageID, UserID) select * from (
SELECT '13970640000011186000' as MessageID , 2 as UserID from UserInfo
UNION ALL
SELECT '13970640000011186000', 3 from UserInfo
UNION ALL
SELECT '13970640000011186000', 4 from UserInfo )

说明1: ACCESS 不支持以下格式的语句:

INSERT INTO MessageUserAssociation (MessageID, UserID)
VALUES 
('13970640000011186000', 2),
('13970640000011186000', 3),
('13970640000011186000', 4);

说明2:在UNION时,返回的结果集的字段,由第一个SELECT语句中的字段名决定,后面的SELECT 语句,将丧失对字段名命名的权利,所以写不写都无所谓了.比如上面语句中返回两个字段,第一个是MessageID 第二个是 UserID 

SELECT '13970640000011186000' as MessageID , 2 as UserID from UserInfo
UNION ALL
SELECT '13970640000011186000', 3 from UserInfo
UNION ALL
SELECT '13970640000011186000', 4 from UserInfo

说明3: union结果集的字段名称,应与INSERT INTO对象的字段列表相同与对应.

INSERT INTO MessageUserAssociation (MessageID, UserID) select * from (
SELECT '13970640000011186000' as MessageID , 2 as UserID 

说明4:  SELECT '13970640000011186000', 3 from UserInfo    必须 from 一个实际存在的表,但是不用担心,这里种情况不会对原表造成修改,只当它是一个工具表即可.

在VBA下拼接完成后,再执行这条SQL语句即可

        Dim arr As Variant, CCID As Long
        arr = Split(sCC, ";")
        iSQL = "insert into MessageUserAssociation ( MessageID, UserID ) select * from ( "
        For Each temp In arr
            iSQL = iSQL & "select " & M1.QuoStr(MID) & " as MessageID," & GetUserIDByName(temp) & " as UserID From UserInfo  union all "
        Next
        iSQL = Left(iSQL, Len(iSQL) - 11) & ")"  'len(" union all ")=11
        
        CurrentDb.Execute iSQL