使用CSV文件更新MSSQL Server表. [英] update MSSQL Server table using CSV file.

问题描述

我想用CSV文件上的数据更新MSSQL Server先前存在的表，但是因为CSV文件是用随机名称生成的，所以我想按顺序打开每个文件，并在数据库中更新表，然后删除

I want to update MSSQL Server pre-existing Table with the data on CSV file but because the CSV files are generated with random names so i want to sequentially open each file as they are created and update table in my database and then delete this file once the record is added.

推荐答案

配置从常量文件导入并使用批处理文件，如下所示:

Configure importing from a constant file and use the batch file like this:

@echo off

for %%i in (*.csv) do call :RUN_ONE %%i
goto END

:RUN_ONE

ren %%i import.csv

rem Load import.csv into SQL Server.

del import.csv

:END

例如，您可以创建一个使用OPENROWSET从文件中选择数据的存储过程:

For example, you may create a stored procedure that uses OPENROWSET to select data from a file:

INSERT INTO ... ( ... )
SELECT
    ...
FROM OPENROWSET(BULK '...import.csv',
    FORMATFILE = '...import.fmt', FIRSTROW = 2)

并使用sqlcmd执行该过程.例如:

sqlcmd -S. -E -h -1 -d数据库-Q"EXEC dbo.uspImport"

and use sqlcmd to execute the procedure. For example:

sqlcmd -S . -E -h -1 -d Database -Q "EXEC dbo.uspImport"

这篇关于使用CSV文件更新MSSQL Server表.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！