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.
| Author |
Topic |
|
imtiluin ao
Starting Member
1 Post |
 Posted - 2012-07-31 : 00:52:27
|
| any sql expert here to help me out?? i have a problem here:my file contains:1. 6 lakhs family datas.2. 5 members in each family.3. the existing data has only 1 to 2 members.my conditions are:1.i want to create dummy rows in each data(family) example..: if a family has serial no. 1 and sl.no 2 then we have to create a dummy for sl no. 3,4 and 5.2. if a family has serial no. 1 and sl.no 3 then we have to create a dummy for sl no. 2,4 and 5. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-07-31 : 01:52:42
|
| The first bit is easy enough:This (or something like it) gets you all combinations of family & serial number that are not in your table.select * from(select * from(select distinct FamilyID from family) as FID,(select 1 as serialNo union all select 2 union all select 3 union all select 4 union all select 5) as serialNos) z -- Z is all combinations of family & serial #left outer join Family F on (F.familyID=z.familyID and F.serialNo=z.serialNo)where z.familyID is nullYou'll have to work out yourself how to dummy up the data but you can use that as a start point.(I have not run or tested it because you did not give me any tables). |
 |
|
|
|
|
|
creating dummy data