hi guys, done heaps of googling and everything leads me to two outcomes.

1. You can't do it

2. You can do it this way

INSERT INTO User(userEmailAddress, userFirstName,userLastName,userStatus,userNickName,userMifrenzPassword,userEmailPassword,userDOB)

SELECT 'overthehill@gmail.com','Ben','Hill','U','benny','milk','soccer','20-10-2000'
UNION ALL

SELECT 'smithy@gmail.com','Tony','Smith','A','smithy','bread','rugby','20-11-1976'
UNION ALL

SELECT 'hotpants@gmail.com','Sarah','Jane','U','hotty','toast','netball','15-1-2000'
UNION ALL

SELECT 'traci@gmail.com','Traci','Hill','U','raisins','coffee','hockey','20-10-2000'
UNION ALL

SELECT 'lol@gmail.com','Ben','Johns','U','stubby','jam','rowing','10-2-2001'
UNION ALL

SELECT 'ss@gmail.com','Sarah','Smith','U','shorty','ham','running','5-5-2000'
UNION ALL

SELECT 'bob@gmail.com','Bob','Cawte','A','bob','eggs','bobsled','29-3-1980'

except it throws a

Syntax error(misisng operator) in query expression "20-10-2000"
UNION ALL

SELECT 'smithy@gmail.com".

worst comes to worst I can use individual INSERT commands to demonstrate my database in class, but I have 40+ records for 2 tables so was trying to find a quicker way of doing it in two statements instead of 80.

Anyone know if you can do this?

cheers

Re: Inserting multiple rows with 1 sql INSERT using ACCESS 80 80

The UNION ALL isn't really buying you much so I don't see the need to do it that way. Just use a transaction and do the insert:

IF OBJECT_ID('Test', 'U') IS NOT NULL DROP TABLE Test
Create Table Test
(
  UserName varchar(30)
)
GO
BEGIN TRANSACTION
Insert Into Test (UserName) Values ('A')
Insert Into Test (UserName) Values ('B')
Insert Into Test (UserName) Values ('C')
Insert Into Test (UserName) Values ('D')
COMMIT TRANSACTION
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.