Import/Update data from Excel to SQL Server

Question

I'm converting a database from Excel onto MS SQL server and am basically clueless.

The Excel file has column headings of GroupID, Name, Members, Remarks

The SQL table has the same fields.

When I update the SQL some records are totally new, so need to be appended, others need a column or two updated, while most records need nothing at all. So far I've taken the lazy way out & truncated the file & appended everything back in, but what's the proper way?

Answer 1

Import the file as a separate table and you can do all your updates from there. Depending on your version of SQL server you may be able to use the MERGE statement. It shouldn't take too long to knock up an insert, and an update statement.

Something like this for the update:

UPDATE o
SET    name = i.name
FROM   originaltablename o
       INNER JOIN importedexceltablename i
           ON o.GroupID = i.GroupID
WHERE  o.name <> i.name

And something like this for the insert:

INSERT INTO originaltablename
SELECT i.*
FROM   importedexceltablename i
       LEFT JOIN originaltablename o
           ON o.GroupID = i.GroupID
WHERE  o.GroupID IS NULL

Be careful though, this is just an example to get you going as you haven't given enough information for a proper solution.