Import MS-Excel to SQL-Server

Greetings All,

I have a excel file which is originally a sqlserver table that was
exported as a excel file. I have added more data to this excel file
and now want to import it again to its original table,i.e, it will
overwrite current data in the table but with no change in the schema.
How should I handle the issue of PKs in the current table that will be
over-written. I know sqlserver dose not adjust PKs when data is
over-written, like my case.

MTIA,
Grawsha<grawsha2000@.yahoo.com> wrote in message
news:1108999543.155717.259460@.o13g2000cwo.googlegr oups.com...
> Greetings All,
> I have a excel file which is originally a sqlserver table that was
> exported as a excel file. I have added more data to this excel file
> and now want to import it again to its original table,i.e, it will
> overwrite current data in the table but with no change in the schema.
> How should I handle the issue of PKs in the current table that will be
> over-written. I know sqlserver dose not adjust PKs when data is
> over-written, like my case.
> MTIA,
> Grawsha

I'm not sure what you mean by "overwritten" - do you mean you want to delete
all rows from the table, then load the .xls? If so, just use DELETE or
TRUNCATE TABLE to empty the table before loading the file. If you mean
something else, then you should give some more details, preferably including
the CREATE TABLE statement for your table, so we can see what your key
actually is.

Simon|||Simon

Yes I want to delete the all rows from the table (employees). But the
problem is there are a PK (Person ID) that I use to relate it to a FK
in another table (sales) . I would assume the Enerprise Manager would
give an error if I try to delete the rows.

My question is, should I remove the relation first, delete the rows,
load the file and then create the relation again?

Grawsha|||If your target table is referenced by a foreign key from another table,
you'll get an error if you delete from it, unless you created the key
with ON DELETE CASCADE (assuming MSSQL 2000 - you didn't mention which
version you have), in which case the referencing rows will be deleted
also.

As you suggest, you can load the data by dropping the foreign key then
recreating it, but this will leave orphaned rows in the referencing
table, unless your .xls contains data for all the current rows in the
target table. It isn't clear from your description if your .xls
contains updated rows for existing PK values, or if it contains
entirely new PK values and rows, or both. You might want to consider
another approach, which is to create a staging table with the same
structure as your target table, load the .xls into it, then INSERT and
UPDATE the data in the target table - this may be closer to what you
really need.

/* Add new rows */
insert into dbo.Target (col1, col2, ...)
from dbo.Staging s
where not exists (select * from dbo.Target t
where s.PK = t.PK)

/* Update values for existing rows */
update dbo.Target
set col1 = s.col1, col2 = s.col2, ...
from dbo.Staging s
join dbo.Target t
on s.PK = t.PK

If this doesn't help, I suggest that you post CREATE TABLE statements
for your tables, INSERT for some sample data, and then a few rows of
data from your .xls, to show what your data looks like and what you
expect to happen - descriptions by themselves are usually unclear.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon