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
 Site Related Forums
 Article Discussion
 INSERT with subSelect?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mar
Starting Member

45 Posts

Posted - 06/12/2012 :  14:48:20  Show Profile  Reply with Quote
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
52325 Posts

Posted - 06/17/2012 :  15:10:07  Show Profile  Reply with Quote
are you using sql 2005 or above?

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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/17/2012 :  15:48:18  Show Profile  Reply with Quote

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/

Go to Top of Page

cjmorgant110
Starting Member

USA
2 Posts

Posted - 07/18/2012 :  12:23:23  Show Profile  Reply with Quote
I live on this page. Much more complete that what Microsoft provides...keep up the good work!.

CJ Morgan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/18/2012 :  12:29:04  Show Profile  Reply with Quote
thanks

------------------------------------------------------------------------------------------------------
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.08 seconds. Powered By: Snitz Forums 2000