如何从.txt文件导入数据以填充SQL Server中的表 [英] How to import data from .txt file to populate a table in SQL Server
问题描述
每天,带有客户数据的PPE.txt
文件(用分号分隔并且始终具有相同的布局)存储在特定的文件目录中.
Every day a PPE.txt
file with clients data, separated by semicolon and always with the same layout is stored to a specific file directory.
每天都有人必须根据此PPE.txt
从我们的数据库中更新特定的表.
Every day someone has to update a specific table from our database based in this PPE.txt
.
我想通过SQL脚本自动化此过程
I want to automate this process via a SQL script
我想将是一种解决方案,就是通过脚本将文件从此.txt
文件导入到创建的表中,然后执行更新.
What I thought would be a solution is to import the data via a script from this .txt
file into a created table, then execute the update.
到目前为止,我有
IF EXISTS (SELECT 1 FROM Sysobjects WHERE name LIKE 'CX_PPEList_TMP%')
DROP TABLE CX_PPEList_TMP
GO
CREATE TABLE CX_PPEList_TMP
(
Type_Registy CHAR(1),
Number_Person INTEGER,
CPF_CNPJ VARCHAR(14),
Type_Person CHAR(1),
Name_Person VARCHAR(80),
Name_Agency VARCHAR(40),
Name_Office VARCHAR(40),
Number_Title_Related INTEGER,
Name_Title_Related VARCHAR(80)
)
UPDATE Table1
SET SN_Policaly_Exposed = 'Y'
WHERE Table1.CD_Personal_Number = CX_PPEList_TMP.CPF_CNPJ
AND Table1.SN_Policaly_Exposed = 'N'
UPDATE Table1
SET SN_Policaly_Exposed = 'N'
WHERE Table1.CD_Personal_Number NOT IN (SELECT CX_PPEList_TMP.CPF_CNPJ
FROM CX_PPEList_TMP)
AND Table1.SN_Policaly_Exposed = 'Y'
我知道我没有给多少钱,但这是因为我还没有多少钱.
I know I haven't given much, but it is because I don't have much yet.
我想通过脚本用PEP.txt
文件中的数据填充CX_PEPList_TMP
临时表,因此我只需执行此脚本即可更新数据库.但是我不知道在我的研究中找不到我无法使用的任何命令.
I want to populate the CX_PEPList_TMP
temp table with the data from the PEP.txt
file via a script so I could just execute this script to update my database. But I don't know any kind of command I can use neither have found in my research.
提前谢谢!
推荐答案
使用OPENROWSET
您可以使用 OPENROWSET 选项(首先必须启用临时查询)
SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\Docs\csv\;',
'SELECT * FROM PPE.txt')
使用OLEDB提供程序
SELECT
*
FROM
OPENROWSET
('Microsoft.ACE.OLEDB.12.0','Text;Database=C:\Docs\csv\;IMEX=1;','SELECT *
FROM PPE.txt') t
使用大容量插入
您可以将文本文件数据导入到临时表并从中更新数据:
Using BULK INSERT
You can import text file data to a staging table and update data from it:
BULK INSERT dbo.StagingTable
FROM 'C:\PPE.txt'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
这篇关于如何从.txt文件导入数据以填充SQL Server中的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!