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