SQL Server Performance

insert, update

Discussion in 'SQL Server 2005 General Developer Questions' started by arkiboys, Feb 5, 2010.

  1. arkiboys Member

    Hello,
    There are thousand of records in tblMain and I am showing a few of those below...
    There are many records which are identical except for their Currency field.
    For every duplicates except the currency field, I would like to set the Active field to 1 and then create one similar record which has both the currencies and set the Active to 1
    Please see below...
    Can you please let me know how to get to the result?
    Thanks


    declare @tblMain table
    (
    Code varchar(20),
    MyID smallint,
    Name varchar(20),
    process varchar(20)
    currency1 varchar(20)
    currency2 varchar(20),
    Active bit
    )

    insert into @tblMain(Code, MyID, Name, process, currency1, currency2, Active)
    select '0700099108636', 1, 'john', 'sab', 'USD', 1
    union
    select '0700099108636', 1, 'john', 'sab', 'GBP', 1
    union
    select '0700099120616', 1, 'jo', 'asa, 'EUR', 1
    union
    select '0700099120616', 1, 'jo', 'asa, 'RUB', 1
    union
    select '6800066728914', 1, 'Dave', 'kwa', 'GBP', 1
    union
    select '6800066728914', 1, 'Dave', 'kwa', 'USD', 1
    union
    select '123456', 1, 'Dave', 'kwa', 'USD', 1
    union
    select '123456', 1, 'Dave', 'kwa', 'PLN', 1
    union
    select '123456', 1, 'Dave', 'kwa', 'PLN', 1
    union
    select '123456', 1, 'Dave', 'kwa', 'USD', 1

    select * from @tblMain

    --RESULT

    select '0700099108636', 1, 'john', 'sab', 'USD', NULL, 0
    union
    select '0700099108636', 1, 'john', 'sab', 'GBP', NULL, 0
    union
    select '0700099108636', 1, 'john', 'sab', 'USD', 'GBP', 1
    union
    select '0700099120616', 1, 'jo', 'asa', 'EUR', NULL, 0
    union
    select '0700099120616', 1, 'jo', 'asa', 'RUB', NULL, 0
    union
    select '0700099120616', 1, 'jo', 'asa', 'EUR', 'RUB', 1
    union
    select '6800066728914', 1, 'Dave', 'kwa', 'GBP', NULL, 0
    union
    select '6800066728914', 1, 'Dave', 'kwa', 'USD', NULL, 0
    union
    select '6800066728914', 1, 'Dave', 'kwa', 'GBP', 'USD', 1
    union
    select '123456', 1, 'Dave', 'kwa', 'USD', NULL, 0
    union
    select '123456', 1, 'Dave', 'kwa', 'PLN', NULL, 0
    union
    select '123456', 1, 'Dave', 'kwa', 'PLN', NULL, 0
    union
    select '123456', 1, 'Dave', 'kwa', 'USD', NULL, 0
    union
    select '123456', 1, 'Dave', 'kwa', 'USD', 'PLN', 1
  2. preethi Member

    Hi,
    Your question is still unclear to me. Unfortunately, the query you have submitted too have errors which prevents me from executing.
    One of the prominent error is you are specifying 7 columns in the insert statement but provide only 6 columns.
    Please correct your query and try to explain the question in different words.
    Thanks.

Share This Page