Creating a flat file

[SUE]

Hi,
I would really appreciate it if someone could help me create a flat file
from a couple of relational tables.
Emp_Info = Employee_id along with name & address info
Emp_Depend = Empolyee_id, Dep_id , lastname, firstname, birthdate (employee's
children)

I need to create one record per Employee that would contain some employee
info along with the names & birthdates of their children.

Would I create a Table_emp_dep in the format that I need, load it with the
Emp_Info data first and then update it with the Emp_Depend data that I need?


Emp_id
Emp_last
Emp_first
Child1_lastname
Child1_firstname
Child1_birthdate
Child2_lastname
Child2_firstname
Child2_birthdate ....and so on for as the most children an employee has.

In sql, how do I set it up to loop through the Emp_Depend file and choose
the records that I need and write them to the specific fields that they correspond
to?

If this is the correct approach, please include example of actual code that
works.

Thank in advance,
Sue

[Porkstone]

Sue,
Forgive me if I have misunderstood, but I think what you're looking for
is to perform a join on the two tables to create a superset of records. The
code for this would look something like this. If you are already familiar
with SQL you may find the following explanation a little bit condescending.

SELECT * FROM Emp_Info AS EI
INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id

--
You can replace the * with the names of the fields you want in your 'flat
file', to retrieve the last name from the Emp_Depend table you would put.

SELECT ED.lastname FROM Emp_Info AS EI
INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id
--
The 'ED.' prefix is an alias for the table, this is created in the join
expression. If you wish to include more fields simply place comma in between
each one and remember to use the correct alias.

Hope this is some use to you.

P


> Hi,
> I would really appreciate it if someone could help me create a flat file
> from a couple of relational tables.
> Emp_Info = Employee_id along with name & address info
> Emp_Depend = Empolyee_id, Dep_id , lastname, firstname, birthdate
(employee's
> children)
>
> I need to create one record per Employee that would contain some employee
> info along with the names & birthdates of their children.
>
> Would I create a Table_emp_dep in the format that I need, load it with the
> Emp_Info data first and then update it with the Emp_Depend data that I
need?
>
>
> Emp_id
> Emp_last
> Emp_first
> Child1_lastname
> Child1_firstname
> Child1_birthdate
> Child2_lastname
> Child2_firstname
> Child2_birthdate ....and so on for as the most children an employee has.
>
> In sql, how do I set it up to loop through the Emp_Depend file and choose
> the records that I need and write them to the specific fields that they
correspond
> to?
>
> If this is the correct approach, please include example of actual code
that
> works.
>
> Thank in advance,
> Sue

[SUE]

Thanks for the response Porkstone but I need to return one long record for
the multiple join in the dependent table. The final outcome needs to look
as follows:

Emp_ID Emp_last Dep_1 Dep1_first Dep_2 Dep2_first Dep_3 Dep3_first

1000 Smith 1 Jane 2 Lucy 3 John
1002 Jones 1 Paul 2 Ben (3 could be blank)
1003 McDonald (They may have no matches)


It is a superset that I need but it can't be multiple records for each employee
as the result...unless I'm doing something wrong I end up with the following:

Emp_ID Emp_last Dep_No Dep_first
1000 Smith 1 Jane
1000 Smith 2 Lucy
1000 Smith 3 John
1002 Jones 1 Paul
1002 Jones 2 Ben
1003 McDonald

Thanks,
Sue

"Porkstone" <Porkstoner@Hotmail.com> wrote:
>Sue,
> Forgive me if I have misunderstood, but I think what you're looking
for
>is to perform a join on the two tables to create a superset of records.
The
>code for this would look something like this. If you are already familiar
>with SQL you may find the following explanation a little bit condescending.
>
>SELECT * FROM Emp_Info AS EI
>INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id
>
>--
>You can replace the * with the names of the fields you want in your 'flat
>file', to retrieve the last name from the Emp_Depend table you would put.
>
>SELECT ED.lastname FROM Emp_Info AS EI
>INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id
>--
>The 'ED.' prefix is an alias for the table, this is created in the join
>expression. If you wish to include more fields simply place comma in between
>each one and remember to use the correct alias.
>
>Hope this is some use to you.
>
>P
>
>
>> Hi,
>> I would really appreciate it if someone could help me create a flat file
>> from a couple of relational tables.
>> Emp_Info = Employee_id along with name & address info
>> Emp_Depend = Empolyee_id, Dep_id , lastname, firstname, birthdate
>(employee's
>> children)
>>
>> I need to create one record per Employee that would contain some employee
>> info along with the names & birthdates of their children.
>>
>> Would I create a Table_emp_dep in the format that I need, load it with
the
>> Emp_Info data first and then update it with the Emp_Depend data that I
>need?
>>
>>
>> Emp_id
>> Emp_last
>> Emp_first
>> Child1_lastname
>> Child1_firstname
>> Child1_birthdate
>> Child2_lastname
>> Child2_firstname
>> Child2_birthdate ....and so on for as the most children an employee has.
>>
>> In sql, how do I set it up to loop through the Emp_Depend file and choose
>> the records that I need and write them to the specific fields that they
>correspond
>> to?
>>
>> If this is the correct approach, please include example of actual code
>that
>> works.
>>
>> Thank in advance,
>> Sue
>
>

[Dennis]

Denormalizing data can be more work than normalizing it. I can think of 3
solutions, all of which require some knowledge and work.

You identified the first, an Insert and a series of Updates into a denormalized
table. This is an abbreviate form.

INSERT into Table_emp_dep (Emp_ID, Emp_last)
SELECT Emp_ID, Emp_last from Emp_info

UPDATE Table_emp_dep
SET Dep1_first = Emp_Depend.firstname
FROM Emp_Depend
WHERE Emp_Depend.Emp_ID = Table_emp_dep.Emp_ID
AND Emp_Depend.Dep_No = 1

UPDATE Table_emp_dep
SET Dep2_first = Emp_Depend.firstname
FROM Emp_Depend
WHERE Emp_Depend.Emp_ID = Table_emp_dep.Emp_ID
AND Emp_Depend.Dep_No = 2

I don't think you'll be able to have all those in a single transaction without
locking problems. This is straightforward and should work.

The second takes a different approach. Briefly, you declare variables for
all the columns you want to insert into the denormalized table. Then you
declare a cursor to walk through all the employees. Within that cursor loop,
you declare another cursor to walk through all the dependents for a single
employee. Each time you load all the column data into the variables. At the
bottom of the employee loop you Insert the row into the denormalized table
using all the variables for the column values.

The third method uses something I know as "linguistic optimization" and would
use a single SELECT and a single INSERT. The SQL would take a while to work
out, but it uses functions and the GROUP BY to compress all the rows you
identified below into a single row per employee.

Dennis

"SUE" <susan.klick@po.state.ct.us> wrote:
>
>Thanks for the response Porkstone but I need to return one long record for
>the multiple join in the dependent table. The final outcome needs to look
>as follows:
>
>Emp_ID Emp_last Dep_1 Dep1_first Dep_2 Dep2_first Dep_3 Dep3_first
>
>1000 Smith 1 Jane 2 Lucy 3 John
>1002 Jones 1 Paul 2 Ben (3 could be blank)
>1003 McDonald (They may have no matches)
>
>
>It is a superset that I need but it can't be multiple records for each
employee
>as the result...unless I'm doing something wrong I end up with the following:
>
>Emp_ID Emp_last Dep_No Dep_first
>1000 Smith 1 Jane
>1000 Smith 2 Lucy
>1000 Smith 3 John
>1002 Jones 1 Paul
>1002 Jones 2 Ben
>1003 McDonald
>
>Thanks,
>Sue
>
>"Porkstone" <Porkstoner@Hotmail.com> wrote:
>>Sue,
>> Forgive me if I have misunderstood, but I think what you're looking
>for
>>is to perform a join on the two tables to create a superset of records.
>The
>>code for this would look something like this. If you are already familiar
>>with SQL you may find the following explanation a little bit condescending.
>>
>>SELECT * FROM Emp_Info AS EI
>>INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id
>>
>>--
>>You can replace the * with the names of the fields you want in your 'flat
>>file', to retrieve the last name from the Emp_Depend table you would put.
>>
>>SELECT ED.lastname FROM Emp_Info AS EI
>>INNER JOIN Emp_Depend AS ED ON EI.Employee_id = ED.Employee_id
>>--
>>The 'ED.' prefix is an alias for the table, this is created in the join
>>expression. If you wish to include more fields simply place comma in between
>>each one and remember to use the correct alias.
>>
>>Hope this is some use to you.
>>
>>P
>>
>>
>>> Hi,
>>> I would really appreciate it if someone could help me create a flat file
>>> from a couple of relational tables.
>>> Emp_Info = Employee_id along with name & address info
>>> Emp_Depend = Empolyee_id, Dep_id , lastname, firstname, birthdate
>>(employee's
>>> children)
>>>
>>> I need to create one record per Employee that would contain some employee
>>> info along with the names & birthdates of their children.
>>>
>>> Would I create a Table_emp_dep in the format that I need, load it with
>the
>>> Emp_Info data first and then update it with the Emp_Depend data that
I
>>need?
>>>
>>>
>>> Emp_id
>>> Emp_last
>>> Emp_first
>>> Child1_lastname
>>> Child1_firstname
>>> Child1_birthdate
>>> Child2_lastname
>>> Child2_firstname
>>> Child2_birthdate ....and so on for as the most children an employee has.
>>>
>>> In sql, how do I set it up to loop through the Emp_Depend file and choose
>>> the records that I need and write them to the specific fields that they
>>correspond
>>> to?
>>>
>>> If this is the correct approach, please include example of actual code
>>that
>>> works.
>>>
>>> Thank in advance,
>>> Sue
>>
>>
>