asked on

T-SQL syntax for inserting multiple rows

Hello, I am learning TSQL, the book I'm using doesn't have a lot on this statement. I am getting some errors on my first attempt at multiple row insertion. Can you tell me how the syntax is supposed to look. I was trying not to enter separate insert statements for each row of data. The error(s) I get are:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'values'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'values'.
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'values'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'values'.

The code is this:
use projects
insert into department (dept_no, dept_name, location)
      values ('d1','Research','Dallas')
      values ('d2','Accounting','Seattle')
      values ('d3','Marketing','Dallas')

insert into employee (emp_no, emp_fname, emp_lname, dept_no)
      values (25348, 'Mathew', 'Smith', 'd3')
      values (10102, 'Ann','Jones','d3')
      values (18316, 'John','Barrimore','d1')
      values (29346, 'James','James','d2')
      values (9031, 'Elke','Hansel','d2')
      values (2581, 'Elsa','Bertoni','d2')
      values (28559, 'Sybill','Moser','d1')

Insert into project (project_no, project_name, budget)
      values ('p1','Apollo',120000)
      values ('p2','Gemini',95000)
      values ('p3','Mercury',185600)

insert into works_on (emp_no, project_no, job, enter_date)
      values (10102,'p1','Analyst',12/7/11)
      values (10102,'p3','Manager',12/1/11)
      values (25348,'p2','Clerk',12/2/11)
      values (18316,'p2',null,12/3/11)
      values (29346,'p2',null,12/4/11)
      values (2581,'p3','Analyst',12/5/11)
      values (9031,'p1','Manager',12/6/11)
      values (28559,'p1',null,12/8/11)
      values (28559,'p2','Clerk',12/9/11)
      values (9031,'p3','Clerk',12/10/11)
      values (29346,'p1','Clerk',12/11/11)

Jim Horn

insert into department (dept_no, dept_name, location)
values ('d1','Research','Dallas')

insert into department (dept_no, dept_name, location)
values ('d2','Accounting','Seattle')

insert into department (dept_no, dept_name, location)
values ('d3','Marketing','Dallas')

you get the idea

ASKER CERTIFIED SOLUTION

tim_cs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RichNH

ASKER

So you do need to create separate insert statements for each row? There's no way to string the data into a single command?

ralmada

It depends on your SQL version.

If SQL 2008 then you can do this like this:

insert into department (dept_no, dept_name, location)
values ('d1','Research','Dallas'), ('d2','Accounting','Seattle'), ('d3','Marketing','Dallas')

If you're in SQL 2005 or below

insert into department (dept_no, dept_name, location)
select 'd1' as dept_no,'Research' as dept_name,'Dallas' as location
union all
select 'd2','Accounting','Seattle'
union all
select 'd3','Marketing','Dallas'

RichNH

ASKER

Thanks Tim, that did t.

RichNH

ASKER

I did not realize that there were differences between the versions. Stands to reason that there would be. Thanks again.