Insert using subsqueries

14 Jul 2006 5:49 PM

KatMagic

How can I do an insert, pulling data from one table to another, plus add
more values -- if possible?

For instance, I have a client table, with the id field as the primary key.
I have tbl2, that references the id field, plus 3 other fields: Cost, Date,
Comments
I need to do inserts:
    insert into tbl2 (ClientID)
        (Select id from tblClient where orgname='Microsoft')

but also want to add in the cost, date, comments values
    insert into tbl2 (ClientID, cost)
        (Select id from tblClient where orgname='Microsoft'), $100    --
?????

How would I do that without having to do 2 queries?

Thank you for your help.

14 Jul 2006 5:57 PM

Arnie Rowland

You are 'almost' there. A small tweak will point you the the right direction.

INSERT INTO tbl2
   (   ClientID
     , Cost
     , Datecolumn
     , SomeTextColumn
   )
   SELECT
        [ID]
      , 100
      , getdate()
      , 'TextLiteral'
   FROM tblClient
   WHERE OrgName = 'Microsoft'

Just include the values and/or variables in the SELECT statement.

--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."

Show quote

"KatMagic" <SSKatMa***@yahoo.com> wrote in message news:%234aYr22pGHA.4988@TK2MSFTNGP04.phx.gbl...
> How can I do an insert, pulling data from one table to another, plus add
> more values -- if possible?
>
> For instance, I have a client table, with the id field as the primary key.
> I have tbl2, that references the id field, plus 3 other fields: Cost, Date,
> Comments
> I need to do inserts:
>    insert into tbl2 (ClientID)
>        (Select id from tblClient where orgname='Microsoft')
>
> but also want to add in the cost, date, comments values
>    insert into tbl2 (ClientID, cost)
>        (Select id from tblClient where orgname='Microsoft'), $100    --
> ?????
>
> How would I do that without having to do 2 queries?
>
> Thank you for your help.
>
>
>
>

14 Jul 2006 5:59 PM

Steve Kass

insert into tbl2(ClientID, cost)
select id, $100 from tblClient where orgname = 'Microsoft'

Steve Kass
Drew University
www.stevekass.com

Show quote

"KatMagic" <SSKatMa***@yahoo.com> wrote in message
news:%234aYr22pGHA.4988@TK2MSFTNGP04.phx.gbl...
> How can I do an insert, pulling data from one table to another, plus add
> more values -- if possible?
>
> For instance, I have a client table, with the id field as the primary key.
> I have tbl2, that references the id field, plus 3 other fields: Cost,
> Date, Comments
> I need to do inserts:
>    insert into tbl2 (ClientID)
>        (Select id from tblClient where orgname='Microsoft')
>
> but also want to add in the cost, date, comments values
>    insert into tbl2 (ClientID, cost)
>        (Select id from tblClient where orgname='Microsoft'), $100    --
> ?????
>
> How would I do that without having to do 2 queries?
>
> Thank you for your help.
>
>
>
>

14 Jul 2006 5:59 PM

Tracy McKibben

KatMagic wrote:
Show quote

> How can I do an insert, pulling data from one table to another, plus add
> more values -- if possible?
>
> For instance, I have a client table, with the id field as the primary key.
> I have tbl2, that references the id field, plus 3 other fields: Cost, Date,
> Comments
> I need to do inserts:
>     insert into tbl2 (ClientID)
>         (Select id from tblClient where orgname='Microsoft')
>
> but also want to add in the cost, date, comments values
>     insert into tbl2 (ClientID, cost)
>         (Select id from tblClient where orgname='Microsoft'), $100    --
> ?????
>
> How would I do that without having to do 2 queries?
>
> Thank you for your help.
>
>
>
>

Specify the additional values as constants...

INSERT INTO tbl2
(ClientID, Cost)
SELECT id, 100.00
FROM tblClient
WHERE orgname = 'Microsoft'

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com