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.
Author |
Topic |
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-18 : 07:47:41
|
Hi all,Table definition as follows,Table1Id --> PrimaryKey NameTable2Id--> PrimaryKeyNameT1-Id--> ForeignKey(Table1)Table3Id-->PrimarykeyNameT2-Id-->ForeignKey(Table2)This my Query"Insert into Table1 Select newid(), name from Table1 where id = 1234""Insert into Table2Select newid(), name, newid() from Table2 where T1-Id = 1234""Insert into Table3Select 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? |
|
|
teamjai
Yak Posting Veteran
52 Posts |
Posted - 2013-01-19 : 01:32:10
|
Hi, This is my Table1Id Name---- ---------1234 AAA5678 BBB9765 CCCTable2Id Name T1.Id---- ------ -------9999 DDD 12348888 EEE 12347777 FFF 12341010 GGG 56781111 TTT 9765Table3Id Name T2.Id---- ------ --------------0000 TTTT 99990101 HHHH 99997111 YYYY 77778754 XXXX 77779872 RRRR 11113654 WWQQ 9999When 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
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|