Repopulating a table without dropping & recreating it ??

[hblabonte]

I have a database created to track inventory. The back-end is SQL server & front-end is in Access. I populate the "person" table (name, position #, title, phone...) based
on data from our HR department. I need to update the table on a regular basis. Once I get an updated file, I'd like to repopulate the table, keeping the position # (KEY) in the table, but changing all other fields related to that position.

For example:

Currently in the Person table:
Position Name Title Room ...
1111 JOE SMITH Manager 101 ...
2222 Jane Doe Peon 102 ...
3333 Jesse James Robber 103 ...

In my new extract file, Position 2222 has been reassigned to
Betty Boop in room 105. I don't want to drop the whole table and recreate it because there are several tables related to it. So I'd want my result to be:

Position Name Title Room ...
1111 JOE SMITH Manager 101 ...
2222 Betty Boop Analyst 105 ...
3333 Jesse James Robber 103 ...

If a postion number already exists, I want to repopulate the fields in that record. If it doesn't exist, I want to add a new record to the table.

The way that I've populated the table in the past is to use Data Transformation Services, Import Data Wizard. I go through the screens and match up the fields in my extract (excel) file to the fields in my Person table. Obviously this won't work to change data, because using the wizard it tries to add new records.
I don't know how to change data without dropping & recreating the table.

Sorry for the long-winded question, but I didn't know an easier way to ask.

thanks,
Heather

 

[majorbroncosfan]

If I understand the issue, you can use the following SQL statement:

UPDATE person SET name = (select name from currenttable where name = yourname)

You can repeat this statement multiple times for each column's information. Also, look up information on cursors (scrollable) and fetch for rowset cursors.

I'm also working on such a project and found this to work, but only if the SELECT statement in the above syntax returns only one record.

 

[hblabonte]

Ok, this is really going to show how ignorant I am in SQL Server. The only thing I've done on the SQL side was to define the tables and import the data into several table. All other work I've done has been done on the Access side.

I honestly don't know where to write the SQL that you've suggested. As I mentioned in my "story", the only way that I was importing before (the only way I knew how) was to use the DTS Import Wizard. All that lets me do is to map columns (extract file to table), adding new entries or dropping & recreating the whole table. I know there must be an easier way to populate the tables. Wouldn't it be nice if the help facility knew what you really wanted to know?

Could you point me in the right direction? Assume that I don't know anything...really. Did I mention this is the first time I've worked with SQL Server?

thanks in advance!!!

 

[KRISF]

Using DTS's Data Driven Query Task, the logic of UPDATE or INSERT could be contained (if you are more familiar with DTS). Otherwise a custom solution could be made with a stored procedure that does the UPDATE or INSERT depending on presense of a key value.

The DTS Wizard does not have this task; but the SQL Server Enterprise Manager as a Data Transformation Services / Local Packages option where you can create your own DTS package.