| Author |
 Topic  |
|
|
teamjai
Starting Member
India
33 Posts |
 Posted - 01/18/2013 : 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
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 01/18/2013 : 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? |
 |
|
|
teamjai
Starting Member
India
33 Posts |
Posted - 01/19/2013 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/19/2013 : 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/
|
|
|
| |
Topic |
|
insert data to table with subquery
