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

• JonkoLainen

  SSC Journeyman

  Points: 97

  More actions

  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

• Jesper-244176

  SSCertifiable

  Points: 7142

  More actions

  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

   

• Eric Brochu-281909

  SSC Veteran

  Points: 240

  More actions

  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

• Jesper-244176

  SSCertifiable

  Points: 7142

  More actions

  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