从Excel导入/更新数据到SQL Server

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

我正在将一个数据库从Excel转换到MS SQL server，而且基本上没有头绪。

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

Excel文件的列标题是GroupID, Name, Members，备注。

The SQL table has the same fields.

SQL表具有相同的字段。

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?

当我更新SQL时，一些记录是全新的，所以需要追加，其他的需要一个或两个更新，而大多数的记录不需要任何东西。到目前为止，我已经使用了惰性方法，删除了文件，并将所有内容都添加回，但是正确的方法是什么呢?

1 个解决方案

#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.

将文件作为一个单独的表导入，您可以从那里进行所有更新。根据您的SQL server版本，您可以使用MERGE语句。不需要太长时间就可以完成插入和更新语句。

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.

但是要小心，这只是一个例子，因为你没有给出足够的信息来得到正确的解决方案。

#1