Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Combine INSERT and SELECT

Posted on 2003-11-18
5
Medium Priority
?
688 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:tariqf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
lausz earned 300 total points
ID: 9771600
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'

0
 
LVL 1

Author Comment

by:tariqf
ID: 9771657
nice one! does exactly what it says on the tin :)
0
 
LVL 6

Expert Comment

by:lausz
ID: 9771663
Glad I could help ...but why do you select a 'B'? It is only curiousity!
0
 
LVL 1

Author Comment

by:tariqf
ID: 9771885
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?
0
 
LVL 6

Expert Comment

by:lausz
ID: 9772435
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
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question