Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 insert data to table with subquery
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

52 Posts

Posted - 01/18/2013 :  07:47:41  Show Profile  Reply with Quote
Hi all,

Table definition as follows,


Id --> PrimaryKey

Id--> PrimaryKey
T1-Id--> ForeignKey(Table1)


This my Query

"Insert into Table1
Select newid(), name from Table1 where id = 1234"

"Insert into Table2
Select newid(), name, newid() from Table2 where T1-Id = 1234"

"Insert into Table3
Select newid(), name, newid() from Table3 where T2-Id in (Select id from Table2 where id = 2345"

please check the above query , i have a problem in 3rd stmt.
query result is always empty. i miss some stmt. any body correct the query's, Please help me and Thanks.

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/18/2013 :  08:02:28  Show Profile  Reply with Quote
I think there is something wrong in the logic even starting at the first insert. NEWID() generates a result of data type UNIQUEIDENTIFIER. So if the Id column in Table1 is of UNIQUEIDENTIFIER type, the WHERE clause (where id=1234) is not correct. You cannot implicitly convert a UNIQUEIDENTIFIER to an INT. On the other hand, if Id in table1 is an int, the insert would fail because you are trying to insert a UNIQUEIDENTIFIER into an integer column.

Can you post some sample data that you want to see in the 3 tables if the insertions were successful?
Go to Top of Page

Yak Posting Veteran

52 Posts

Posted - 01/19/2013 :  01:32:10  Show Profile  Reply with Quote
Hi, This is my

Id Name
---- ---------
1234 AAA
5678 BBB
9765 CCC

Id Name T1.Id
---- ------ -------
9999 DDD 1234
8888 EEE 1234
7777 FFF 1234
1010 GGG 5678
1111 TTT 9765


Id Name T2.Id
---- ------ --------------
0000 TTTT 9999
0101 HHHH 9999
7111 YYYY 7777
8754 XXXX 7777
9872 RRRR 1111
3654 WWQQ 9999

When I run query, I get the following error:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."

But the point of the subquery was to in fact return more than one value.
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 01/19/2013 :  02:27:40  Show Profile  Reply with Quote
subquery cant return multiple values unless you use IN operator or exists. Can you explain what exactly you're trying to achieve and your current used query?

SQL Server MVP

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000