Member Avatar for mr_scooby

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

  • 2 Contributors
  • 1 Reply
  • 2K Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by sknake
Member Avatar for sknake

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.