How To Set a Variable During an Insert Into Select From

15 Dec 2005 9:22 PM

RitaG

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

15 Dec 2005 9:52 PM

Tibor Karaszi

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/

Show quote

"RitaG" <Ri***@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
>

16 Dec 2005 3:25 PM

RitaG

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

Show quote

"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" <Ri***@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
> >
>
>

16 Dec 2005 4:13 PM

Scott Morris

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 ...

16 Dec 2005 4:25 PM

RitaG

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

Show quote

"Scott Morris" wrote:

> 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 ...
>
>
>