Help with SQL Update Query -- Not sure how to approach this situation

[Alex]

Hello,

I have a rather large table in MS SQL 2000 that I'm writing reports in
Crystal from, but I'm unsure how to get the various data files (from
different departments) into the table. Below is a small chunk of the
table:

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.

Does anyone have other suggestions? Above is only a small example...
the table has about 25 columns with various data I'll be collecting
from 3-4 departments, so it's a hodge podge of data to combine into
one report.

Thanks for any suggestions or ideas in solving this. Thanks in
advance...

Alex.

[Erland Sommarskog]

Alex (al**@totallynerd.com) writes:

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.

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve Kass]

Alex,

I think the following will do what you want:

Import the first file into a table that has two more
columns than the file has data for (those two being TotHours
and Benefits). Then import the second file into a temporary
staging table with columns DeptCode, Type, TotHours, Benefits,
then

update T set
TotHours = Staging.TotHours,
Benefits = Staging.Benefits
from Staging
where Staging.DeptCode = T.DeptCode
and Staging.Type = T.Type

Alternatively, you can use table structures in SQL Server that
correspond to the import files, and create a view that looks
like your "rather large table," constructed by joining together
the separate tables on the primary key columns.

-- Steve Kass
-- Drew University
-- Ref: ECC5E7E8-F3DB-47FB-953C-BD504A3B249B

Alex wrote:

Hello,

I have a rather large table in MS SQL 2000 that I'm writing reports in
Crystal from, but I'm unsure how to get the various data files (from
different departments) into the table. Below is a small chunk of the
table:

DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;B enefits

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;B enefits
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.

Does anyone have other suggestions? Above is only a small example...
the table has about 25 columns with various data I'll be collecting
from 3-4 departments, so it's a hodge podge of data to combine into
one report.

Thanks for any suggestions or ideas in solving this. Thanks in
advance...

Alex.