Introduction
This is a short tutorial on how to insert data with a subquery on SQL Server databases.
The reason I am writing about it is that the required syntax is not that obvious!
Sample tables and data
Let’s create two simple tables for this tutorial with the names ‘Customers’ and ‘Orders’.
CREATE TABLE [dbo].[Customers]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](100) NOT NULL, [username] [varchar](20) NOT NULL )
CREATE TABLE [dbo].[Orders]( [order_id] [int] IDENTITY(1,1) NOT NULL, [customer_id] [int] NOT NULL, [order_date] [datetime] NOT NULL )
We are also going to insert a new row on table ‘Customers’:
INSERT INTO Customers VALUES ('John Smith','john.smith')
Inserting data with a subquery
The obvious syntax to insert data into the orders table with a subquery would be:
INSERT INTO Orders VALUES ((SELECT id FROM Customers WHERE username = 'john.smith'), GETDATE())
However if you try the above query, you would end up with the following message:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
The correct syntax to accomplish our task is:
INSERT INTO Orders SELECT id, GETDATE() FROM Customers WHERE username = 'john.smith'
or if you would like to specify the columns:
INSERT INTO Orders (customer_id, order_date) SELECT id, GETDATE() FROM Customers WHERE username = 'john.smith'
Conclusion
Hope you find it useful, especially if you are getting the “Subqueries are not allowed …” error.
Too bad this results in only being able to look up a single value, from a single table. 😦
For someone who’s used to MySQL, this limitation of SQL Server is really frustrating.
Its quite simple actually. CHEAT!
INSERT INTO md_ref_eq_index (index_id, equity_id)
SELECT A.columnA, B.columnB
FROM (SELECT columnA FROM ABC WHERE some_column = ‘DEF’) A,
(SELECT columnB FROM JKL WHERE some_column = ‘CNA’) B
WHERE 1 = 1
plz I need a specific answer to move data from one sql server to another sql server assuming they have the same structure.
SELECT ps.cat_id, c.description, ps.product_id, p.product_name,
ps.size_id, s.size_name,ob.ob,
(select sum(qty) as a from stockin_2 as si where cat_id = ps.cat_id and
product_id =ps.product_id and size_id = ps.size_id group by cat_id,product_id,size_id) as stockin,
(select sum(qty) as b from sale_2 As so where cat_id = ps.cat_id and
product_id = ps.product_id and size_id = ps.size_id group by cat_id,product_id,size_id) as stockout
FROM product_size As ps
inner join catagory As c on ps.cat_id = c.cat_id
inner join product As p on ps.cat_id = p.cat_id and ps.product_id = p.product_id
inner join size As s on ps.size_id = s.size_id
left outer join open_balance As ob on ps.cat_id = ob.catagory_id and ps.product_id = ob.product_id
and ps.size_id = ob.size_id
WHERE c.cat_id = 1
i want to total to total subqueries within a main query result
This helped me loads!
Thanks!
It really helped me…
Sparow… Thanks for the “CHEAT” idea…
thx, helped much!
Hi ,
This works if thw whole value for insert is taken from
select,but this does not works for me if i give query lik these
Insert into user_rights(admin_gid,menu_gid)
Values ( ‘5’,(select menu_gid from tmenu))
Im getting error lik
“Subqueries are not allowed in this context. Only scalar expressions are allowed ”
I think because one value for insert is from GUI and other value from insert.
So how can i do this ,Can any one help on this issue
INSERT INTO user_rights (admin_gid, menu_gid)
SELECT 5, menu_gid FROM tmenu
Awesome. Just what I was looking for. THANKS!
Hello. Fantastic job, if I wasn’t so busy with my school work I read your total site. Thanks!
Thanks a lot Sparow. The syntax you mentioned is quite useful.
Can anybody help with this, i get the same error wen i run dis.
INSERT INTO PSPS_Posts (Post, Date_Posted, user_ID, Thread_ID) VALUES (@Post, @Date_Posted, @user_ID, (SELECT Thread_ID FROM PSPS_Thread WHERE Thread_ID = (SELECT MAX(Thread_ID) FROM PSPS_Thread
try
INSERT INTO PSPS_Posts
(Post, Date_Posted, user_ID, Thread_ID)
select
@Post, @Date_Posted, @user_ID,
Thread_ID FROM PSPS_Thread WHERE Thread_ID = (SELECT MAX(Thread_ID) FROM PSPS_Thread)
Hey!
Just one question, when I try to do the INSERT INTO SELECT for a decimal data type field…it only save the integer part….somebody heard about this before?
Thanks!
Mel
Great tip. Thanks!
Good post. Solved my problem. Thanks
Very nice. Thanks a ton!
Can anyone help me with this one, getting the same error
/****** Object: StoredProcedure [dbo].[RegisterSubdomain] Script Date: 07/06/2009 10:10:40 ******/
SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
CREATE PROCEDURE [dbo].[Registersubdomain] @un VARCHAR(50),
@subdomain VARCHAR(50),
@domain VARCHAR(50),
@limit INT,
@isR BIT
AS
BEGIN
DECLARE @success BIT
SET nocount ON;
IF EXISTS (SELECT subdomain
FROM subdomains
WHERE subdomain = @subdomain)
BEGIN
SET @success = 0
END
ELSE
BEGIN
IF (SELECT COUNT(*)
FROM subdomains
WHERE un = @un) < @limit
BEGIN
INSERT INTO [subdomains]
([un],
[subdomain],
[domain],
[isActive],
[isR],
[isPaid])
VALUES (@un,
Lower(@subdomain),
Lower(@domain),
1,
@isR,
(SELECT paiduser
FROM users
WHERE un = @un))
–add sample page
INSERT INTO [pages]
([title],
[description],
[pageFolder],
[keywords],
[isStartPage],
[subdomain],
[hits])
VALUES ('Welcome to my site',
'My default page',
'Root',
@subdomain,
1,
@subdomain,
0)
–add sample style
INSERT INTO [styles]
([style],
[subdomain])
VALUES ( N'body {
margin: 0px;
padding:0px;
}',
@subdomain)
— set success 1
SET @success =1
END
END
RETURN @success
END
GO
This solved my “subqueries are not allowed…” error. Thanks a lot!
hello there!
can anyone help me please..?
im trying to select a data from table1 which is the alumniID where firstname is equal to textbox1 and surname is textbox2, then after selecting the alumniID.. it will be inserted in the 2nd table in column1 named as alumniID. so basically, i will get the alumniID from table1 and then insert it in alumniID in table2.
i will really appreciate for the help!
thanks!
Sparrow, thanks for the cheat. It worked great.
Kindly let me know whether I use query parameters in subquery of insert statement. I have tried for mysql. No rows are inserted and there is no exception too.
Thanks. Works for sybase too.
Very Thanks, you help me.
I need SELECT subquery in sql server 2008
With example
fine
Great, thanks.
It also works with Access 2003 😀
Awesome ! Worked great ! Thanks !
Thank You Very Much
thanks …………….
[…] = 'john.smith' source code : // https://decoding.wordpress.com/2008/10/11/sql-server-insert-into-with-subquery/ Paylaş Hide Sites $$('div.d751').each( function(e) { […]
Very informative post, you explain very well on SubQuery in SQL server. I’ve some good articles too which I was found during searching this topic over internet. Here I’m sharing that post links……..
http://www.mindstick.com/Blog/202/Sub%20query%20in%20sqlserver
http://www.dbtalks.com/UploadFile/rohatash123/495/
I’m very thankful for your nice post.
tnqqq
Thank you, this example solved my problem.
I post my code because it can be useful to someone. In my problem I had to insert into a multivalue field from a joined table. So basically moving one2many join information into one table. (I know, not best practice and access is doing this join behind the screen, but team members like less complicated views)
INSERT INTO tbl_b1 ([Observers].[Value])
SELECT tbl_b3.Observer FROM tbl_b1 INNER JOIN tbl_b3 ON tbl_b1.pkTeamID = tbl_b3.TeamID WHERE (((b3.Observer)=”Name”));
Obviously without the WHERE all the Observers are inserted at once.
what is the use of this INSERT INTO ……SELECT………………………..Where i use
It worked, Thanks