On 4 Aug 2006 07:17:54 -0700, "Jo" <jodiep***@gmail.com> wrote:

>Hi there:
>
>I want to insert using a cursor.
>I have a table
>
>Services:
>
>ServiceId    ServiceName  SupplierId     TextType1    TextType2
>    Flag
>44            SomeName   787878       null
>asdaasdasd      Null
>45            OtherName  324342          asdasdasdas      nulll
>        Null
>46     asdadasd             7878766      yyyyyyy               yyyyyyy
>       Null
>47       asdasdasdii          898888     uuuuuuuu
>yyyyyy      Null
>
>
>What i need to do is copy information to another table, lets call it
>INFO with the following structure
>
>ServiceID       SupplierID     Name     Text
>
>
>
>ServiceID, supplierID and Name can go as they are, the problem comes in
>text
>basically IF texttype1 is empty then texttype2 should be in the text
>column of the INFO table, if textype1 has some values they should go to
>INFO.Text and disregard the null values of TextType2 if TextType1 =
>TextType2 then that value should be inseerted to text column, but if
>TextType1 and TextType2 have values  2 rows should be inserted where
>serviceID, SupplierID and Name are the same and on the first row we
>have textType1 and in the second row text type2 if this happens i have
>also to update the Flag in the services table
>
>thanks in advance for your help
>
>
>J

> No need for cursors at all.  The simple answer is to use two INSERTs.
>
> INSERT Whatever
> SELECT ServiceID,
>        SupplierID,
>        Name = ServiceName,
>        Text = CASE WHEN TextType1 <> ' ' THEN TextType1
>                    ELSE TextType2
>               END
>   FROM Services
>
> INSERT Whatever
> SELECT ServiceID,
>        SupplierID,
>        Name = ServiceName,
>        Text = TextType2
>   FROM Services
>  WHERE TextType1 <> ' '
>    AND TextType1 <> TextType2

>Roy Harvey wrote:
>> No need for cursors at all.  The simple answer is to use two INSERTs.
>>
>> INSERT Whatever
>> SELECT ServiceID,
>>        SupplierID,
>>        Name = ServiceName,
>>        Text = CASE WHEN TextType1 <> ' ' THEN TextType1
>>                    ELSE TextType2
>>               END
>>   FROM Services
>>
>> INSERT Whatever
>> SELECT ServiceID,
>>        SupplierID,
>>        Name = ServiceName,
>>        Text = TextType2
>>   FROM Services
>>  WHERE TextType1 <> ' '
>>    AND TextType1 <> TextType2

> On 4 Aug 2006 07:53:42 -0700, "Jo" <jodiep***@gmail.com> wrote:
>
> >Hi Roy:
> >
> >Thanks for your answer, I would really prefer not to use cursor but in
> >your solution
> >when TextType1 and TextType2 have different values? if I run it in the
> >order displayed then i ll loose TextType2 values, and I need to keep
> >them
>
> Well, one of us is obviously reading my code differently than the
> other.  I thought - think - that the second INSERT was dealing with
> that.
>
> >Roy Harvey wrote:
> >> No need for cursors at all.  The simple answer is to use two INSERTs.
> >>
> >> INSERT Whatever
> >> SELECT ServiceID,
> >>        SupplierID,
> >>        Name = ServiceName,
> >>        Text = CASE WHEN TextType1 <> ' ' THEN TextType1
> >>                    ELSE TextType2
> >>               END
> >>   FROM Services
> >>
> >> INSERT Whatever
> >> SELECT ServiceID,
> >>        SupplierID,
> >>        Name = ServiceName,
> >>        Text = TextType2
> >>   FROM Services
> >>  WHERE TextType1 <> ' '
> >>    AND TextType1 <> TextType2
>
> In the meanwhile I have thought of an alternative that uses one INSERT
> and is simpler.
>
> INSERT Whatever
> SELECT ServiceID,
>        SupplierID,
>        Name = ServiceName,
>        Text = TextType1
>   FROM Services
>  WHERE TextType1 <> ' '
> UNION
> SELECT ServiceID,
>        SupplierID,
>        Name = ServiceName,
>        Text = TextType2
>   FROM Services
>  WHERE TextType2 <> ' '
>
> Note that it must be UNION, not UNION ALL.  UNION performs a DISTINCT,
> which resolves the case where the two are equal.
>
> Roy Harvey
> Beacon Falls, CT