SQL insert into select query

Question

I have 2 tables.

Attendance table with 2 columns and Records table with 1 column

+----------------------+
|       Attendance     |
+----------------------+
| EmployeeID   | Hours |
+----------------------+ 

+--------------+
|   Records    |
+--------------+
| EmployeeID   |
+--------------+
|     1        |
|     2        |
|     3        |
+--------------+

I want a query that will insert all employeeID from Records table into EmployeeID in the Attendance table with a value of 8 in Hours column.

Like this:

+----------------------+
|       Attendance     |
+----------------------+
| EmployeeID   | Hours |
|----------------------|
|      1       |   8   |
|      2       |   8   |
|      3       |   8   |
+----------------------+

I can't understand the code that I searched so I end up asking :)

By the way, I'm using SQL Server.

marc_s · Accepted Answer · 2014-08-23 20:11:16Z

1

Recently, I have made a query like that you want.

INSERT INTO Attendence (EmployeeID, Hours) 
    SELECT EmployeeID, 8 
    FROM Records 
    WHERE EmployeeID > 0

WHERE condition is for SELECT, not for INSERT INTO. This query will copy all EmployeeID's to Attendence table where EmployeeID is greater than 0.

SELECT EmployeeID, 8 FROM Records

will return something like

(1,8),(2,8),(3,8)

edited Aug 23, 2014 at 20:11

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 23, 2014 at 19:03

zkanoca

10k10 gold badges55 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

akoxi Over a year ago

I don't understand the WHERE 1. I got an error "An expression of non-boolean type specified in a context where a condition is expected, near 'SET'". But if I remove WHERE 1, I got no problem. Thanks to you anyway.

zkanoca Over a year ago

WHERE 1 means that there is no specific condition for the SELECTed rows. Of course you can delete it.

akoxi Over a year ago

Im using MSSQL. I guess WHERE 1 is only on mySql? Correct me if i'm wrong. Thanks again.

Zanon · Accepted Answer · 2015-02-02 23:30:39Z

0

Just use:

INSERT INTO Attendance (EmployeeID, Hours) 
    SELECT EmployeeID, 8 FROM Records;

edited Feb 2, 2015 at 23:30

answered Aug 23, 2014 at 19:14

Zanon

31k21 gold badges119 silver badges127 bronze badges

Collectives™ on Stack Overflow

SQL insert into select query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related