How to insert values into a sub select query for MS SQL?

Question

For some reason, I need only one query to finish my project.

select empId, lastName,firstName,employee.sinNum,departmentId,position,baseSalary,gender,age,emailAddr,phoneNum from employee join person
on employee.sinNum = person.sinNum

Above will generate a result set and I want to insert values into this result set.

Insert into (empId, lastName,firstName,employee.sinNum,departmentId,position,baseSalary,gender,age,emailAddr,phoneNum from 
employee join person on employee.sinNum = person.sinNum) values 
('meng','xue',333,10,'clerk',3000,'male',30,'j@tt.com',2321)

But it does not work. So how to combine the "insert into (select...)"? Appreciate it for your time. : )

Updated (Here are tables I created)

create table person (
sinNum int primary key not null,
gender varchar(6) not null check (gender in ('male','female')) default 
'female',age int not null check (age>=18 and age<=100),
emailAddr varchar (50) not null,
phoneNum int not null,  
)

create table employee (
empId int identity (1,1) unique,
lastName varchar (30) not null,
firstName varchar (30) not null,
sinNum int not null unique foreign key references person (sinNum),
departmentId int not null foreign key references department (departmentId), 
position varchar (20) not null check (position in 
('clerk','assistant','supervisor','manager','director','president')) default 
'clerk',
baseSalary float not null
)

One person should have employee info and personal info. The two table connect each other by sinNum

When a new employee join the company. We need all register all his(her) info. This is why I want join two table as one result and using one statement to add all info.

Your first query doesn't generate a table, it returns a result set. Why do you want to insert in your results? I suspect you want to insert into `employee` and into `person`, right? — HoneyBadger, Jul 03 '17 at 07:38
Can you try in this format? INSERT INTO "TABLENAME" ("COLUMNNAME1","COLUMNNAME2") SELECT T1."COLUMNNAME1",T2."COLUMNNAME2" FROM T1 LEFT JOIN T2 ON — divya, Jul 03 '17 at 07:41
If you wanna combine the two result set as one result set, try `Union ALL` as next code:- `select empId, Name, position, sinNum, age, phoneNum from employee join person on employee.sinNum = person.sinNum Union all Select 01,'Colin','clerk',909,20,345678` — ahmed abdelqader, Jul 03 '17 at 07:44
Yes, I want to insert into employee and person table. But they have something in common and I don't want to insert same values into duplicate column — Jeffery, Jul 03 '17 at 07:44
You have to do it in 2 steps, you cannot insert data into 2 tables with 1 INSERT statement — , Jul 03 '17 at 07:46
@Jeffery different tables, different columns. Why are you talking about duplicates? And why do you want to use a *single* statement instead of using two inserts ? That's plain-old SQL, not specific to SQL Server. — Panagiotis Kanavos, Jul 03 '17 at 07:49
@Jeffery https://stackoverflow.com/questions/175066/sql-server-is-it-possible-to-insert-into-two-tables-at-the-same-time — , Jul 03 '17 at 08:19
You could write trigger on 1 table and insert data to the other when something is inserted but this would still be 2 inserts. The other would be just hidden that's all. — , Jul 03 '17 at 08:21

LukStorms · Answer 1 · 2017-07-03T08:59:11.130

For a select into you need to tell it in which table the results need to be inserted to. Best also with the column names, so that adding an extra column to the table doesn't invalidate the sql.

For example:

insert into mytable (empId, Name, position, sinNum, age, phoneNum)
select e.empId, Name, position, e.sinNum, age, phoneNum 
from employee e
join person p on e.sinNum = p.sinNum;

or just values:

insert into mytable (empId, Name, position, sinNum, age, phoneNum)
values (1,'Colin','clerk',909,20,345678);

This requires that the destination table already exists.

If you want to insert it in for example a new temporary table, then you could use the select into syntax. Which would create that temp table.

select e.empId, Name, position, e.sinNum, age, phoneNum 
into #mytemptable
from employee e
join person p on e.sinNum = p.sinNum;

-- the temp table already exists, so we can insert some extra values to it
insert into #mytemptable (empId, Name, position, sinNum, age, phoneNum) values
(1,'Colin','clerk',909,20,345678),
(2,'Hazel','boss',1001,50,876543);

A temp table will only exist while the session is active.

temp table will be destroyed when I close the connect with database? — Jeffery, Jul 03 '17 at 08:31
@Jeffery Correct. That's why it's a temporary table. It's kept in the temp database while it exists, and gets automatic removed when you disconnect. So it's usefull for testing, or to fill it and then use it to do stuff with that data. For example updates to a real table. But a table variable can also be used for that purpose. — LukStorms, Jul 03 '17 at 08:41

score 1 · Answer 2 · answered Jul 03 '17 at 08:39

1

HI Jeffery -

Select query return result set instead of table. So if you want insert one more row into your result set then you can either use temporary table or UNION. Like this -

select empId, lastName,firstName,employee.sinNum,departmentId,position,baseSalary,gender,age,emailAddr,phoneNum 
from employee join personon ON employee.sinNum = person.sinNum 
UNION 
Select 'meng','xue',333,10,'clerk',3000,'male',30,'j@tt.com',2321

Thanks :)

answered Jul 03 '17 at 08:39

Santosh Paswan

84
5

I tried your way, it works for adding one person but it failed to add another one. – Jeffery Jul 03 '17 at 08:48
You can use multiple UNION as per your new rows, Like - UNION Select 'meng','xue',333,10,'clerk',3000,'male',30,'j@tt.com',2321 UNION Select 'meng','xue',333,10,'clerk',3000,'male',30,'j@tt.com',2321 and so on .. – Santosh Paswan Jul 03 '17 at 08:49
That is considerable plan. But what if I want to add a lots of people then it will need lots of "union" right? – Jeffery Jul 03 '17 at 08:51
Absolutely you need lots of UNION. If you have lots of such records then insert these records in a table and then use single select query from new table with single UNION. – Santosh Paswan Jul 03 '17 at 08:54

Munawar · Answer 3 · 2017-07-03T08:12:29.383

0

Select Into creates a physical table, if you do not want to create a physical table then use @table variable like following:

DECLARE @tmpEmployee TABLE
(  
  empId int, [Name] varchar(100), position varchar(100), sinNum int, age int , phoneNum int
)

INSERT INTO @tmpEmployee
select empId, Name, position, sinNum, age, phoneNum from employee join person
on employee.sinNum = person.sinNum

INSERT INTO @tmpEmployee (empId, Name, position, sinNum, age, phoneNum)
VALUES (01,'Colin','clerk',909,20,345678)

Select * from @tmpEmployee

Update:

if the row to be merged is always a single row of known values then following method can be used to end up with single queuery:

SELECT empId, Name, position, sinNum, age, phoneNum from employee join person
on employee.sinNum = person.sinNum
UNION ALL
SELECT empid=01,Name='Colin',position='clerk',sinName=909,age=20,phoneNum=345678

edited Jul 03 '17 at 08:12

answered Jul 03 '17 at 07:51

Munawar

2,588
2
26
29

The OP is asking how to insert to two existing tables with a single statement – Panagiotis Kanavos Jul 03 '17 at 07:54
He said, want to insert into new table (created from first query result-set). his comments "Above will generate a result table and I want to insert values into this new table" – Munawar Jul 03 '17 at 07:58
Read the question and the comments again. `Yes, I want to insert into employee and person table. But they have something in common and I don't want to insert same values into duplicate column`. I'd say the OP misunderstands SQL and tries to *hack* the insert statement – Panagiotis Kanavos Jul 03 '17 at 07:59
Yes, a single statement please. Because I create a java program with GUI and I want interact with database through JDBC. I will let users fill all the blank textfield and generate to one query so when I click the add button it will send it to database. Then display results on Jtable – Jeffery Jul 03 '17 at 08:00
@Jeffery, if it will be always a single row of data need to be merged then check my updated answer. – Munawar Jul 03 '17 at 08:10
Basically, you are not inserting data into table instead, merging some data to select result-set, so union can be used to merge multiple result-sets as explained in updated answer – Munawar Jul 03 '17 at 08:14
@Munawar I have post table I created and updated the question. Hopefully, It will be more clear now. Thanks – Jeffery Jul 03 '17 at 08:26
To achieve that goal, its better to use stored procedure which allows to execute multiple queries in a single call from application/UI. – Munawar Jul 03 '17 at 08:37

How to insert values into a sub select query for MS SQL?

3 Answers3