SQL code to insert multiple rows in ms-access table?

If not, you'll probably get better performance if you insert rows using a recordset.

If not, you'll probably get better performance if you insert rows using a recordset: Set rs=db. OpenRecordset("tblSimulation", dbOpenDynaset) With rs . AddNew!

P=0! CfYear=2 ' And so on . Update End With.

I am connecting to the mdb from outside Access through ODBC. That's why I use SQL – Thierry Jul 31 '09 at 15:14 ODBC wouldn't affect this. (At least not that I know.

I'm always ready to learn about another quirk with ODBC. ) – Smandoli Aug 1 '09 at 21:18.

Not that I am aware of. And I have felt this pain before. The good news is that if you wrap the insert in a transaction and don't close the connection between each call (eg--pass the command as a parameter) then it is orders of magnitude quicker than open()->INSERT->close() is.

Use a code pattern something like: using (DbConnection conn = new OdbcConnection()) using (DbCommand cmd = conn.CreateCommand()) { cmd. CommandText = "INSERT INTO foo(bar) VALUES (@bar)"; DbParameter p = cmd.CreateParameter(); p. ParameterName = "@bar"; cmd.

CommandType = CommandType. Text; conn.Open(); using (DbTransaction tran = conn. BeginTransaction()) { cmd.

Transaction = tran; try { for (int I = 0; I.

When I try the SQL command "BEGIN TRANSACTION;" the ODBC driver return an error: Ilegal SQL-instruction. DELETE, INSERT, PROCEDURE, SELECT or UPDATE expected. So transactions don't work through ODBC?

– Thierry Jul 31 '09 at 15:21 Don't add them to your SQL command, us a transaction in your app. I'll update my answer. – Wyatt Barnett Jul 31 '09 at 15:27 Thanks.

Now it works. With 20.000 rows I got a speed gain of about 16%. – Thierry Jul 31 '09 at 15:37 -1 "Not that I am aware of" maybe but it is possible.

See my answer ;) – onedaywhen Jul 31 '09 at 6:38.

You can insert all the lines from that with ADO. Db = "C:\Docs\ltd. Mdb" Set cn = CreateObject("ADODB.

Connection") cn. Open "Provider = Microsoft.Jet.OLEDB.4.0; " & _ "Data Source =" & db sSQL = "INSERT INTO New (id,schedno) " _ & "SELECT id,schedno FROM new. Txt IN '' " _ & "'text;HDR=Yes;FMT=Delimited;database=C:\Docs\';" cn.

Execute sSQL.

The VALUES keyword is for single-record appends. You can insert multiple records from source_table using the SELECT keyword: INSERT INTO target_table ( field1, field2, .... ) SELECT field1, field2, .... FROM source_table; So if your values are coming from a table or query, you should be good. And if they aren't, maybe you can get them into a table?

Oh, I see Remou also reaches for the SELECT keyword. Using it to grab a text file is very interesting! – Smandoli Aug 1 '09 at 21:16.

You need a table that is guaranteed to contain at least one row (exactly one row is best, in which case you can omit the DISTINCT keyword). Here I'm using my standard issue auxiliary table Calendar. Within the derived table DT1, you must supply column correlation names (colloquially 'aliases') for the first appearance only: INSERT INTO tblSimulation (p, cfYear, cfLocation, Delta, Design, SigmaLoc, Sigma, SampleSize, Intercept) SELECT DT1.

P, DT1. CfYear, DT1. CfLocation, DT1.

Delta, DT1. Design, DT1. SigmaLoc, DT1.

Sigma, DT1. SampleSize, DT1. Intercept FROM ( SELECT DISTINCT 0 AS p, 2 AS cfYear, 8.3 AS cfLocation, 0 AS Delta, 1 AS Design, 0.5 AS SigmaLoc, 0.2 AS Sigma, 220 AS SampleSize, 3.4 AS Intercept FROM Calendar UNION ALL SELECT DISTINCT 0, 2.4, 7.8, 0, 1, 0.5, 0.2, 220, 3.4 FROM Calendar UNION ALL SELECT DISTINCT 0, 2.3, 5.9, 0, 1, 0.5, 0.2, 220, 3.4 FROM Calendar ) AS DT1; Now for the bad news: you can only create 49 UNION ALLs in a single query after which the Access database engine whines it is 'too complex'.

Dynamic SQL is to be avoided in favour of a stored procedure and anyhow the column correlation names in the derived table makes dynamic SQL a pain for this kind of construct. So here's a example using a proc with 49 * 9 = 441 parameters to create up to 49 rows in a single execution. Someone asked about performance.

Well, this proc creates 49 rows in around 100 milliseconds on my machine Schema: CREATE TABLE OneRowTable ( lock CHAR(1) DEFAULT 'x' NOT NULL PRIMARY KEY, CONSTRAINT OneRowTable__lock CHECK (lock = 'x') ) ; INSERT INTO OneRowTable (lock) VALUES ('x') ; CREATE TABLE tblSimulation ( p DECIMAL(19,4), cfYear DECIMAL(19,4), cfLocation DECIMAL(19,4), Delta DECIMAL(19,4), Design DECIMAL(19,4), SigmaLoc DECIMAL(19,4), Sigma DECIMAL(19,4), SampleSize DECIMAL(19,4), Intercept DECIMAL(19,4) ) ; CREATE PROCEDURE Add100Simulations ( :001_1 DECIMAL(19, 4) = NULL, :001_1 DEC97IMAL(19, 4) = NULL, :001_1 DEC9IMAL(19, 4) = NULL, :001_1 DEC46IMAL(19, 4) = NULL, :001_1 DEC95IMAL(19, 4) = NULL, :001_1 DEC44IMAL(19, 4) = NULL, :001_1 DEC4IMAL(19, 4) = NULL, :001_1 DECIMAL(19, 4) = NULL, :001_1 DEC89IMAL(19, 4) = NULL, :002_1 DECIMAL(19, 4) = NULL, :002_1 DEC97IMAL(19, 4) = NULL, :002_1 DEC9IMAL(19, 4) = NULL, :002_1 DEC46IMAL(19, 4) = NULL, :002_1 DEC95IMAL(19, 4) = NULL, :002_1 DEC44IMAL(19, 4) = NULL, :002_1 DEC4IMAL(19, 4) = NULL, :002_1 DECIMAL(19, 4) = NULL, :002_1 DEC89IMAL(19, 4) = NULL, :003_1 DECIMAL(19, 4) = NULL, :003_1 DEC97IMAL(19, 4) = NULL, :003_1 DEC9IMAL(19, 4) = NULL, :003_1 DEC46IMAL(19, 4) = NULL, :003_1 DEC95IMAL(19, 4) = NULL, :003_1 DEC44IMAL(19, 4) = NULL, :003_1 DEC4IMAL(19, 4) = NULL, :003_1 DECIMAL(19, 4) = NULL, :003_1 DEC89IMAL(19, 4) = NULL, :004_1 DECIMAL(19, 4) = NULL, :004_1 DEC97IMAL(19, 4) = NULL, :004_1 DEC9IMAL(19, 4) = NULL, :004_1 DEC46IMAL(19, 4) = NULL, :004_1 DEC95IMAL(19, 4) = NULL, :004_1 DEC44IMAL(19, 4) = NULL, :004_1 DEC4IMAL(19, 4) = NULL, :004_1 DECIMAL(19, 4) = NULL, :004_1 DEC89IMAL(19, 4) = NULL, :005_1 DECIMAL(19, 4) = NULL, :005_1 DEC97IMAL(19, 4) = NULL, :005_1 DEC9IMAL(19, 4) = NULL, :005_1 DEC46IMAL(19, 4) = NULL, :005_1 DEC95IMAL(19, 4) = NULL, :005_1 DEC44IMAL(19, 4) = NULL, :005_1 DEC4IMAL(19, 4) = NULL, :005_1 DECIMAL(19, 4) = NULL, :005_1 DEC89IMAL(19, 4) = NULL, :006_1 DECIMAL(19, 4) = NULL, :006_1 DEC97IMAL(19, 4) = NULL, :006_1 DEC9IMAL(19, 4) = NULL, :006_1 DEC46IMAL(19, 4) = NULL, :006_1 DEC95IMAL(19, 4) = NULL, :006_1 DEC44IMAL(19, 4) = NULL, :006_1 DEC4IMAL(19, 4) = NULL, :006_1 DECIMAL(19, 4) = NULL, :006_1 DEC89IMAL(19, 4) = NULL, :007_1 DECIMAL(19, 4) = NULL, :007_1 DEC97IMAL(19, 4) = NULL, :007_1 DEC9IMAL(19, 4) = NULL, :007_1 DEC46IMAL(19, 4) = NULL, :007_1 DEC95IMAL(19, 4) = NULL, :007_1 DEC44IMAL(19, 4) = NULL, :007_1 DEC4IMAL(19, 4) = NULL, :007_1 DECIMAL(19, 4) = NULL, :007_1 DEC89IMAL(19, 4) = NULL, :008_1 DECIMAL(19, 4) = NULL, :008_1 DEC97IMAL(19, 4) = NULL, :008_1 DEC9IMAL(19, 4) = NULL, :008_1 DEC46IMAL(19, 4) = NULL, :008_1 DEC95IMAL(19, 4) = NULL, :008_1 DEC44IMAL(19, 4) = NULL, :008_1 DEC4IMAL(19, 4) = NULL, :008_1 DECIMAL(19, 4) = NULL, :008_1 DEC89IMAL(19, 4) = NULL, :009_1 DECIMAL(19, 4) = NULL, :009_1 DEC97IMAL(19, 4) = NULL, :009_1 DEC9IMAL(19, 4) = NULL, :009_1 DEC46IMAL(19, 4) = NULL, :009_1 DEC95IMAL(19, 4) = NULL, :009_1 DEC44IMAL(19, 4) = NULL, :009_1 DEC4IMAL(19, 4) = NULL, :009_1 DECIMAL(19, 4) = NULL, :009_1 DEC89IMAL(19, 4) = NULL, :010_1 DECIMAL(19, 4) = NULL, :010_1 DEC97IMAL(19, 4) = NULL, :010_1 DEC9IMAL(19, 4) = NULL, :010_1 DEC46IMAL(19, 4) = NULL, :010_1 DEC95IMAL(19, 4) = NULL, :010_1 DEC44IMAL(19, 4) = NULL, :010_1 DEC4IMAL(19, 4) = NULL, :010_1 DECIMAL(19, 4) = NULL, :010_1 DEC89IMAL(19, 4) = NULL, :011_1 DECIMAL(19, 4) = NULL, :011_1 DEC97IMAL(19, 4) = NULL, :011_1 DEC9IMAL(19, 4) = NULL, :011_1 DEC46IMAL(19, 4) = NULL, :011_1 DEC95IMAL(19, 4) = NULL, :011_1 DEC44IMAL(19, 4) = NULL, :011_1 DEC4IMAL(19, 4) = NULL, :011_1 DECIMAL(19, 4) = NULL, :011_1 DEC89IMAL(19, 4) = NULL, :012_1 DECIMAL(19, 4) = NULL, :012_1 DEC97IMAL(19, 4) = NULL, :012_1 DEC9IMAL(19, 4) = NULL, :012_1 DEC46IMAL(19, 4) = NULL, :012_1 DEC95IMAL(19, 4) = NULL, :012_1 DEC44IMAL(19, 4) = NULL, :012_1 DEC4IMAL(19, 4) = NULL, :012_1 DECIMAL(19, 4) = NULL, :012_1 DEC89IMAL(19, 4) = NULL, :013_1 DECIMAL(19, 4) = NULL, :013_1 DEC97IMAL(19, 4) = NULL, :013_1 DEC9IMAL(19, 4) = NULL, :013_1 DEC46IMAL(19, 4) = NULL, :013_1 DEC95IMAL(19, 4) = NULL, :013_1 DEC44IMAL(19, 4) = NULL, :013_1 DEC4IMAL(19, 4) = NULL, :013_1 DECIMAL(19, 4) = NULL, :013_1 DEC89IMAL(19, 4) = NULL, :014_1 DECIMAL(19, 4) = NULL, :014_1 DEC97IMAL(19, 4) = NULL, :014_1 DEC9IMAL(19, 4) = NULL, :014_1 DEC46IMAL(19, 4) = NULL, :014_1 DEC95IMAL(19, 4) = NULL, :014_1 DEC44IMAL(19, 4) = NULL, :014_1 DEC4IMAL(19, 4) = NULL, :014_1 DECIMAL(19, 4) = NULL, :014_1 DEC89IMAL(19, 4) = NULL, :015_1 DECIMAL(19, 4) = NULL, :015_1 DEC97IMAL(19, 4) = NULL, :015_1 DEC9IMAL(19, 4) = NULL, :015_1 DEC46IMAL(19, 4) = NULL, :015_1 DEC95IMAL(19, 4) = NULL, :015_1 DEC44IMAL(19, 4) = NULL, :015_1 DEC4IMAL(19, 4) = NULL, :015_1 DECIMAL(19, 4) = NULL, :015_1 DEC89IMAL(19, 4) = NULL, :016_1 DECIMAL(19, 4) = NULL, :016_1 DEC97IMAL(19, 4) = NULL, :016_1 DEC9IMAL(19, 4) = NULL, :016_1 DEC46IMAL(19, 4) = NULL, :016_1 DEC95IMAL(19, 4) = NULL, :016_1 DEC44IMAL(19, 4) = NULL, :016_1 DEC4IMAL(19, 4) = NULL, :016_1 DECIMAL(19, 4) = NULL, :016_1 DEC89IMAL(19, 4) = NULL, :017_1 DECIMAL(19, 4) = NULL, :017_1 DEC97IMAL(19, 4) = NULL, :017_1 DEC9IMAL(19, 4) = NULL, :017_1 DEC46IMAL(19, 4) = NULL, :017_1 DEC95IMAL(19, 4) = NULL, :017_1 DEC44IMAL(19, 4) = NULL, :017_1 DEC4IMAL(19, 4) = NULL, :017_1 DECIMAL(19, 4) = NULL, :017_1 DEC89IMAL(19, 4) = NULL, :018_1 DECIMAL(19, 4) = NULL, :018_1 DEC97IMAL(19, 4) = NULL, :018_1 DEC9IMAL(19, 4) = NULL, :018_1 DEC46IMAL(19, 4) = NULL, :018_1 DEC95IMAL(19, 4) = NULL, :018_1 DEC44IMAL(19, 4) = NULL, :018_1 DEC4IMAL(19, 4) = NULL, :018_1 DECIMAL(19, 4) = NULL, :018_1 DEC89IMAL(19, 4) = NULL, :019_1 DECIMAL(19, 4) = NULL, :019_1 DEC97IMAL(19, 4) = NULL, :019_1 DEC9IMAL(19, 4) = NULL, :019_1 DEC46IMAL(19, 4) = NULL, :019_1 DEC95IMAL(19, 4) = NULL, :019_1 DEC44IMAL(19, 4) = NULL, :019_1 DEC4IMAL(19, 4) = NULL, :019_1 DECIMAL(19, 4) = NULL, :019_1 DEC89IMAL(19, 4) = NULL, :020_1 DECIMAL(19, 4) = NULL, :020_1 DEC97IMAL(19, 4) = NULL, :020_1 DEC9IMAL(19, 4) = NULL, :020_1 DEC46IMAL(19, 4) = NULL, :020_1 DEC95IMAL(19, 4) = NULL, :020_1 DEC44IMAL(19, 4) = NULL, :020_1 DEC4IMAL(19, 4) = NULL, :020_1 DECIMAL(19, 4) = NULL, :020_1 DEC89IMAL(19, 4) = NULL, :021_1 DECIMAL(19, 4) = NULL, :021_1 DEC97IMAL(19, 4) = NULL, :021_1 DEC9IMAL(19, 4) = NULL, :021_1 DEC46IMAL(19, 4) = NULL, :021_1 DEC95IMAL(19, 4) = NULL, :021_1 DEC44IMAL(19, 4) = NULL, :021_1 DEC4IMAL(19, 4) = NULL, :021_1 DECIMAL(19, 4) = NULL, :021_1 DEC89IMAL(19, 4) = NULL, :022_1 DECIMAL(19, 4) = NULL, :022_1 DEC97IMAL(19, 4) = NULL, :022_1 DEC9IMAL(19, 4) = NULL, :022_1 DEC46IMAL(19, 4) = NULL, :022_1 DEC95IMAL(19, 4) = NULL, :022_1 DEC44IMAL(19, 4) = NULL, :022_1 DEC4IMAL(19, 4) = NULL, :022_1 DECIMAL(19, 4) = NULL, :022_1 DEC89IMAL(19, 4) = NULL, :023_1 DECIMAL(19, 4) = NULL, :023_1 DEC97IMAL(19, 4) = NULL, :023_1 DEC9IMAL(19, 4) = NULL, :023_1 DEC46IMAL(19, 4) = NULL, :023_1 DEC95IMAL(19, 4) = NULL, :023_1 DEC44IMAL(19, 4) = NULL, :023_1 DEC4IMAL(19, 4) = NULL, :023_1 DECIMAL(19, 4) = NULL, :023_1 DEC89IMAL(19, 4) = NULL, :024_1 DECIMAL(19, 4) = NULL, :024_1 DEC97IMAL(19, 4) = NULL, :024_1 DEC9IMAL(19, 4) = NULL, :024_1 DEC46IMAL(19, 4) = NULL, :024_1 DEC95IMAL(19, 4) = NULL, :024_1 DEC44IMAL(19, 4) = NULL, :024_1 DEC4IMAL(19, 4) = NULL, :024_1 DECIMAL(19, 4) = NULL, :024_1 DEC89IMAL(19, 4) = NULL, :025_1 DECIMAL(19, 4) = NULL, :025_1 DEC97IMAL(19, 4) = NULL, :025_1 DEC9IMAL(19, 4) = NULL, :025_1 DEC46IMAL(19, 4) = NULL, :025_1 DEC95IMAL(19, 4) = NULL, :025_1 DEC44IMAL(19, 4) = NULL, :025_1 DEC4IMAL(19, 4) = NULL, :025_1 DECIMAL(19, 4) = NULL, :025_1 DEC89IMAL(19, 4) = NULL, :026_1 DECIMAL(19, 4) = NULL, :026_1 DEC97IMAL(19, 4) = NULL, :026_1 DEC9IMAL(19, 4) = NULL, :026_1 DEC46IMAL(19, 4) = NULL, :026_1 DEC95IMAL(19, 4) = NULL, :026_1 DEC44IMAL(19, 4) = NULL, :026_1 DEC4IMAL(19, 4) = NULL, :026_1 DECIMAL(19, 4) = NULL, :026_1 DEC89IMAL(19, 4) = NULL, :027_1 DECIMAL(19, 4) = NULL, :027_1 DEC97IMAL(19, 4) = NULL, :027_1 DEC9IMAL(19, 4) = NULL, :027_1 DEC46IMAL(19, 4) = NULL, :027_1 DEC95IMAL(19, 4) = NULL, :027_1 DEC44IMAL(19, 4) = NULL, :027_1 DEC4IMAL(19, 4) = NULL, :027_1 DECIMAL(19, 4) = NULL, :027_1 DEC89IMAL(19, 4) = NULL, :028_1 DECIMAL(19, 4) = NULL, :028_1 DEC97IMAL(19, 4) = NULL, :028_1 DEC9IMAL(19, 4) = NULL, :028_1 DEC46IMAL(19, 4) = NULL, :028_1 DEC95IMAL(19, 4) = NULL, :028_1 DEC44IMAL(19, 4) = NULL, :028_1 DEC4IMAL(19, 4) = NULL, :028_1 DECIMAL(19, 4) = NULL, :028_1 DEC89IMAL(19, 4) = NULL, :029_1 DECIMAL(19, 4) = NULL, :029_1 DEC97IMAL(19, 4) = NULL, :029_1 DEC9IMAL(19, 4) = NULL, :029_1 DEC46IMAL(19, 4) = NULL, :029_1 DEC95IMAL(19, 4) = NULL, :029_1 DEC44IMAL(19, 4) = NULL, :029_1 DEC4IMAL(19, 4) = NULL, :029_1 DECIMAL(19, 4) = NULL, :029_1 DEC89IMAL(19, 4) = NULL, :030_1 DECIMAL(19, 4) = NULL, :030_1 DEC97IMAL(19, 4) = NULL, :030_1 DEC9IMAL(19, 4) = NULL, :030_1 DEC46IMAL(19, 4) = NULL, :030_1 DEC95IMAL(19, 4) = NULL, :030_1 DEC44IMAL(19, 4) = NULL, :030_1 DEC4IMAL(19, 4) = NULL, :030_1 DECIMAL(19, 4) = NULL, :030_1 DEC89IMAL(19, 4) = NULL, :031_1 DECIMAL(19, 4) = NULL, :031_1 DEC97IMAL(19, 4) = NULL, :031_1 DEC9IMAL(19, 4) = NULL, :031_1 DEC46IMAL(19, 4) = NULL, :031_1 DEC95IMAL(19, 4) = NULL, :031_1 DEC44IMAL(19, 4) = NULL, :031_1 DEC4IMAL(19, 4) = NULL, :031_1 DECIMAL(19, 4) = NULL, :031_1 DEC89IMAL(19, 4) = NULL, :032_1 DECIMAL(19, 4) = NULL, :032_1 DEC97IMAL(19, 4) = NULL, :032_1 DEC9IMAL(19, 4) = NULL, :032_1 DEC46IMAL(19, 4) = NULL, :032_1 DEC95IMAL(19, 4) = NULL, :032_1 DEC44IMAL(19, 4) = NULL, :032_1 DEC4IMAL(19, 4) = NULL, :032_1 DECIMAL(19, 4) = NULL, :032_1 DEC89IMAL(19, 4) = NULL, :033_1 DECIMAL(19, 4) = NULL, :033_1 DEC97IMAL(19, 4) = NULL, :033_1 DEC9IMAL(19, 4) = NULL, :033_1 DEC46IMAL(19, 4) = NULL, :033_1 DEC95IMAL(19, 4) = NULL, :033_1 DEC44IMAL(19, 4) = NULL, :033_1 DEC4IMAL(19, 4) = NULL, :033_1 DECIMAL(19, 4) = NULL, :033_1 DEC89IMAL(19, 4) = NULL, :034_1 DECIMAL(19, 4) = NULL, :034_1 DEC97IMAL(19, 4) = NULL, :034_1 DEC9IMAL(19, 4) = NULL, :034_1 DEC46IMAL(19, 4) = NULL, :034_1 DEC95IMAL(19, 4) = NULL, :034_1 DEC44IMAL(19, 4) = NULL, :034_1 DEC4IMAL(19, 4) = NULL, :034_1 DECIMAL(19, 4) = NULL, :034_1 DEC89IMAL(19, 4) = NULL, :035_1 DECIMAL(19, 4) = NULL, :035_1 DEC97IMAL(19, 4) = NULL, :035_1 DEC9IMAL(19, 4) = NULL, :035_1 DEC46IMAL(19, 4) = NULL, :035_1 DEC95IMAL(19, 4) = NULL, :035_1 DEC44IMAL(19, 4) = NULL, :035_1 DEC4IMAL(19, 4) = NULL, :035_1 DECIMAL(19, 4) = NULL, :035_1 DEC89IMAL(19, 4) = NULL, :036_1 DECIMAL(19, 4) = NULL, :036_1 DEC97IMAL(19, 4) = NULL, :036_1 DEC9IMAL(19, 4) = NULL, :036_1 DEC46IMAL(19, 4) = NULL, :036_1 DEC95IMAL(19, 4) = NULL, :036_1 DEC44IMAL(19, 4) = NULL, :036_1 DEC4IMAL(19, 4) = NULL, :036_1 DECIMAL(19, 4) = NULL, :036_1 DEC89IMAL(19, 4) = NULL, :037_1 DECIMAL(19, 4) = NULL, :037_1 DEC97IMAL(19, 4) = NULL, :037_1 DEC9IMAL(19, 4) = NULL, :037_1 DEC46IMAL(19, 4) = NULL, :037_1 DEC95IMAL(19, 4) = NULL, :037_1 DEC44IMAL(19, 4) = NULL, :037_1 DEC4IMAL(19, 4) = NULL, :037_1 DECIMAL(19, 4) = NULL, :037_1 DEC89IMAL(19, 4) = NULL, :038_1 DECIMAL(19, 4) = NULL, :038_1 DEC97IMAL(19, 4) = NULL, :038_1 DEC9IMAL(19, 4) = NULL, :038_1 DEC46IMAL(19, 4) = NULL, :038_1 DEC95IMAL(19, 4) = NULL, :038_1 DEC44IMAL(19, 4) = NULL, :038_1 DEC4IMAL(19, 4) = NULL, :038_1 DECIMAL(19, 4) = NULL, :038_1 DEC89IMAL(19, 4) = NULL, :039_1 DECIMAL(19, 4) = NULL, :039_1 DEC97IMAL(19, 4) = NULL, :039_1 DEC9IMAL(19, 4) = NULL, :039_1 DEC46IMAL(19, 4) = NULL, :039_1 DEC95IMAL(19, 4) = NULL, :039_1 DEC44IMAL(19, 4) = NULL, :039_1 DEC4IMAL(19, 4) = NULL, :039_1 DECIMAL(19, 4) = NULL, :039_1 DEC89IMAL(19, 4) = NULL, :040_1 DECIMAL(19, 4) = NULL, :040_1 DEC97IMAL(19, 4) = NULL, :040_1 DEC9IMAL(19, 4) = NULL, :040_1 DEC46IMAL(19, 4) = NULL, :040_1 DEC95IMAL(19, 4) = NULL, :040_1 DEC44IMAL(19, 4) = NULL, :040_1 DEC4IMAL(19, 4) = NULL, :040_1 DECIMAL(19, 4) = NULL, :040_1 DEC89IMAL(19, 4) = NULL, :041_1 DECIMAL(19, 4) = NULL, :041_1 DEC97IMAL(19, 4) = NULL, :041_1 DEC9IMAL(19, 4) = NULL, :041_1 DEC46IMAL(19, 4) = NULL, :041_1 DEC95IMAL(19, 4) = NULL, :041_1 DEC44IMAL(19, 4) = NULL, :041_1 DEC4IMAL(19, 4) = NULL, :041_1 DECIMAL(19, 4) = NULL, :041_1 DEC89IMAL(19, 4) = NULL, :042_1 DECIMAL(19, 4) = NULL, :042_1 DEC97IMAL(19, 4) = NULL, :042_1 DEC9IMAL(19, 4) = NULL, :042_1 DEC46IMAL(19, 4) = NULL, :042_1 DEC95IMAL(19, 4) = NULL, :042_1 DEC44IMAL(19, 4) = NULL, :042_1 DEC4IMAL(19, 4) = NULL, :042_1 DECIMAL(19, 4) = NULL, :042_1 DEC89IMAL(19, 4) = NULL, :043_1 DECIMAL(19, 4) = NULL, :043_1 DEC97IMAL(19, 4) = NULL, :043_1 DEC9IMAL(19, 4) = NULL, :043_1 DEC46IMAL(19, 4) = NULL, :043_1 DEC95IMAL(19, 4) = NULL, :043_1 DEC44IMAL(19, 4) = NULL, :043_1 DEC4IMAL(19, 4) = NULL, :043_1 DECIMAL(19, 4) = NULL, :043_1 DEC89IMAL(19, 4) = NULL, :044_1 DECIMAL(19, 4) = NULL, :044_1 DEC97IMAL(19, 4) = NULL, :044_1 DEC9IMAL(19, 4) = NULL, :044_1 DEC46IMAL(19, 4) = NULL, :044_1 DEC95IMAL(19, 4) = NULL, :044_1 DEC44IMAL(19, 4) = NULL, :044_1 DEC4IMAL(19, 4) = NULL, :044_1 DECIMAL(19, 4) = NULL, :044_1 DEC89IMAL(19, 4) = NULL, :045_1 DECIMAL(19, 4) = NULL, :045_1 DEC97IMAL(19, 4) = NULL, :045_1 DEC9IMAL(19, 4) = NULL, :045_1 DEC46IMAL(19, 4) = NULL, :045_1 DEC95IMAL(19, 4) = NULL, :045_1 DEC44IMAL(19, 4) = NULL, :045_1 DEC4IMAL(19, 4) = NULL, :045_1 DECIMAL(19, 4) = NULL, :045_1 DEC89IMAL(19, 4) = NULL, :046_1 DECIMAL(19, 4) = NULL, :046_1 DEC97IMAL(19, 4) = NULL, :046_1 DEC9IMAL(19, 4) = NULL, :046_1 DEC46IMAL(19, 4) = NULL, :046_1 DEC95IMAL(19, 4) = NULL, :046_1 DEC44IMAL(19, 4) = NULL, :046_1 DEC4IMAL(19, 4) = NULL, :046_1 DECIMAL(19, 4) = NULL, :046_1 DEC89IMAL(19, 4) = NULL, :047_1 DECIMAL(19, 4) = NULL, :047_1 DEC97IMAL(19, 4) = NULL, :047_1 DEC9IMAL(19, 4) = NULL, :047_1 DEC46IMAL(19, 4) = NULL, :047_1 DEC95IMAL(19, 4) = NULL, :047_1 DEC44IMAL(19, 4) = NULL, :047_1 DEC4IMAL(19, 4) = NULL, :047_1 DECIMAL(19, 4) = NULL, :047_1 DEC89IMAL(19, 4) = NULL, :048_1 DECIMAL(19, 4) = NULL, :048_1 DEC97IMAL(19, 4) = NULL, :048_1 DEC9IMAL(19, 4) = NULL, :048_1 DEC46IMAL(19, 4) = NULL, :048_1 DEC95IMAL(19, 4) = NULL, :048_1 DEC44IMAL(19, 4) = NULL, :048_1 DEC4IMAL(19, 4) = NULL, :048_1 DECIMAL(19, 4) = NULL, :048_1 DEC89IMAL(19, 4) = NULL, :049_1 DECIMAL(19, 4) = NULL, :049_1 DEC97IMAL(19, 4) = NULL, :049_1 DEC9IMAL(19, 4) = NULL, :049_1 DEC46IMAL(19, 4) = NULL, :049_1 DEC95IMAL(19, 4) = NULL, :049_1 DEC44IMAL(19, 4) = NULL, :049_1 DEC4IMAL(19, 4) = NULL, :049_1 DECIMAL(19, 4) = NULL, :049_1 DEC89IMAL(19, 4) = NULL ) AS INSERT INTO tblSimulation (p, cfYear, cfLocation, Delta, Design, SigmaLoc, Sigma, SampleSize, Intercept) SELECT DT1. P, DT1. CfYear, DT1.

CfLocation, DT1. Delta, DT1. Design, DT1.

SigmaLoc, DT1. Sigma, DT1. SampleSize, DT1.

Intercept FROM ( SELECT :001_1 AS p, :001_2 AS cfYear, :001_3 AS cfLocation, :001_4 AS Delta, :001_5 AS Design, :001_6 AS SigmaLoc, :001_7 AS Sigma, :001_8 AS SampleSize, :001_9 AS Intercept FROM OneRowTable UNION ALL SELECT :002_1, :002_2, :002_3, :002_4, :002_5, :002_6, :002_7, :002_8, :002_9 FROM OneRowTable UNION ALL SELECT :003_1, :003_2, :003_3, :003_4, :003_5, :003_6, :003_7, :003_8, :003_9 FROM OneRowTable UNION ALL SELECT :004_1, :004_2, :004_3, :004_4, :004_5, :004_6, :004_7, :004_8, :004_9 FROM OneRowTable UNION ALL SELECT :005_1, :005_2, :005_3, :005_4, :005_5, :005_6, :005_7, :005_8, :005_9 FROM OneRowTable UNION ALL SELECT :006_1, :006_2, :006_3, :006_4, :006_5, :006_6, :006_7, :006_8, :006_9 FROM OneRowTable UNION ALL SELECT :007_1, :007_2, :007_3, :007_4, :007_5, :007_6, :007_7, :007_8, :007_9 FROM OneRowTable UNION ALL SELECT :008_1, :008_2, :008_3, :008_4, :008_5, :008_6, :008_7, :008_8, :008_9 FROM OneRowTable UNION ALL SELECT :009_1, :009_2, :009_3, :009_4, :009_5, :009_6, :009_7, :009_8, :009_9 FROM OneRowTable UNION ALL SELECT :010_1, :010_2, :010_3, :010_4, :010_5, :010_6, :010_7, :010_8, :010_9 FROM OneRowTable UNION ALL SELECT :011_1, :011_2, :011_3, :011_4, :011_5, :011_6, :011_7, :011_8, :011_9 FROM OneRowTable UNION ALL SELECT :012_1, :012_2, :012_3, :012_4, :012_5, :012_6, :012_7, :012_8, :012_9 FROM OneRowTable UNION ALL SELECT :013_1, :013_2, :013_3, :013_4, :013_5, :013_6, :013_7, :013_8, :013_9 FROM OneRowTable UNION ALL SELECT :014_1, :014_2, :014_3, :014_4, :014_5, :014_6, :014_7, :014_8, :014_9 FROM OneRowTable UNION ALL SELECT :015_1, :015_2, :015_3, :015_4, :015_5, :015_6, :015_7, :015_8, :015_9 FROM OneRowTable UNION ALL SELECT :016_1, :016_2, :016_3, :016_4, :016_5, :016_6, :016_7, :016_8, :016_9 FROM OneRowTable UNION ALL SELECT :017_1, :017_2, :017_3, :017_4, :017_5, :017_6, :017_7, :017_8, :017_9 FROM OneRowTable UNION ALL SELECT :018_1, :018_2, :018_3, :018_4, :018_5, :018_6, :018_7, :018_8, :018_9 FROM OneRowTable UNION ALL SELECT :019_1, :019_2, :019_3, :019_4, :019_5, :019_6, :019_7, :019_8, :019_9 FROM OneRowTable UNION ALL SELECT :020_1, :020_2, :020_3, :020_4, :020_5, :020_6, :020_7, :020_8, :020_9 FROM OneRowTable UNION ALL SELECT :021_1, :021_2, :021_3, :021_4, :021_5, :021_6, :021_7, :021_8, :021_9 FROM OneRowTable UNION ALL SELECT :022_1, :022_2, :022_3, :022_4, :022_5, :022_6, :022_7, :022_8, :022_9 FROM OneRowTable UNION ALL SELECT :023_1, :023_2, :023_3, :023_4, :023_5, :023_6, :023_7, :023_8, :023_9 FROM OneRowTable UNION ALL SELECT :024_1, :024_2, :024_3, :024_4, :024_5, :024_6, :024_7, :024_8, :024_9 FROM OneRowTable UNION ALL SELECT :025_1, :025_2, :025_3, :025_4, :025_5, :025_6, :025_7, :025_8, :025_9 FROM OneRowTable UNION ALL SELECT :026_1, :026_2, :026_3, :026_4, :026_5, :026_6, :026_7, :026_8, :026_9 FROM OneRowTable UNION ALL SELECT :027_1, :027_2, :027_3, :027_4, :027_5, :027_6, :027_7, :027_8, :027_9 FROM OneRowTable UNION ALL SELECT :028_1, :028_2, :028_3, :028_4, :028_5, :028_6, :028_7, :028_8, :028_9 FROM OneRowTable UNION ALL SELECT :029_1, :029_2, :029_3, :029_4, :029_5, :029_6, :029_7, :029_8, :029_9 FROM OneRowTable UNION ALL SELECT :030_1, :030_2, :030_3, :030_4, :030_5, :030_6, :030_7, :030_8, :030_9 FROM OneRowTable UNION ALL SELECT :031_1, :031_2, :031_3, :031_4, :031_5, :031_6, :031_7, :031_8, :031_9 FROM OneRowTable UNION ALL SELECT :032_1, :032_2, :032_3, :032_4, :032_5, :032_6, :032_7, :032_8, :032_9 FROM OneRowTable UNION ALL SELECT :033_1, :033_2, :033_3, :033_4, :033_5, :033_6, :033_7, :033_8, :033_9 FROM OneRowTable UNION ALL SELECT :034_1, :034_2, :034_3, :034_4, :034_5, :034_6, :034_7, :034_8, :034_9 FROM OneRowTable UNION ALL SELECT :035_1, :035_2, :035_3, :035_4, :035_5, :035_6, :035_7, :035_8, :035_9 FROM OneRowTable UNION ALL SELECT :036_1, :036_2, :036_3, :036_4, :036_5, :036_6, :036_7, :036_8, :036_9 FROM OneRowTable UNION ALL SELECT :037_1, :037_2, :037_3, :037_4, :037_5, :037_6, :037_7, :037_8, :037_9 FROM OneRowTable UNION ALL SELECT :038_1, :038_2, :038_3, :038_4, :038_5, :038_6, :038_7, :038_8, :038_9 FROM OneRowTable UNION ALL SELECT :039_1, :039_2, :039_3, :039_4, :039_5, :039_6, :039_7, :039_8, :039_9 FROM OneRowTable UNION ALL SELECT :040_1, :040_2, :040_3, :040_4, :040_5, :040_6, :040_7, :040_8, :040_9 FROM OneRowTable UNION ALL SELECT :041_1, :041_2, :041_3, :041_4, :041_5, :041_6, :041_7, :041_8, :041_9 FROM OneRowTable UNION ALL SELECT :042_1, :042_2, :042_3, :042_4, :042_5, :042_6, :042_7, :042_8, :042_9 FROM OneRowTable UNION ALL SELECT :043_1, :043_2, :043_3, :043_4, :043_5, :043_6, :043_7, :043_8, :043_9 FROM OneRowTable UNION ALL SELECT :044_1, :044_2, :044_3, :044_4, :044_5, :044_6, :044_7, :044_8, :044_9 FROM OneRowTable UNION ALL SELECT :045_1, :045_2, :045_3, :045_4, :045_5, :045_6, :045_7, :045_8, :045_9 FROM OneRowTable UNION ALL SELECT :046_1, :046_2, :046_3, :046_4, :046_5, :046_6, :046_7, :046_8, :046_9 FROM OneRowTable UNION ALL SELECT :047_1, :047_2, :047_3, :047_4, :047_5, :047_6, :047_7, :047_8, :047_9 FROM OneRowTable UNION ALL SELECT :048_1, :048_2, :048_3, :048_4, :048_5, :048_6, :048_7, :048_8, :048_9 FROM OneRowTable UNION ALL SELECT :049_1, :049_2, :049_3, :049_4, :049_5, :049_6, :049_7, :049_8, :049_9 FROM OneRowTable ) AS DT1 WHERE NOT ( DT1. P IS NULL AND DT1. CfYear IS NULL AND DT1.

CfLocation IS NULL AND DT1. Delta IS NULL AND DT1. Design IS NULL AND DT1.

SigmaLoc IS NULL AND DT1. Sigma IS NULL AND DT1. SampleSize IS NULL AND DT1.

Intercept IS NULL ) ; Usage: EXECUTE Add100Simulations 1, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 3, 0, 0, 0, 0, 0, 0, 0, 0, 4, 0, 0, 0, 0, 0, 0, 0, 0, 5, 0, 0, 0, 0, 0, 0, 0, 0, 6, 0, 0, 0, 0, 0, 0, 0, 0, 7, 0, 0, 0, 0, 0, 0, 0, 0, 8, 0, 0, 0, 0, 0, 0, 0, 0, 9, 0, 0, 0, 0, 0, 0, 0, 0, 10, 0, 0, 0, 0, 0, 0, 0, 0, 11, 0, 0, 0, 0, 0, 0, 0, 0, 12, 0, 0, 0, 0, 0, 0, 0, 0, 13, 0, 0, 0, 0, 0, 0, 0, 0, 14, 0, 0, 0, 0, 0, 0, 0, 0, 15, 0, 0, 0, 0, 0, 0, 0, 0, 16, 0, 0, 0, 0, 0, 0, 0, 0, 17, 0, 0, 0, 0, 0, 0, 0, 0, 18, 0, 0, 0, 0, 0, 0, 0, 0, 19, 0, 0, 0, 0, 0, 0, 0, 0, 20, 0, 0, 0, 0, 0, 0, 0, 0, 21, 0, 0, 0, 0, 0, 0, 0, 0, 22, 0, 0, 0, 0, 0, 0, 0, 0, 23, 0, 0, 0, 0, 0, 0, 0, 0, 24, 0, 0, 0, 0, 0, 0, 0, 0, 25, 0, 0, 0, 0, 0, 0, 0, 0, 26, 0, 0, 0, 0, 0, 0, 0, 0, 27, 0, 0, 0, 0, 0, 0, 0, 0, 28, 0, 0, 0, 0, 0, 0, 0, 0, 29, 0, 0, 0, 0, 0, 0, 0, 0, 30, 0, 0, 0, 0, 0, 0, 0, 0, 31, 0, 0, 0, 0, 0, 0, 0, 0, 32, 0, 0, 0, 0, 0, 0, 0, 0, 33, 0, 0, 0, 0, 0, 0, 0, 0, 34, 0, 0, 0, 0, 0, 0, 0, 0, 35, 0, 0, 0, 0, 0, 0, 0, 0, 36, 0, 0, 0, 0, 0, 0, 0, 0, 37, 0, 0, 0, 0, 0 Note the above is ANSI-92 Query Mode syntax (see http://office.microsoft.co.

Learn something new every day. I've removed my comment to say it's impossible: what's the performance like (compared to multiple inserts in a single transaction)? Pity Access forces such a hack to do it at all... – mavnn Aug 3 '09 at 9:18.

I found an elegant solution within R, (the software I'm working with). The RODBC package has a function sqlSave which allow to append and entire data. Frame at once to a table.

This works almost twice as fast as individual inserts within a transaction. Library(RODBC) MDB.

Your query should look like this insert into aaa (col1, col2) select * from (select 'yourdatarow1col1' as col1 , 'yourdatarow1col2' as col2 from ddd union all select 'yourdatarow2col1' as col1, 'yourdatarow1col2' as col2 from ddd) as tmp aaa: Is your target table in access ddd: create a table in access db with one colum and must have 1 row, doesn't not mater what data. Aliases: All aliases should have same name and order has target table Hope works for you, my process drop from 15 minutes to 1.30 minutes Bye Gus.

Aliases don't matter in the successive SELECT statements in a UNION. Only the aliases of the first SELECT statement matter, and then only for a SELECT statement based on the UNION. – David-W-Fenton Apr 19 '10 at 18:10.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.