CODEWITHSUNDEEP
sql-server-2008
Ankit Suhail
Ankit Suhail

Reputation: 2105

Inserting from one table to another

I am working on a database that is not normalized and it cannot be normalized as I don't have the permissions. The problem is

I have two tables

the structure of the tables are like

Gl_Account (empty table or with some old data)

loc  |  gl_acct  |  HMISTOTAL
-------------------------------
     |           |
-------------------------------

Trial Balance

loc   |    g1101    |   g1102   | g1103
----------------------------------------
1400  |     20      |    30     |  0
----------------------------------------
1500  |     10      |    0      |  40
----------------------------------------

Now what I want to do is

Fetch the Record that are not in Gl_Account Table from the trialBalance table with the respective GL_acct Number where the amount under the respective gXXXX (in trial balance) is not zero

Let me explain it with an example. Keeping the above table in mind I want to fill the table GL_Account as

GL_Account

 loc      |    gl_acct      |  HMISTOTAL
    ---------------------------------------
       1400  |      1101       |   20
    ---------------------------------------
       1400  |      1102       |   30
    ---------------------------------------
       1500  |      1101       |   10
    ---------------------------------------
       1500  |      1103       |   40
    ---------------------------------------

i have tried this query but it only the first record captured is entered into the gl_accountt table. I skipped the amount part for now.. but it is required.

insert into Gl_Account (loc,gl_acct,HMISTOTAL)
  select * from (
                select a.loc,
                 CASE
                 WHEN a.G1101 <> 0 THEN '1101'
                 WHEN a.G1102 <> 0 THEN '1102'
                 WHEN a.G1104 <> 0 THEN '1104'
                 WHEN a.G1151 <> 0 THEN '1151'
                 END AS gl_Acct
               '0' as HMISTOTAL
                  FROM trialBalance a where NOT EXISTS
                         (SELECT 1 from GL_Account b WHERE b.loc = a.loc)) ab

thanks in advance.

Upvotes: 1

Views: 875

Answers (3)

Maryam Arshi
Maryam Arshi

Reputation: 2024

If your records in TrialBalance are limited and not more than 1 million record, the simplest way is to use union like below, but you can also write it as loop , I don't know which will be more efficient for you

Select loc,HMISTOTAL,gl_acct from(
SELECT   [Trial Balance].loc, [Trial Balance].g1101 AS HMISTOTAL, '1101' AS gl_acct
FROM         [Trial Balance] LEFT OUTER JOIN
                      [Gl_Account] ON [Trial Balance].g1101 = [Gl_Account].gl_acct AND [Trial Balance].loc = [Gl_Account].loc

Union
                      SELECT   [Trial Balance].loc, [Trial Balance].g1102 AS HMISTOTAL, '1102' AS gl_acct
FROM         [Trial Balance] LEFT OUTER JOIN
                      [Gl_Account] ON [Trial Balance].g1101 = [Gl_Account].gl_acct AND [Trial Balance].loc = [Gl_Account].loc

Union
                      SELECT   [Trial Balance].loc, [Trial Balance].g1103 AS HMISTOTAL, '1103' AS gl_acct
FROM         [Trial Balance] LEFT OUTER JOIN
                      [Gl_Account] ON [Trial Balance].g1101 = [Gl_Account].gl_acct AND [Trial Balance].loc = [Gl_Account].loc) x

where HMISTOTAL >0
Order By loc,HMISTOTAL

Upvotes: 5

Ivan Golović
Ivan Golović

Reputation: 8832

This gets the results that you specified:

SELECT  loc,
        gl_acct,
        CASE gl_acct
            WHEN '1101' THEN g1101 
            WHEN '1102' THEN g1102
            WHEN '1103' THEN g1103 
        END AS HMISTOTAL
FROM    trialBalance a
JOIN (
        VALUES  ('1101'), 
                ('1102'),
                ('1103')
        ) c(gl_acct) ON
        (a.g1101 <> 0 AND gl_acct = '1101') OR
        (a.g1102 <> 0 AND gl_acct = '1102') OR
        (a.g1103 <> 0 AND gl_acct = '1103')
WHERE   NOT EXISTS
(
    SELECT  1
    FROM    GL_Account x
    WHERE   x.loc = a.loc
)

I've used VALUES row constructor to create an ad-hoc table containing values to join with.

Upvotes: 1

Narendra
Narendra

Reputation: 3117

Try this one.

INSERT INTO Gl_Account (loc,gl_acct,HMISTOTAL)
  SELECT * FROM (
                SELECT trialBalance .loc,
                 CASE
                 WHEN a.G1101 <> 0 THEN '1101'
                 WHEN a.G1102 <> 0 THEN '1102'
                 WHEN a.G1104 <> 0 THEN '1104'
                 WHEN a.G1151 <> 0 THEN '1151'
                 END AS gl_Acct
               '0' AS HMISTOTAL
                  FROM 
                    trialBalance LEFT JOIN GL_Account 
                    ON trialBalance.loc = GL_Account.loc
                  WHERE 
                    GL_Account.loc IS NULL
                  )

Upvotes: 1

Related Questions