SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

teamjai
Starting Member

India
44 Posts

Posted - 01/18/2013 :  07:47:41  Show Profile  Reply with Quote
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

3719 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

teamjai
Starting Member

India
44 Posts

Posted - 01/19/2013 :  01:32:10  Show Profile  Reply with Quote
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

India
52325 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
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000