Create dummy data from Sub Table

• fjchairul

  Grasshopper

  Points: 19

  More actions

  September 12, 2014 at 4:58 am

  #145225

  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

• Hunterwood

  SSCrazy

  Points: 2074

  More actions

  September 16, 2014 at 5:47 am

  #1745745

  fjchairul (9/12/2014)

  ---

  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

  Hi,

  you just need to join in @SystemParameer again on Country:

  --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

  union

  select B.SystemParamGroup, A.SystemParamKey, A.SystemParamSubCountry as SystemParamCountry, B.SystemParamCountry as MainCountry

  from @SubSystemParameter A

  inner join @SystemParameter C

  on A.MainSystemParamID = C.SystemParamID

  inner join @SystemParameter B

  on C.SystemParamCountry = B.SystemParamCountry

  and C.SystemParamID <> B.SystemParamID

  🙂

  /M

• fjchairul

  Grasshopper

  Points: 19

  More actions

  September 16, 2014 at 6:36 am

  #1745752

  Great thanks a lot 🙂 😀