INSERT with subSelect? - 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

Site Related Forums

Article Discussion

INSERT with subSelect?

Author

Topic

Mar
Starting Member

47 Posts

Posted - 2012-06-12 : 14:48:20

Hello

Just wanted to find out if something can be done since I cant get it to work. I am trying to INSERT with a subSelect but apparently it isn't allowed. I can do the job with 3 statements, but I was wondering if it is possible to do it with one. I tried everything I can think of, so either it can't be done or I couldn't figure it out.

There are 3 tables:
Table 1 contains Phrases and is called tblPhrase.
Table 2 contains Form names and is called tblPhraseForm.
Table 3 contains links between tblPhrase and tblPhraseForm.
This allows a phrase to have more than one parent.

So I made a new form:
INSERT into tblPhraseForm (RecType, PhraseFormID, PhraseFormName, PhraseFormDescription, RevNbr, LastRevBy, LastRevDate)
VALUES('A', NewID(), 'frmNewForm', 'New form that uses phrases', 0, 'user', GetDate())

Then I made a new phrase:
INSERT tblPhrase (RecType, PhraseID, PhraseName, Phrase, RevNbr, LastRevBy, LastRevDate)
values('A', NewID(), 'XXX', 'Don''t drink the milk, it''s spoiled!', 0, 'user', GetDate())

Here's where the problem comes in, I tried to be clever and failed:
INSERT tblPhraseUsage (RecType, PhraseID, PhraseFormID, RevNbr, LastRevBy, LastRevDate)
values('A',
(SELECT PhraseID FROM tblPhrase WHERE PhraseName = 'XXX'),
(SELECT PhraseFormID FROM tblPhraseForm WHERE PhraseFormName = 'frmNewForm'), 0, 'user', GetDate())

I tried using MAX and TOP 1 but they don't help.

It says subqueries not allowed. Then I tried:
SELECT 'A' RecType, PhraseID, (SELECT PhraseFormID FROM tblPhraseForm WHERE PhraseFormName = 'frmNewForm') PhraseFormID, null PhraseSort, 0 RevNbr, 'user' LastRevBy, GetDate() LastRevDate, null DeletedBy, null DeletedDate
INTO tblPhraseUsage
FROM tblPhrase WHERE PhraseName = 'XXX'

Which failed because the existing table is not empty.

I know how to solve the problem but I was just wondering if it can be done in one statment without using a stored procedure

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 15:10:07

are you using sql 2005 or above?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 15:48:18

[code]
INSERT tblPhraseUsage (RecType, PhraseID, PhraseFormID, RevNbr, LastRevBy, LastRevDate)
SELECT p.RecType,
p.PhraseID,
pf.PhraseFormID,
0,
'user',
GetDate()
FROM tblPhrase p
INNER JOIN tblPhraseForm pf
ON p.RecType = pf.RecType
WHERE p.PhraseName = 'XXX'
AND pf.PhraseFormName = 'frmNewForm'
AND p.RecType = 'A'
[/code]

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

cjmorgant110
Starting Member

2 Posts

Posted - 2012-07-18 : 12:23:23

I live on this page. Much more complete that what Microsoft provides...keep up the good work!.

CJ Morgan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 12:29:04

thanks

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources