# How to Merge a Table

### Question

• I am having Table1 as Main Account and Sub Account with Account No. First 8 digits are same. Now, I have to create a Table 2 with all the Main Account as it is but the Sub Account (end with 03) having a single Row with [Mkt_val] equal to some of Sub Account  Mkt Value and also [Asset_Val] having some of all (Main Account and Sub Account).

 Table1 Acct Numb Account Type Asset Mkt Val Acct Asset MV 881009201 Main Account 30 120 881009201 Main Account 40 120 881009201 Main Account 20 120 881009201 Main Account 30 120 881009203 Sub Account 50 80 881009203 Sub Account 10 80 881009203 Sub Account 20 80 Table2 Acct Numb Account Type Asset Mkt Val Acct Asset MV 881009201 Main Account 30 200 881009201 Main Account 40 200 881009201 Main Account 20 200 881009201 Main Account 30 200 881009201 Sub Account 80 200

I am Having a Table1 with the information given above. I am having 2 types of account:

1. Main Account (ending with 01)

2. Sub Account (ending with 03)

Now, I need a table with Having All the Main Account. But All the Sub Account Club into a Single Account No. Whose Account no. will be same as of its Main Account No. but the Account Type wii be  [Sub Account].

Now we have 2 more colums:

1. Asset Mkt Val

2. Acct Asset MV

In Table 1: The [Acct Asset MV] shows the sum of Main Account ( 30+ 40+ 20+ 30= 120) and [Asset Mkt Val] shows the Sum of Sub Accont (50+10+20 = 80).

Now in Table 2: I want to have  [Acct Asset MV] should be some of all account =(120+80= 200) and in Sub Account the [Asset Mkt Val] should be 80.

• Edited by Tuesday, June 25, 2013 4:59 PM More Explanation
Tuesday, June 25, 2013 1:56 PM

• Hi njain8,

There are some steps, you can refer to them.

```create table table1(
AcctNumb int not null,
AccountType varchar(50) not null,
AssetMktVal int not null,
AcctAssetMV int not null)
----create a table1

insert into Table1( AcctNumb,AccountType,AssetMktVal,AcctAssetMV)
values(881009201,'Main Account',30,120);
insert into Table1( AcctNumb,AccountType,AssetMktVal,AcctAssetMV)
values(881009201,'Main Account',40,120),
(881009201,'Main Account',20,120),
(881009201,'Main Account',30,120),
(881009203,'Sub Account',50,80),
(881009203,'Sub Account',10,80),
(881009203,'Sub Account',20,80)

---insert records into table1
Select * from Table1
---check table1 records

---Create the table2 by copying

Select * into table2 from table1
----check table2
Select * from table2
Update table2 set AcctAssetMV=(Select sum(AssetMktVal) from table2)
----update AcctAssetMV in table2
Select * from table2

insert into table2(AcctNumb,AccountType,AssetMktVal,AcctAssetMV)
values ((select distinct AcctNumb from table2  where AccountType='Main Account'),'sub Account',
(select SUM (AssetMktVal) from table2 where AccountType='Sub Account'),(select distinct AcctAssetMV from table2 where AccountType='Sub Account'))
----Delete the unnecessary records
delete from table2 where AcctNumb not in(select AcctNumb from table2 where AccountType='Main Account')
---result of table2
AcctNumb AccountType	 AssetMktVal AcctAssetMV
881009201	Sub Account	80	200
881009201	Main Account	30	200
881009201	Main Account	40	200
881009201	Main Account	20	200
881009201	Main Account	30	200```
Thanks,
Sofiya Li

Sofiya Li
TechNet Community Support

Thursday, June 27, 2013 7:54 AM

### All replies

• Of course I don't know what you are doing, but I suspect you probably want to separate the main account records and the sub account records into two different tables. Then it will be easier to do this type of thing.

As it is, I think you want to create two virtual tables, something like:
SELECT SUM([Asset Mkt Val]
FROM Table1
WHERE [Account Type] = 'Sub Account'
GROUP BY [Acct Numb];

Then you need to add the Main Account number to that sub account table by removing the 03 from the Acct Numb and replacing it with 01. Then you can UNION with the other records from Table1 (WHERE [Account Type] = Main Account') and do a sum of everything, grouping by Acct Numb.

So much easier if you put different types of items (Main and Sub accounts) into different tables. It's much easier to add and join tables together than it is too split tables apart once the different types of data have already been mixed together.

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Tuesday, June 25, 2013 3:57 PM
• hi ,

i cant understood can you pls elaborate your query with pictorial representation

• Edited by Tuesday, June 25, 2013 4:26 PM need some updation
Tuesday, June 25, 2013 4:24 PM
• Hi,

I am Having a Table1 with the information given above. I am having 2 types of account:

1. Main Account (ending with 01)

2. Sub Account (ending with 03)

Now, I need a table with Having All the Main Account. But All the Sub Account Club into a Single Account No. Whose Account no. will be same as of its Main Account No. but the Account Type wii be  [Sub Account].

Now we have 2 more colums:

1. Asset Mkt Val

2. Acct Asset MV

In Table 1: The [Acct Asset MV] shows the sum of Main Account ( 30+ 40+ 20+ 30= 120) and [Asset Mkt Val] shows the Sum of Sub Accont (50+10+20 = 80).

Now in Table 2: I want to have  [Acct Asset MV] should be some of all account =(120+80= 200) and in Sub Account the [Asset Mkt Val] should be 80.

Tuesday, June 25, 2013 4:58 PM
• Hi njain8,

There are some steps, you can refer to them.

```create table table1(
AcctNumb int not null,
AccountType varchar(50) not null,
AssetMktVal int not null,
AcctAssetMV int not null)
----create a table1

insert into Table1( AcctNumb,AccountType,AssetMktVal,AcctAssetMV)
values(881009201,'Main Account',30,120);
insert into Table1( AcctNumb,AccountType,AssetMktVal,AcctAssetMV)
values(881009201,'Main Account',40,120),
(881009201,'Main Account',20,120),
(881009201,'Main Account',30,120),
(881009203,'Sub Account',50,80),
(881009203,'Sub Account',10,80),
(881009203,'Sub Account',20,80)

---insert records into table1
Select * from Table1
---check table1 records

---Create the table2 by copying

Select * into table2 from table1
----check table2
Select * from table2
Update table2 set AcctAssetMV=(Select sum(AssetMktVal) from table2)
----update AcctAssetMV in table2
Select * from table2

insert into table2(AcctNumb,AccountType,AssetMktVal,AcctAssetMV)
values ((select distinct AcctNumb from table2  where AccountType='Main Account'),'sub Account',
(select SUM (AssetMktVal) from table2 where AccountType='Sub Account'),(select distinct AcctAssetMV from table2 where AccountType='Sub Account'))
----Delete the unnecessary records
delete from table2 where AcctNumb not in(select AcctNumb from table2 where AccountType='Main Account')
---result of table2
AcctNumb AccountType	 AssetMktVal AcctAssetMV
881009201	Sub Account	80	200
881009201	Main Account	30	200
881009201	Main Account	40	200
881009201	Main Account	20	200
881009201	Main Account	30	200```
Thanks,
Sofiya Li

Sofiya Li
TechNet Community Support

Thursday, June 27, 2013 7:54 AM