Alex (alex@totallynerd.com) writes:[color=blue]
> DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;Be nefits
>
> I'm getting data from multiple places, but as I import the data, I
> want it to populate the table with DeptCode and Type being the primary
> keys.
>
> If someone sends me the following file:
> DeptCode;Type;AveMonthVolume;WorkedFTE
> 1000;Budget;100;200
> 1010;Target;233;433
>
> And I get the following file from another source:
> DeptCode;Type;TotHours;Benefits
> 1000;Budget;433;400
> 1010;Target;33;43
>
> I want a simple way to import all this into the table so it looks like
> this:
> DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;Be nefits
> 1000;Budget;100;200;433;400
> 1010;Target;233;433;33;43
>
> The data will be coming in delimited text and in Excel format. I
> assume I can import his into MS SQL Enterprise Manager directly, but I
> can't find any simple way of running in update query to import my
> data. Also, I'd hate to use MS Access as the middle-man if at all
> possible... but if this is the only option, I'll do it.[/color]
First, I would recommend you to use BCP to load the data, so that
you can automate the load. You can't spend your days loading files
in EM.
I would define a staging table for each file that I may get. Then
for each table, there would be something like:
INSERT target_tbl (DeptCode, Type)
SELECT DeptCode, Type
FROM staging_tbl s
WHERE NOT EXISTS (SELECT *
FROM target_tbl t
WHERE t.DeptCode = s.DeptCode
AND t.Type = s.Type)
UPDATE target_tbl
SET some_col = s.some_col,
...
FROM targget_tbl t
JOIN staging_tbl s ON t.DeptCode = s.DeptCode
AND t.Type = s.Type
Here, I assume that all columns, save DeptCode and Type, in the target
table would be nullable, so that you can receive the files in any
order.
Obviously you need to package the whole thing, so when you receive
a file, the right commands are executed.
Note: above I mentioned BCP, but you would also write a program in VB
for the task. Or you could use DTS, which may have support for this
built-in. I don't use DTS myself, but the friendly people in
microsoft.public.sqlserver.dts may have useful ideas.
--
Erland Sommarskog, SQL Server MVP,
sommar@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
