query then insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query then insert

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2006-12-21 : 08:19:15

i am running a pretty crazy query with a couple on inner joins

lets say it returns 20 results that i then want to insert into a different table

how do i do this -- is there a way to do it with out cursors?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 08:23:37

Yes, just write INSERT ... stuff here...
before the select thingy.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-21 : 08:27:13

Insert into Target_table(columns)
Select columns from
(
Your inner join query
) T

Madhivanan

Failing to plan is Planning to fail

tpiazza55
Posting Yak Master

162 Posts

Posted - 2006-12-21 : 08:30:28

ok -- how do i do the values part of the insert

insert into table1(columns1, columns2) values ( value1, values2)
select stuff here?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 08:35:48

YOU DON'T!

There are two ways to insert values into a table.

1) INSERT SomeTable (Col1, Col2) VALUES (val1, val2)
2) INSERT SomeTable (Col1, Col2) SELECT val1, val2 FROM SomeTable

Peter Larsson
Helsingborg, Sweden

tpiazza55
Posting Yak Master

162 Posts

Posted - 2006-12-21 : 08:43:00

i had an extra column that was why i was getting an error -- thanks alot

is there a way to run a second delete query -- where it deletes a record in one table and insert a record -- both based on the query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 08:45:16

Yes, but then you must store the data temporarily in a table, becuse it is not possible to do both delete and insert at the same time.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 08:45:54

Unless you are talking about a UPDATE?
Update a record with column to new values?

Peter Larsson
Helsingborg, Sweden

tpiazza55
Posting Yak Master

162 Posts

Posted - 2006-12-21 : 08:46:27

basically i have an exclude table that im checking against and if the data matches the other tables insert into the include -- hence the question above -- is there a way to send a delete also

i will need to grab the id variable which will make it have one more value in the select than in the insert

tpiazza55
Posting Yak Master

162 Posts

Posted - 2006-12-21 : 08:48:51

how would i do the delete in a temp table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 08:50:36

You better post here some sample data and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

tpiazza55
Posting Yak Master

162 Posts

Posted - 2006-12-21 : 09:00:48

ok here is the insert on the query -- what i need to do is grab the exclude_id from the exclude table and delete that record -- basically get all the info insert it into the include table and then delete the record from the exclude table

INSERT INTO include(Distributor_CONO, Distributor_CSNO, ITNO, Qty, ShipDate, Customer_CONO, Customer_CSNO, FileName, Import_Date, Imported_By, Price)

SELECT

exclude.Distributor_cono, exclude.Distributor_csno, ref.DSIITNO, exclude.Qty, exclude.ShipDate, exclude.Distributor_cono AS Expr1, cust.DSICSNO, exclude.FileName, exclude.Import_Date, exclude.Imported_By, exclude.Price

FROM Exclude exclude

INNER JOIN productRef ref ON ref.CONO = exclude.Distributor_cono AND ref.CSNO = exclude.Distributor_csno AND ref.CSITNO = exclude.DistItemNo AND ref.Exclude <> 1 AND ref.DSIITNO IS NOT NULL

INNER JOIN customerRef cust ON cust.CONO = exclude.Distributor_cono AND cust.CSNO = exclude.Distributor_csno AND cust.CSName = exclude.shipCustName AND cust.DSICONO = exclude.Distributor_cono AND cust.DSICSNO IS NOT NULL AND cust.Exclude <> 1

tpiazza55
Posting Yak Master

162 Posts

Posted - 2006-12-21 : 10:56:56

well i gave up and went with cursors

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 11:00:44

It might help if you explained how to use the EXCLUDE_ID column...

Try using plain english what you are trying to accomplish.

You have a table with values? You want to delete some records in another table using some ID from the first table?

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-22 : 18:30:53

quote:
Originally posted by Peso

YOU DON'T!

There are two ways to insert values into a table.

1) INSERT SomeTable (Col1, Col2) VALUES (val1, val2)
2) INSERT SomeTable (Col1, Col2) SELECT val1, val2 FROM SomeTable

Peter Larsson
Helsingborg, Sweden

also there is INSERT SomeTable EXEC SomeProc

www.elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-22 : 22:58:04

<<
also there is INSERT SomeTable EXEC SomeProc
>>

Provided the result returned by executing procedure identical to the table structure

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources