使用 CSV 数据更新 SQL 表?
我正在尝试使用源 CSV 文件中的新列更新 SQL 表之一。该文件中的 CSV 记录已存在于该 SQL 表中,但该 SQL 表缺少该 CSV 文件中的一些新列。
我已经通过 ALTER TABLE 将新列添加到我的 SQL 表结构中。但现在我只需要将 CSV 文件中的数据导入到新列中。我该怎么做?我正在尝试使用 SSIS 和 SQL Server 来完成此任务,但对 Excel 还很陌生。
I am trying to update one of my SQL tables with new columns in my source CSV file. The CSV records in this file are already in this SQL table, but this SQL table is lacking some of the new columns from this CSV file.
I already added the new columns to my SQL table structure via ALTER TABLE. But now I just need to import the data from this CSV file into the new columns. How can I do this? I am trying to use SSIS and SQL Server to accomplish this, but am pretty new to Excel.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这对于解决救赎问题来说可能已经太晚了;虽然我是为未来的读者发布此信息!
您可以通过解析 csv 文件来生成 SQL INSERT/UPDATE/etc 命令(一个简单的 python 脚本即可)。
您也可以使用这个在线解析器:
http://www.convertcsv.com/csv-to-sql.htm
(希望当您点击时它仍然可用!)
生成您的 SQL 命令。界面非常简单,并且以一种很棒的方式完成整个工作。
This is probably too late to solve salvationishere's problem; though I'm posting this for future readers!
You could just generate the SQL INSERT/UPDATE/etc command by parsing the csv file (a simple python script will do).
You could alternatively use this online parser:
http://www.convertcsv.com/csv-to-sql.htm
(Hoping that it'd still be available when you click!)
to generate your SQL command. The interface is extremely straight forward and it does the entire job in an awesome way.
您有多种选择:
如果您要将数据加载到可以编辑目标表的非生产系统中,则可以将数据加载到新表中,将旧表重命名为废弃表,然后重命名新表到旧表名称。
您可以将数据加载到临时表中,然后编写 SQL 语句从临时表更新目标表。
您可以在 Excel 中打开 CSV 文件并编写公式来生成更新脚本,将公式向下拖动到所有行,以便为每行获取单独的更新语句,然后在 Management Studio 中运行单独的更新语句.
如果您的 CSV 文件中有完整的历史记录,您可以截断目标表并更新导入文件的现有 ssis 包以使用新列。
还有更多选择,但以上任何一个都可能是足够的解决方案。
You have several options:
If you are loading the data into a non-production system where you can edit the target tables, you could load the data into a new table, rename the old table to obsolete, and rename the new table to the old table name.
You can load the data into a staging table and then write a SQL statement to update the target table from the staging table.
You can open the CSV file in Excel and write a formula to generate an update script, drag the formula down across all rows so that you get a separate update statement for each row, and then run the separate update statements in management studio.
You can truncate the target table and update your existing ssis package that imports the file to use the new columns if you have the full history in your CSV file.
There are more options, but any of the above would probably be more than adequate solutions.