根据库表表格生成DML语句

|{{code language="sql"}}insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1001', 'admin', 'Administrator', null, null, '1', '系统管理员');{{/code}}

63

|{{code language="sql"}}insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1002', 'stduser', 'Standard User', null, 'stduser@example.org', '1', '标准用户');{{/code}}

64

|{{code language="sql"}}insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1003', 'demo', 'Demo', '010-888888', 'demo@example.org', null, '演示用户');{{/code}}

author	version	line-number	content
		1	= VBA 脚本 =
		2
		3	{{code language="basic"}}
		4	' 获取文本内容作为一个值出现在 DML 语句中的拼写形式，入参支持多个单元格。
		5	Function dbval(textOrCells As Variant) As String
		6	If (TypeOf textOrCells Is Range) Then
		7	dbval = ""
		8	For Each c In textOrCells.Cells
		9	If (Len(dbval) > 0) Then
		10	dbval = dbval & ", "
		11	End If
		12	dbval = dbval & dbval(c.Text)
		13	Next
		14	Else
		15	dbval = Trim("" & textOrCells)
		16
		17	If (Len(dbval) <= 0) Then
		18	dbval = "null"
		19	Exit Function
		20	End If
		21
		22	dbval = Replace(dbval, "'", "' \|\| chr(" & Asc("'") & ") \|\| '")
		23	dbval = Replace(dbval, """", "' \|\| chr(" & Asc("""") & ") \|\| '")
		24	dbval = Replace(dbval, vbCr, "' \|\| chr(" & Asc(vbCr) & ") \|\| '")
		25	dbval = Replace(dbval, vbLf, "' \|\| chr(" & Asc(vbLf) & ") \|\| '")
		26
		27	dbval = "'" & dbval & "'"
		28	dbval = Replace(dbval, "'' \|\| ", "")
		29	dbval = Replace(dbval, " \|\| ''", "")
		30	End If
		31	End Function
		32
		33	' 获取数据行对应的 DML 插入语句。
		34	Function dbins(tableName As String, titleCells As Range, valueCells As Range) As String
		35	dbins = "insert into " & tableName & " (" & Replace(dbval(titleCells), "'", "") & ") values (" & dbval(valueCells) & ");"
		36	End Function
		37
		38
		39	' 获取数据行对应的 DML 删除语句。
		40	Function dbdel(tableName As String, primaryKey As String, primaryValue As String) As String
		41	primaryValue = dbval(primaryValue)
		42	dbdel = "delete from " & tableName & " where " & primaryKey & IIf(primaryValue = "null", " IS ", " = ") & primaryValue & ";"
		43	End Function
		44	{{/code}}
		45
		46	= 使用示例 =
		47
		48	详细内容见附件《[[USER_INFO.xlsm>>attach:USER_INFO.xlsm]]》
		49
		50	\|表名:\|(% colspan="7" %)USER_INFO
		51	\|序号:\|USER_ID\|ACCOUNT\|USERNAME\|TELNUM\|EMAIL\|STATUS\|REMARK
		52	\|1\|1001\|admin\|Administrator\|　\|　\|1\|系统管理员
		53	\|2\|1002\|stduser\|Standard User\|　\|[[stduser@example.org>>mailto:stduser@example.org]]\|1\|标准用户
		54	\|3\|1003\|demo\|Demo\|010-888888\|[[demo@example.org>>mailto:demo@example.org]]\|　\|演示用户
		55
		56	\|DELETE_STATEMENT
		57	\|{{code language="sql"}}delete from USER_INFO where USER_ID = '1001';{{/code}}
		58	\|{{code language="sql"}}delete from USER_INFO where USER_ID = '1002';{{/code}}
		59	\|{{code language="sql"}}delete from USER_INFO where USER_ID = '1003';{{/code}}
		60
		61	\|INSERT_STATEMENT
		62	\|{{code language="sql"}}insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1001', 'admin', 'Administrator', null, null, '1', '系统管理员');{{/code}}
		63	\|{{code language="sql"}}insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1002', 'stduser', 'Standard User', null, 'stduser@example.org', '1', '标准用户');{{/code}}
		64	\|{{code language="sql"}}insert into USER_INFO (USER_ID, ACCOUNT, USERNAME, TELNUM, EMAIL, STATUS, REMARK) values ('1003', 'demo', 'Demo', '010-888888', 'demo@example.org', null, '演示用户');{{/code}}

Wiki源代码根据库表表格生成DML语句