Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    52

    Unanswered: Join into new data

    Hi guys,

    I need help with joining 2 tables and putting the result into a new table.
    Table export has the following columns:
    Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k

    Table cc_split has the following columns:
    Company, Fun_Account, CC_Account, x, y, Split

    The new table called export2 has exactly the same values as export i.e. :
    Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k

    So what I need to join from export and split into export2 are company, cc_account (that shall go into account column of export2 together with the account of export) and the amount, which shall be ( split column * amount column of export), the rest values shall be the same.

    So what I have so far is

    Code:
     INSERT INTO EXP_DATA_2(PERIOD, ACTUALITY, COMPANY, CURRENCY, ACCOUNT, AMOUNT,
                                                    EXTDIM1, EXTDIM2, EXTDIM3,EXTDIM4,JOURNAL_TYPE,C_COMPANY,
                                                    COMPANY,C_DIM, TRANAMOUNT, TRANCURR,REGION, TRANSF_DATE)
                    SELECT COMPANY, CC_ACCOUNT, SPLIT
                    FROM EXP_DATA INNER JOIN CC_SPLIT ON EXP_DATA.COMPANY=CC_SPLIT.COMPANY AND
                    EXP_DATA.ACCOUNT = CC_SPLIT.CC_ACCOUNT
                    WHERE EXP_DATA.PERIOD IN ('1102', '1103', '1104')]
    So at the end basically table exp_data 2, shall have the same values as exp_data but the account column shall include the same values of account column of exp_data plus the values of cc_account of cc_split table. And also the amount column shall be the multiplication of the column split from cc_split * amount of exp_data

    thanks in advance

    I have realised that I need an alias, maybe a for exp_data and b for cc_split.
    Last edited by buzmay; 01-19-12 at 08:18.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have only thrown a shallow look at your query.
    You will at least have to synchronise the destination columns and the source columns. There MUST be a 1 to 1 correlation between those. You specified more than 15 columns at the destination, and only 3 at the source side.

    Code:
    INSERT INTO EXP_DATA_2(PERIOD, ACTUALITY, COMPANY, CURRENCY, ACCOUNT, AMOUNT,
                       EXTDIM1, EXTDIM2, EXTDIM3,EXTDIM4,JOURNAL_TYPE,C_COMPANY,
                       COMPANY,C_DIM, TRANAMOUNT, TRANCURR,REGION, TRANSF_DATE)
                    SELECT COMPANY, CC_ACCOUNT, SPLIT
    When you specify 15 columns on the destination side, you also have to specify 15 at the source side, but they don't have to have the same name, only the position is important (and the data types, they must be compatible).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2012
    Posts
    52
    Hi Wim,

    Thanks for the answer I realized that, so far what I have is this

    Code:
    INSERT INTO EXP_DATA_2
    SELECT A.PERIOD, A.ACTUALITY, A.COMPANY, (B.CC_ACCOUNT)ACCOUNT, (B.SPLIT * A.AMOUNT)AMOUNT,A.EXTDIM1,A.EXTDIM2,
               A.EXTDIM3,A.EXTDIM4,A.JOURNAL_TYPE,A.C_COMPANY,A.C_DIM,A.TRANAMOUNT,A.TRANCURR,A.REGION,
               A.TRANSF_DATE
    FROM EXP_DATA AS A,
    CC_SPLIT AS B
    WHERE A.COMPANY = B.COMPANY AND A.ACCOUNT = B.FUNC_ACCOUNT
    AND A.PERIOD IN ('1102', '1103', '1104')
    But I get column of name or number of supplied values does not match. Any ideas?

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Write down each and every column on both the source and destination side.

    Don't use
    INSERT INTO TableName SELECT ...
    on the destination side, but
    INSERT INTO TableName (Column1, column2, ...) SELECT ...

    Don't use
    SELECT * FROM ...
    on the source side but
    SELECT ColumnA, ColumnB, .... FROM ...

    The easier you think you're making it for yourself, the harder you get hit in reality.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2012
    Posts
    52
    Thanks for the advice Wim, I figure it out by not complicating myself
    Thanks for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •