Insert multiple rows using one insert statement in Access 2010

Use this confirm working query:

INSERT INTO Product (Code,Name,IsActive,CreatedById,CreatedDate )

SELECT * FROM 
(    
  SELECT '10001000' AS Code,
         'Blackburn sunglasses' AS Name,
         1 AS IsActive,
         1 AS CreatedById,
         '2/20/2015 12:23:00 AM' AS CreatedDate 
   FROM Product    
   UNION 
   SELECT '10005200' AS Code,
          '30 panel football' AS Name,
          1 AS IsActive,
          1 AS CreatedById,
          '2/20/2015 12:23:09 AM' AS CreatedDate 
    FROM Product

) ;

As marc_s has pointed out, for SQL Server 2008 and later, you can just use table value constructors. For previous versions, you can use insert and select...union all, e.g.:

INSERT INTO Production.UnitMeasure
SELECT N'FT2',N'Square Feet ','20080923' union all
SELECT N'Y',  N'Yards',       '20080923' union all
SELECT N'Y3', N'Cubic Yards', '20080923'

(Specific documentation on Table Value Constructors in SQL Server. I can't find specific separate documentation on row value constructors, but that's what they are)

For SQL-Server: Yes, and it can exactly like you write. Just be certain that the column values are in the same order as they appear in the table. Also: you must supply a value for each existing column.

For Access 2010: No. At least not by hardcoded values in the sql, but only by selecting multiple records from a table (in the same or in another database). See also the link in the answer of Khepri.

SQL Server definitely allows this: EDIT: [As of SQL Server 2008, thank you Marc_s]

INSERT INTO [Table]
([COL1], [COL2])
VALUES
('[email protected]', 1),
('[email protected]', 2)

As for the Access requirement, I'm no access guru but I found this MSDN documentation that shows how to do multiple inserts at once.

INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase]
     SELECT [source.]field1[, field2[, …] FROM tableexpression

Doing some cursory reading beyond this, you can use a "dummy" from table if all of your values are known ahead of time as in your example.