Insert into table using 2 different SELECT statements fails  
Author Message
kdstratton





PostPosted: Wed Oct 04 10:42:28 CDT 2006 Top

SQL Server >> Insert into table using 2 different SELECT statements fails

This is a multi-part message in MIME format.

------=_NextPart_000_0006_01C6E7D1.C0FC0200
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi=20

I want to do the following Insert statement using 2 seperate Select =
statements

from 2 diferent tables

insert into tblProcessStageEmails

(ProcessID,EmailTemplateID)

select ProcessID from tblProcessLookup where ProcessDescription Like =
'%Awaiting Respsonse%'

select EmailTemplateID,* from tblEmailTemplates where TemplateName like =
'VAT - Awaiting response follow up (Voluntary)'



However this fails for the following reason:

Msg 120, Level 15, State 1, Line 1

The select list for the INSERT statement contains fewer items than the =
insert list. The number of SELECT values must match the number of INSERT =
columns.



What I am doing wrong here?

Thanks

Chris

------=_NextPart_000_0006_01C6E7D1.C0FC0200
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2963" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV>
<P><FONT face=3DVerdana size=3D2>Hi </FONT></P>
<P><FONT face=3DVerdana size=3D2>I want to do the following Insert =
statement using 2=20
seperate Select statements</FONT></P>
<P><FONT face=3DVerdana size=3D2>from 2 diferent tables</FONT></P>
<P><FONT face=3DVerdana size=3D2>insert into =
tblProcessStageEmails</FONT></P>
<P><FONT face=3DVerdana size=3D2>(ProcessID,EmailTemplateID)</FONT></P>
<P><FONT face=3DVerdana size=3D2>select ProcessID from tblProcessLookup =
where=20
ProcessDescription Like '%Awaiting Respsonse%'</FONT></P>
<P><FONT face=3DVerdana size=3D2>select EmailTemplateID,* from =
tblEmailTemplates=20
where TemplateName like 'VAT - Awaiting response follow up=20
(Voluntary)'</FONT></P>
<P><FONT face=3DVerdana size=3D2></FONT>&nbsp;</P>
<P><FONT face=3DVerdana size=3D2>However this fails for the following=20
reason:</FONT></P><FONT size=3D1>
<P><FONT size=3D3>Msg 120, Level 15, State 1, Line 1</FONT></P>
<P><FONT size=3D3>The select list for the INSERT statement contains =
fewer items=20
than the insert list. The number of SELECT values must match the number =
of=20
INSERT columns.</FONT></P>
<P><FONT size=3D3></FONT>&nbsp;</P>
<P><FONT size=3D3>What I am doing wrong here?</FONT></P>
<P><FONT face=3DArial size=3D2>Thanks</FONT></P>
<P><FONT face=3DArial =
size=3D2>Chris</FONT></P></FONT></DIV></BODY></HTML>

------=_NextPart_000_0006_01C6E7D1.C0FC0200--

SQL Server12  
 
 
Paul





PostPosted: Wed Oct 04 10:42:28 CDT 2006 Top

SQL Server >> Insert into table using 2 different SELECT statements fails This is a multi-part message in MIME format.

------=_NextPart_000_001B_01C6E7D4.148F81B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Chris,
what is the schema of the 2 tables and how are they related? It looks =
like you need a join between the 2 tables and to pull ProcessID from =
tblProcessLookup and EmailTemplateID from tblEmailTemplates. =
Alternatively perhaps both columns exist in each table. Anyway, my guess =
(sorry but that's all it can be until you post up the schema of the 2 =
tables and explain their relationship) is that that the former is true =
and you'll need something like this:

insert into tblProcessStageEmails(ProcessID,EmailTemplateID)
select tblProcessLookup.ProcessID, tblEmailTemplates.EmailTemplateID
from tblProcessLookup inner join tblEmailTemplates on =
tblProcessLookup.colx =3D tblEmailTemplates.colx=20
where colx is the common column.

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
------=_NextPart_000_001B_01C6E7D4.148F81B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2963" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Chris,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>what is the schema of the 2 tables and =
how are they=20
related? It looks like you need a join between the 2 tables and to pull =
<FONT=20
face=3DVerdana>ProcessID from tblProcessLookup and EmailTemplateID from=20
tblEmailTemplates. Alternatively perhaps both columns exist in each =
table.=20
Anyway, my guess (sorry but that's all it can be until you post up the =
schema of=20
the 2 tables and explain their relationship) is that that the former is =
true and=20
you'll need something like this:</FONT></FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DVerdana size=3D2>insert into=20
tblProcessStageEmails(ProcessID,EmailTemplateID)</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>select tblProcessLookup.ProcessID,=20
tblEmailTemplates.EmailTemplateID</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>from tblProcessLookup inner join=20
tblEmailTemplates on tblProcessLookup.colx =3D=20
tblEmailTemplates.colx&nbsp;</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>where colx is the common =
column.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;&nbsp;&nbsp;&nbsp;=20
Cheers,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Paul =
Ibison=20
SQL Server MVP, <A href=3D"">www.replicationanswers.com</A> . =
</DIV></BODY></HTML>

------=_NextPart_000_001B_01C6E7D4.148F81B0--

 
 
Keith





PostPosted: Wed Oct 04 10:53:22 CDT 2006 Top

SQL Server >> Insert into table using 2 different SELECT statements fails This is a multi-part message in MIME format.

------=_NextPart_000_0042_01C6E7A3.4F952DE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Your sql statement is wrong.

SQL Server is trying to treat your statement as two blocks of code
INSERT INTO ....SELECT....
and=20
SELECT ....

You are only selecting ProcessID from tblProcessLookup......
What value do you want to insert into EmailTemplateID?
If there is only one valid EmailTemplateID within tblEmailTemplates that =
match the where clause that you listed?

By the way, your WHERE clause isn't correct either.
When you use LIKE you should use a wildcard character when you mean =
like.
Since you used the % in the first select but not the second, I assume =
that the second statement isn't a LIKE, it should be =3D


Is there only one EmailTemplateID within tblEmailTemplates WHERE =
TemplateName =3D 'VAT - Awaiting response follow up (Voluntary)'
If so, you could do something like this:



FROM tblEmailTemplates=20
WHERE TemplateName =3D 'VAT - Awaiting response follow up (Voluntary)'

INSERT INTO tblProcessStageEmails (ProcessID,EmailTemplateID)

FROM tblProcessLookup=20
WHERE ProcessDescription Like '%Awaiting Respsonse%'


Here is another method that you might try:

INSERT INTO tblProcessStageEmails (ProcessID,EmailTemplateID)
SELECT ProcessID, EmailTemplateID=20
FROM tblProcessLookup=20
CROSS JOIN tblEmailTemplates=20
WHERE ProcessDescription Like '%Awaiting Respsonse%'
AND TemplateName =3D 'VAT - Awaiting response follow up (Voluntary)'


Of course you should test these scripts (or any scripts that anyone =
gives you) within a development server before running them on a =
production system.



--=20
Keith Kratochvil




Hi=20

I want to do the following Insert statement using 2 seperate Select =
statements

from 2 diferent tables

insert into tblProcessStageEmails

(ProcessID,EmailTemplateID)

select ProcessID from tblProcessLookup where ProcessDescription Like =
'%Awaiting Respsonse%'

select EmailTemplateID,* from tblEmailTemplates where TemplateName =
like 'VAT - Awaiting response follow up (Voluntary)'



However this fails for the following reason:

Msg 120, Level 15, State 1, Line 1

The select list for the INSERT statement contains fewer items than the =
insert list. The number of SELECT values must match the number of INSERT =
columns.



What I am doing wrong here?

Thanks

Chris

------=_NextPart_000_0042_01C6E7A3.4F952DE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2963" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#bfc48a>
<DIV><FONT face=3DArial size=3D2>Your sql statement is =
wrong.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>SQL Server is trying to treat your =
statement as two=20
blocks of code</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>INSERT INTO ....SELECT....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>and </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>SELECT ....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>You are only selecting ProcessID from=20
tblProcessLookup......</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>What value do you want to insert into=20
EmailTemplateID?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>If there is only one valid =
EmailTemplateID within=20
tblEmailTemplates that match the where clause that you =
listed?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>By the way, your WHERE clause isn't =
correct=20
either.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>When you use LIKE you should use&nbsp;a =
wildcard=20
character when you mean like.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Since you used the % in the first =
select but not=20
the second, I assume that the second statement isn't a LIKE, it should =
be=20
=3D</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Is there only one EmailTemplateID =
within=20
tblEmailTemplates WHERE TemplateName =3D <FONT face=3DVerdana>'VAT - =
Awaiting=20
response follow up (Voluntary)'</FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>If so, you could do something like=20
this:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>

/*assumption that it=20
is an int data type*/</FONT></DIV>

EmailTemplateID=20
</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>FROM tblEmailTemplates </FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2>WHERE TemplateName =3D 'VAT - =
Awaiting response=20
follow up (Voluntary)'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>INSERT INTO tblProcessStageEmails=20

<BR>FROM=20
tblProcessLookup <BR>WHERE ProcessDescription Like '%Awaiting=20
Respsonse%'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Here is another method that you might=20
try:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>INSERT INTO tblProcessStageEmails=20
(ProcessID,EmailTemplateID)<BR>SELECT ProcessID, EmailTemplateID =
<BR>FROM=20
tblProcessLookup <BR>&nbsp;CROSS JOIN tblEmailTemplates <BR>WHERE=20
ProcessDescription Like '%Awaiting Respsonse%'<BR>&nbsp;AND TemplateName =
=3D 'VAT=20
- Awaiting response follow up (Voluntary)'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Of course you should test these scripts =
(or any=20
scripts that anyone gives you) within a development server before =
running them=20
on a production system.</DIV>
<DIV><BR></DIV></FONT>
<DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial =
size=3D2></FONT><BR>--=20
<BR>Keith Kratochvil</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Chris Asaipillai" &lt;<A=20
=

et.com</A>&gt;=20

=


<DIV>
<P><FONT face=3DVerdana size=3D2>Hi </FONT></P>
<P><FONT face=3DVerdana size=3D2>I want to do the following Insert =
statement using=20
2 seperate Select statements</FONT></P>
<P><FONT face=3DVerdana size=3D2>from 2 diferent tables</FONT></P>
<P><FONT face=3DVerdana size=3D2>insert into =
tblProcessStageEmails</FONT></P>
<P><FONT face=3DVerdana =
size=3D2>(ProcessID,EmailTemplateID)</FONT></P>
<P><FONT face=3DVerdana size=3D2>select ProcessID from =
tblProcessLookup where=20
ProcessDescription Like '%Awaiting Respsonse%'</FONT></P>
<P><FONT face=3DVerdana size=3D2>select EmailTemplateID,* from =
tblEmailTemplates=20
where TemplateName like 'VAT - Awaiting response follow up=20
(Voluntary)'</FONT></P>
<P><FONT face=3DVerdana size=3D2></FONT>&nbsp;</P>
<P><FONT face=3DVerdana size=3D2>However this fails for the following=20
reason:</FONT></P><FONT size=3D1>
<P><FONT size=3D3>Msg 120, Level 15, State 1, Line 1</FONT></P>
<P><FONT size=3D3>The select list for the INSERT statement contains =
fewer items=20
than the insert list. The number of SELECT values must match the =
number of=20
INSERT columns.</FONT></P>
<P><FONT size=3D3></FONT>&nbsp;</P>
<P><FONT size=3D3>What I am doing wrong here?</FONT></P>
<P><FONT face=3DArial size=3D2>Thanks</FONT></P>
<P><FONT face=3DArial=20
size=3D2>Chris</FONT></P></FONT></DIV></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0042_01C6E7A3.4F952DE0--