subquery in insert query - SQL Server Forums

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

subquery in insert query

Author

Topic

Alban_T
Starting Member

3 Posts

Posted - 2011-10-04 : 14:34:32

Hi,
I have a little problem. I have an INSERT query and want to use the result of a SELECT query as value.

Example:

the query: "SELECT myID FROM myDB WHERE myName='john doe'" results in a single numerical value (lets's say 5) because myName needs to be unique in my DB.

I want to use the result in an INSERT query:

INSERT INTO myDB2 ([some_field]) VALUES (5)

Could someone shed some light on my problem!?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-04 : 15:00:30

insert into myDB2 ([some_column])
Select <column | expression>
from ...
where...

Be One with the Optimizer
TG

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-05 : 02:50:17

Hi, Use

insert into table_name(column_names) select columnname from sourcetablename where condition.

http://msdn.microsoft.com/en-us/library/ms188263.aspx

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-05 : 02:57:01

[code]
INSERT INTO myDB2 ([some_field]) ~~VALUES (5)~~
SELECT myID
FROM myDB
WHERE myName = 'john doe'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Alban_T
Starting Member

3 Posts

Posted - 2011-10-05 : 03:37:50

Thnx for the answers but I already tried those and they work partially. Maybe I should have used a slightly more realistic version of my query in my original post.

INSERT INTO myDB2 ([some_field])
SELECT myID
FROM myDB
WHERE myName = 'john doe'

the query above works, but my "real" query has more than 1 value. From these values only 1 is a SELECT query.

INSERT INTO myDB2 ([some_field],[some_other_field) VALUES (5,'qwerty')

INSERT INTO myDB2 ([some_field])
SELECT myID
FROM myDB
WHERE myName = 'john doe','qwerty'

above results in error (SQL2005):
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-05 : 03:41:42

[code]
WHERE myName in ( 'john doe','qwerty' )
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Alban_T
Starting Member

3 Posts

Posted - 2011-10-05 : 04:44:35

won't it look for 'qwerty' in myDB? Oops I also found a typo in my last posted query.
the 'qwerty' is a normal value, not retrieved using SELECT.

If I understood the msdn documentation correctly I can either use value(1,'qwerty') OR a SELECT query. When I stopped thinking about it an idea popped up, how about a SELECT query which contains my static data as well:
SELECT myID, 'qwerty' AS Expr1 FROM myDB;

This would result in:
I figured something like:

INSERT INTO myDB2 ([some_field],[some_other_field)
SELECT myID, 'qwerty' AS Expr1
FROM myDB;

Havn't had time yet to test it with the real DB but SQL manager gives no errors when parsing.....

Thanks for the answers and pointing me into the right direction!!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-05 : 04:52:23

yes. you can do that also.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 04:53:11

the last query is correct and would insert records with myID values from myDb for first field and constant value 'qwerty' for second field in myDB2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources