Conditional Statements in the INSERT-SELECT statement!

Conditional Statements in the INSERT-SELECT statement!

Post by Tibor Karasz » Thu, 31 Oct 2002 23:28:50



See my reply in .server. Please don't multipost.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


Quote:

> Hi

> I have a table into which I am doing a normal 'Insert Into and
> Select'....the problem is that one of the fields that I am inserting
> contains a value

> that I wish to replace and then insert the new value instead...

> e.g.

> Insert Into Table A

> (Field A, Field, etc ....)

> Select Field A, Field B ---- if Field B has a value of '234' for a
> particular record then I

> want to be able to substitute that with a new default value e.g.
> '7'.....?????

> Similar to using COALESCE, but this is not replacing NULL values....

> Any ideas... ???

> --
> Kind Regards

> Rikesh
> (Using Win2K/VB6/SQL2K)

 
 
 

1. insert statement using values from a select statement

Hi there,

I have some old ASP code which uses 3 stored procedures.

I want to replace them with one stored procedure.

Here is what I need to do
-------------------------
Check a table for an instance of a stock code

if record in table then exit stored procedure returning a 0 in a recordset.

else

use a stock code to get info out of table, ie: price, description, vat etc.

then use the info in this select statement in an insert statement.

here is what I have so far
-------------------------


AS


BEGIN
/*  ADD ITEM TO BASKET*/

SELECT   v.vat_rate, p.stock_code, p.[description], p.case_weight, pr.price
FROM tbl_products p  INNER JOIN ref_vat_codes v ON p.vat_code = v.vat_code
INNER JOIN view_customer_products_prices pr ON p.stock_code = pr.stock_code

/*
INSERT INTO tbl_www_basket  (session_id, stock_code, price, qty_ordered,

p.case_weight)
*/

The error I get... I think referers to pr.price and so on. - HELP PLEASE!!!

Error 128: The name 'price' is not permitted in this context. Only
constants, expressions, or variables allowed here. Column names are not
permitted.

END

ELSE
BEGIN
/*  ITEM ALREADY IN BASKET*/

END


GO

Cheers

Chris

2. Dates without times and select

3. USING A FIELD FROM MAIN SELECT STATEMENT IN SUB SELECT STATEMENT

4. MS SQL Performance

5. select statement that includes a conditional

6. This is a must visit site!

7. Conditional Select statement

8. US-CA-WEB SAVVY DEVELOPER'S APPLY HERE!

9. Conditional COUNT in a SELECT Statement

10. Conditional select statement!

11. Conditional Select statement

12. Conditional execution of a select statement