Run a query for each resault in parent query

[SBSP]

Lets say i have an Inventory table with 2 fields (Our inventory dont really only have 2 fields just keeping it simple)

[B]ITEM_NUMBER[/B]     [B]ITEM_DESC[/B]
1234ZZ000AA     Wifi Keyboard 
1234ZZ000AB     Wifi Keyboard

The above 2 items are the SAME item, Lets say the first item came out the factory
Then the second one had a firmware change to improve it, but its still the exact same item.

Now i want insert them, into our database Price List Table (Which is now empty)
Problem is our price list that the marketing people create in excel will always have the newest Item on it.
They dont keep both items on there because that would make it tremendously long.

So now i need to first return the items from the Inventory

Like this

Select ITEM_NUMBER , ITEM_DESC from INVENTORY Where ITEM_NUMBER like substring(ITEM_NUMBER1,8) + '%'

- Not sure if my code is exactly right but its the thought that counts.


So then for each resault i want to insert each item into the price list into the database

 

[xumwun]

something like

insert into table (fields)
Select ITEM_NUMBER , ITEM_DESC from INVENTORY Where ITEM_NUMBER like substring(ITEM_NUMBER1,8) + '%'

 

[SBSP]

something like

insert into table (fields)
Select ITEM_NUMBER , ITEM_DESC from INVENTORY Where ITEM_NUMBER like substring(ITEM_NUMBER1,8) + '%'

That will tell you that the sub query is only allowed to give you one result.

Something like.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

 

[xumwun]

What i meant is that you can have the INSERT query take the results of the SELECT query.

 

[DominionZA]

I use CTE's (Common Table Expression) for stuff like this.
Makes it real easy for testing more complex queries.

 

[SBSP]

What i meant is that you can have the INSERT query take the results of the SELECT query.

Thanks!

I didnt understand what you meant.

INSERT INTO TEST (PRICE,ITEM) 
(select '1000', ITEMNO from ICITEM where ITEMNO like '9285A003%' )

If i change Price and Item around is gives an error not sure why though, but this way it works.
so i now have to incorporate it into my StoredProcedure

 

[Nether]

Use a store proc and create some x_result temp tables

 

[FarligOpptreden]

I use CTE's (Common Table Expression) for stuff like this.
Makes it real easy for testing more complex queries.

This.

Use a store proc and create some x_result temp tables

Bad idea. Try to avoid unnecessary writes (and subsequent deletes) in the database. Common Table Expressions are your friend if you're working in MS SQL Server. Think of them as dynamic, execution-time, once-off views. Benefit is the query path would still be cached in the database after the first run, so it should be waaaaaay more efficient than writing to and reading from a temp table.

 

[FarligOpptreden]

Thanks!

I didnt understand what you meant.

INSERT INTO TEST (PRICE,ITEM) 
(select '1000', ITEMNO from ICITEM where ITEMNO like '9285A003%' )

If i change Price and Item around is gives an error not sure why though, but this way it works.
so i now have to incorporate it into my StoredProcedure

The reason you're probably getting an error, is because the columns you're inserting INTO must be the same order as the columns you're selecting FROM.

 

[SBSP]

The reason you're probably getting an error, is because the columns you're inserting INTO must be the same order as the columns you're selecting FROM.

Nha I have tried this it doesnt work.

INSERT INTO TEST (ITEM,PRICE) 
(select  ITEMNO from ICITEM where ITEMNO like '9285A003%' ,'1000')

 

[xumwun]

Nha I have tried this it doesnt work.

INSERT INTO TEST (ITEM,PRICE) 
(select  ITEMNO from ICITEM where ITEMNO like '9285A003%' ,'1000')

That's because you need price in the select query as well.
The columns in the insert query need to the same as the ones in the select query.

 

[DominionZA]

That's because you need price in the select query as well.
The columns in the insert query need to the same as the ones in the select query.

I have used various stock, Omega and CM and they all supported BT Audio with no probs.

 

[mercurial]

I have used various stock, Omega and CM and they all supported BT Audio with no probs.

Wrong thread!

 

[DominionZA]

Wrong thread!

Oh my. How the hell did I get that right

 

[FarligOpptreden]

Nha I have tried this it doesnt work.

INSERT INTO TEST (ITEM,PRICE) 
(select  ITEMNO from ICITEM where ITEMNO like '9285A003%' ,'1000')

You're doing it wrong!

INSERT INTO TEST
(
    [ITEM]
  , [PRICE]
)
SELECT
    [ITEMNO]
  , '1000'
FROM ICITEM
WHERE
    [ITEMNO] LIKE '9285A003%'

No might also be a good time to learn to format your queries uniformly in a readable format like my sample above...

EDIT: Stupid Tapatalk non-pre formatting...

 

[SBSP]

I completed the whole thing and it worked like a charm.

this morning being the 1st of the month i normally backup our live company so that logistics have static stock,
and i overwrote the whole thing!!!! :-(