How To Set a Variable During an Insert Into Select From

Hello.
I'm inserting rows into a table that I retrieve from another table.
There's a lot of data manipulation going on during this process.
For 10 columns in the Select From portion I'm using a CASE statement that
starts with CASE
WHEN Left(Discount_Specification, 2)= @.PF THEN etc.
END,
Instead of doing the "Left" 10 times (10 * 8 million rows in the "From"
table!) I though of setting a variable: Set @.MyVar =
Left(Discount_Specification, 2) and then
saying WHEN @.MyVar = @.PF etc.
I just don't know where in the logic to place this Set @.MyVar so it works
for each row that's inserted.
TIA,
RitaHard to say without DDL, but perhaps something like:
INSERT INTO ...
SELECT ds, ds + 'a', col2
FROM
(
SELECT LEFT(Discout_Specification, 2) AS ds, col2 FROM tbl
) AS t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:A07835AD-AAC2-4225-BA8C-FDA70C1C0631@.microsoft.com...
> Hello.
> I'm inserting rows into a table that I retrieve from another table.
> There's a lot of data manipulation going on during this process.
> For 10 columns in the Select From portion I'm using a CASE statement that
> starts with CASE
> WHEN Left(Discount_Specification, 2)= @.PF THEN etc.
> END,
> Instead of doing the "Left" 10 times (10 * 8 million rows in the "From"
> table!) I though of setting a variable: Set @.MyVar =
> Left(Discount_Specification, 2) and then
> saying WHEN @.MyVar = @.PF etc.
> I just don't know where in the logic to place this Set @.MyVar so it works
> for each row that's inserted.
> TIA,
> Rita
>|||Hi Tibor,
Thanks for your response.
I'm trying to figure out how to use it along with a CASE statement.
Here's my code:
INSERT INTO MyTable(
Col1,
Col2,
etc.)
SELECT
CASE
WHEN Left(SM.Discount_Specification, 2) IN (@.P, @.L) THEN
Something
ELSE 1
END,
CASE
WHEN Left(SM.Discount_Specification, 2) = @.K THEN
SomethingElse
ELSE 1
END,
Etc.
From MyTable
Thanks,
Rita
"Tibor Karaszi" wrote:

> Hard to say without DDL, but perhaps something like:
> INSERT INTO ...
> SELECT ds, ds + 'a', col2
> FROM
> (
> SELECT LEFT(Discout_Specification, 2) AS ds, col2 FROM tbl
> ) AS t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RitaG" <RitaG@.discussions.microsoft.com> wrote in message
> news:A07835AD-AAC2-4225-BA8C-FDA70C1C0631@.microsoft.com...
>|||Is this a "lazy programmer doesn't want to type all those keystrokes" issue
or something else? It is possible that "Left(SM.Discount_Specification, 2)"
indicates a schema issue. If so, you should consider a change to the schema
to unbind the two attributes currently stored in the Discount_Specification
column. This can be done permanently via the addition of another column
(and the movement of the associated information), via a view, or via a
computed column, via a udf, etc. You can also do this via a derived table
within this particular query.
insert ...
select case when derived_discount in (@.P, @.L) then x else y end,
...
from
(select Left(SM.Discount_Specification, 2) as derived_discount,
...
from MyTable ) as t1
where ...|||Hi Scott,
No, it's not a "lazy programmer"! :-)
I just thought there may be a more efficient way since I'm dealing with a
large volume of rows (up to 10 million).
Thanks for your reponse. That was what I was looking for.
Rita
"Scott Morris" wrote:

> Is this a "lazy programmer doesn't want to type all those keystrokes" issu
e
> or something else? It is possible that "Left(SM.Discount_Specification, 2
)"
> indicates a schema issue. If so, you should consider a change to the sche
ma
> to unbind the two attributes currently stored in the Discount_Specificatio
n
> column. This can be done permanently via the addition of another column
> (and the movement of the associated information), via a view, or via a
> computed column, via a udf, etc. You can also do this via a derived table
> within this particular query.
> insert ...
> select case when derived_discount in (@.P, @.L) then x else y end,
> ...
> from
> (select Left(SM.Discount_Specification, 2) as derived_discount,
> ...
> from MyTable ) as t1
> where ...
>
>