We help IT Professionals succeed at work.

T-SQL syntax for inserting multiple rows

RichNH
RichNH asked
on
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)      
Comment
Watch Question

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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
Commented:
You don't need the repeating "values".

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

Author

Commented:
So you do need to create separate insert statements for each row?  There's no way to string the data into a single command?
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'



Author

Commented:
Thanks Tim, that did t.

Author

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