| Author |
 Topic  |
|
|
Mar
Starting Member
23 Posts |
 Posted - 06/12/2012 : 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
India
47069 Posts |
Posted - 06/17/2012 : 15:10:07
|
are you using sql 2005 or above?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 06/17/2012 : 15:48:18
|
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'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
cjmorgant110
Starting Member
USA
2 Posts |
Posted - 07/18/2012 : 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
India
47069 Posts |
Posted - 07/18/2012 : 12:29:04
|
thanks
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
INSERT with subSelect?
