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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 insert data to table with subquery

Author  Topic 

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-01-18 : 07:47:41
Hi all,

Table definition as follows,

Table1

Id --> PrimaryKey
Name

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

Table3
Id-->Primarykey
Name
T2-Id-->ForeignKey(Table2)

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-18 : 08:02:28
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

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-01-19 : 01:32:10
Hi, This is my

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

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

Table3

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-19 : 02:27:40
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
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -