Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Create dummy data from Sub Table

Author  Topic 

chairuby
Starting Member

1 Post

Posted - 2014-09-12 : 07:01:23
Hi,

I need advise on how to create dummy data from sub table.
1. I have 2 table, 1 is Main Table and another one is Sub Table.
2. I have Full setup in Main Table and sub table i want to have extension but i dont want to duplicate the whole setup in Sub Table because some of the value is similar with main table. Only if the value is different i will create a record in Sub Table.

Below is the sample script.


See bellow example:

DECLARE @SystemParameter table(
SystemParamID int,
SystemParamGroup nvarchar(100),
SystemParamKey int,
SystemParamCountry nvarchar(100)
);

DECLARE @SubSystemParameter table(
SystemParamID int,
MainSystemParamID int,
SystemParamKey int,
SystemParamSubCountry nvarchar(100)
);

insert into @SystemParameter
select 1, 'GroupA', 10, 'China'

insert into @SystemParameter
select 2, 'GroupA', 20, 'Indonesia'

insert into @SystemParameter
select 3, 'GroupA', 30, 'US'

insert into @SystemParameter
select 4, 'GroupA', 40, 'Malaysia'

insert into @SystemParameter
select 5, 'GroupB', 50, 'China'

insert into @SystemParameter
select 6, 'GroupB', 60, 'Indonesia'

insert into @SystemParameter
select 7, 'GroupB', 70, 'US'

insert into @SystemParameter
select 8, 'GroupB', 80, 'Malaysia'

insert into @SubSystemParameter
select 9, 2, 90, 'Jakarta'

--To create view with combination of both Main and Sub into 1 table.
select SystemParamGroup, SystemParamKey, SystemParamCountry, SystemParamCountry as MainCountry from @SystemParameter
union
select B.SystemParamGroup, A.SystemParamKey, A.SystemParamSubCountry as SystemParamCountry, B.SystemParamCountry as MainCountry
from @SubSystemParameter A inner join @SystemParameter B on A.MainSystemParamID = B.SystemParamID

What i want to achieve is to have dynamic data for those sub table data that is not created for SystemParamCountry to be display with the same SystemParamKey as main.

This is what i want to view in my view table : http://i59.tinypic.com/9roj93.png

Thanks in advance
   

- Advertisement -