Post reply

SQL insert statement question ??? How can this be done

  • December 22, 2005 at 4:28 am

    #102999

     

    Hi All.

     

    I have an insert statement that just wont be OK even though I thought it should be simple one....

    I have one table with a lot o countrycodes like

    ID, CountryCode

    1    SE

    2    GB

    3    FR

    Then I have another table holding different Transactions..each transaction have a description for only one language. This table has got a transactionid,a languageid and a descriptive text. What I would like to do is insert additional rows for each transaction so that each transactionID have got the same description but for all languages existing in my countrycode table(see above).

    Initially my transaction table looks like this:

    TransactionID     CountryCode     DescriptionText

    1                      SE                   trans 1

    2                      FR                   trans2

     

    After the insert operation based on the content of the countrycode table example  above I would like the table to look like:

    TransactionID     CountryCode     DescriptionText

    1                      SE                   trans 1

    1                      GB                   trans 1

    1                      FR                   trans1

    2                      SE                   trans 2

    2                      GB                   trans 2

    2                      FR                   trans2

     

    How can I do this with a single statement ?? Help appreciated

    Cheers

  • December 22, 2005 at 4:42 am

    #611073

    Try this:

     

    declare @ContryCodes table (ID int, ContryCode varchar(2))

    insert @ContryCodes select 1, 'SE'

    insert @ContryCodes select 2, 'GB'

    insert @ContryCodes select 3, 'FR'

    declare @Transactions table (TransactionID int, ContryCode varchar(2), DescriptionText varchar(10))

    insert @Transactions select 1, 'SE', 'trans 1'

    insert @Transactions select 2, 'FR', 'trans 2'

    insert @Transactions select t.TransactionId, c.ContryCode, t.DescriptionText

    from @ContryCodes c inner join @Transactions t

    on c.ContryCode != t.ContryCode

    select * from @Transactions order by TransactionID

     

  • December 22, 2005 at 7:10 am

    #611114

    Hi,

    I did not try the following but this could work:

    INSERT INTO TableName (TransactionId,CountryCode,DescriptionText)
    SELECT a.TransactionId,b.CountryCode,a.DescriptionText
    FROM Transaction a
    CROSS JOIN CountryCode b

    HTH,

    Eric

  • December 22, 2005 at 7:15 am

    #611118

    If you add "where a.ContryCode != b.ContryCode" then we have the same solution, more or less

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply