Object Reference
- Classes
- Command Interface
- External
- General
- System Core
- System Library
xf_DataList
xf_Scripterxf_ScripterSQL Script Generation and Processing
Public Function CreateInsertStatement (
dbs As DAO.Database,
TableName As String,
Optional WhereOrOrderByClause As String = "" ,
Optional SkipAutonumberInsert As Boolean = True,
Optional MssqlMode As Boolean = False,
Optional ColumnList As String = ""
) As String
Generate insert statements to create data in table TableName
dbs as DAO.Database
The database object to generate the script against
TableName as String
The name of the table to create
Optional WhereOrOrderByClause as String = ""
A full SQL WHERE and/or ORDER BY clause restricting or ordering the dataset used to generate the INSERT statements
Optional SkipAutonumberInsert as Boolean = True
Omit autonumber columns from script generation
Optional MssqlMode as Boolean = False
Generate SQL compatible with MS SQL Server rather then MS Access
Optional ColumnList as String = ""
Restrict the columns generated to this list
Call Template:
Dim s As String
s = xf.Scripter.CreateInsertStatement(dbs, TableName)
s = xf.Scripter.CreateInsertStatement(dbs, TableName, WhereOrOrderByClause, SkipAutonumberInsert, MssqlMode, ColumnList)
See Also:
Example 1:
CreateInsertStatement "tblKeyNames","WHERE keyname='ReconStatusStr'"
returning three rows as an INSERT statements ...
INSERT INTO tblKeyNames (KeyName,KeyVal,KeyValDesc,KeyValDescShort,Grp1,Grp2,Grp3,Int1,Int2,SortOrder)
VALUES ('ReconStatusStr','J','J','',0,0,0,0,0,0)
go
INSERT INTO tblKeyNames (KeyName,KeyVal,KeyValDesc,KeyValDescShort,Grp1,Grp2,Grp3,Int1,Int2,SortOrder)
VALUES ('ReconStatusStr','K','K','',0,0,0,0,0,0)
go
INSERT INTO tblKeyNames (KeyName,KeyVal,KeyValDesc,KeyValDescShort,Grp1,Grp2,Grp3,Int1,Int2,SortOrder)
VALUES ('ReconStatusStr','L','L','',0,0,0,0,0,0)
go
Note that the preferred format for MSSQL shown below does not work in Access (the UNION operator is not accepted)
INSERT INTO tblKeyNames (KeyName,KeyVal,KeyValDesc,KeyValDescShort,Grp1,Grp2,Grp3,Int1,Int2,SortOrder)
SELECT 'ReconStatusStr','J','J','',0,0,0,0,0,0 FROM xf_tblDummy
UNION SELECT 'ReconStatusStr','K','K','',0,0,0,0,0,0 FROM xf_tblDummy
UNION SELECT 'ReconStatusStr','L','L','',0,0,0,0,0,0 FROM xf_tblDummy