469,090 Members | 1,196 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Insert based on a select query

Hi,

I have a SQL query:

select products.name, products.notes, products.purchase_date,
products.serial_no, products.total_value, products.product_code,
products.quantity, products.product_group, products.lhs1_name,
lhs1.department, lhs1.address1, lhs1.city, lhs1.country
from products, lhs1
where products.lhs1_id = lhs1.id

I want to insert the results of this query into a table called 'temp' in the
database. I used to copy and paste this into excel then import it but it
doesn't always work.

Is there a way to do it all in a SQL script. Please be aware that my
knowledge of SQL is fairly basic so please explain things clearly.

Thanks,

Darren
Sep 20 '05 #1
4 13563
INSERT INTO Temp
select products.name, products.notes, products.purchase_date,
products.serial_no, products.total_value, products.product_code,
products.quantity, products.product_group, products.lhs1_name,
lhs1.department, lhs1.address1, lhs1.city, lhs1.country
from products, lhs1
where products.lhs1_id = lhs1.id

if it does not exists at runtime:

SELECT
products.name, products.notes, products.purchase_date,
products.serial_no, products.total_value, products.product_code,
products.quantity, products.product_group, products.lhs1_name,
lhs1.department, lhs1.address1, lhs1.city, lhs1.country
from products, lhs1
where products.lhs1_id = lhs1.id
INTO Temp
HTH, Jens Suessmeyer.

Sep 20 '05 #2
Hi Jens,

Thanks for the quick reply. Your help is much appreciated. What would I need
to do in order to insert the results into a table called 'temp' that resides
in a different database e.g. Logi?

Thanks

"Jens" <Je**@sqlserver2005.de> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
INSERT INTO Temp
select products.name, products.notes, products.purchase_date,
products.serial_no, products.total_value, products.product_code,
products.quantity, products.product_group, products.lhs1_name,
lhs1.department, lhs1.address1, lhs1.city, lhs1.country
from products, lhs1
where products.lhs1_id = lhs1.id

if it does not exists at runtime:

SELECT
products.name, products.notes, products.purchase_date,
products.serial_no, products.total_value, products.product_code,
products.quantity, products.product_group, products.lhs1_name,
lhs1.department, lhs1.address1, lhs1.city, lhs1.country
from products, lhs1
where products.lhs1_id = lhs1.id
INTO Temp
HTH, Jens Suessmeyer.

Sep 20 '05 #3
Refering to the four part notation this is gernerally:

[servername].[databasename].[owner].[objectname]

SELECT
products.name, products.notes, products.purchase_date,
products.serial_no, products.total_value, products.product_code,
products.quantity, products.product_group, products.lhs1_name,
lhs1.department, lhs1.address1, lhs1.city, lhs1.country
from products, lhs1
where products.lhs1_id = lhs1.id
INTO logi..Temp

HTH, Jens Suessmeyer.

Sep 20 '05 #4
>> Products.lhs1_id = lhs1.id <<

Why did a data element change names from table to table? Why do so
many of your data elements violate the rules of data modeling and
ISO-11179?

I would bet that this magical table, Temp, is not needed but is used to
mimic a scratch tape in a procedural solution. It is a common Newbie
error. You probably ought to get some help on the entire schema.

Otherwise, go to BOL and look up INSERT INTO .. SELECT..

Sep 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by traceable1 | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
2 posts views Thread by maltchev | last post: by
6 posts views Thread by efgh | last post: by
10 posts views Thread by Serdge Kooleman | last post: by
8 posts views Thread by nano2k | last post: by
2 posts views Thread by lenygold via DBMonster.com | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.