Combine INSERT and SELECT

how do I combine the following 2 statements?

INSERT INTO JL_MACHINE(ServiceTag, DateModified, PCName)
VALUES ('2G40T0J', '18/11/2003 13:52:19', 'LSPC166')

INSERT INTO JL_MACHINE(STAFF) SELECT DIWOR from lawsoft.dbo.STAFF where EARNERNAME = 'john smith'


I tried to put the select from the 2nd statement into a subquery in the VALUES section of the 1st statement, but this will not work giving an error saying "you cannot use subqueries in this context". I am using MS SQL server 2000.

Any help much appreciated.

Tariq
LVL 1
tariqfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lauszCommented:
Try this

INSERT INTO JL_MACHINE(ServiceTag, DateModified, PCName,staff)
SELECT '2G40T0J', '18/11/2003 13:52:19', 'LSPC166' ,DIWOR  
from lawsoft.dbo.STAFF where EARNERNAME = 'john smith'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tariqfAuthor Commented:
nice one! does exactly what it says on the tin :)
lauszCommented:
Glad I could help ...but why do you select a 'B'? It is only curiousity!
tariqfAuthor Commented:
what do you mean 'B'?

also I have tried to put another entry after the select like this

INSERT INTO JL_MACHINE(ServiceTag, DateModified, PCName,staff,manufacturer)
SELECT '2G40T0J',
'18/11/2003 13:52:19',
'LSPC166',
DIWOR from lawsoft.dbo.STAFF where EARNERNAME = 'john smith',
'1'

but it does not work. How do I add more values?
lauszCommented:
You have to change the order...

INSERT INTO JL_MACHINE(ServiceTag, DateModified, PCName,manufacturer,staff)
SELECT '2G40T0J',
'18/11/2003 13:52:19',
'LSPC166', '1' ,
DIWOR
from lawsoft.dbo.STAFF where EARNERNAME = 'john smith'


The first 4 values are hardcoding , and the only one you are selecting from a table is staff, the from condition is for all the query but you are using only staff in your query..The query syntax is always ..

select field1, field2, field3, etc
from table
where .....

you tried to do this and it is wrong


select field1, field2, field3
DIWOR  from table ,
field4, etc
where .....


About the 'B'  I am talking when you accept the answer depend on the result you can select 'a','b','c' ..'b' is not very good and I want to understand why ..


pd: sorry for my English
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.