如何在ssms，存储过程中从文件读取数据以及将数据写入文件中 [英] How to read data and write data from and into file in ssms , stored procedure

问题描述

大家好

只是想纠正一个存储过程，该过程将从.txt文件中读取六列，然后插入到temp表中，所以我必须对数据进行一些转换，然后才想将此数据写回到四个文件中

所以我有一列谁的值是A，B，C，D，所以我必须写4个文件，我想从求职代理那里运行它，这不是问题，并且存储过程中有一种方法可以直接读取或粘贴ftp服务器中的文件

请任何帮助将不胜枚举


谢谢

解决方案

嘿，
我有两套代码可以帮助您解决问题.
第一个是从文本文件读取数据.

 - 创建链接服务器.
 EXEC  sp_addlinkedserver txtsrv，N '  Jet 4.0'，
   N '  Microsoft.Jet.OLEDB.4.0'，
   N '  c:\ data \'，
    NULL ，
   N ' 文本';
转到

- -设置登录映射.
 EXEC  sp_addlinkedsrvlogin txtsrv，FALSE，sa， NULL ;
转到

- 列出链接服务器中的表.
 EXEC  sp_tables_ex txtsrv;
转到

- 查询以下表格之一:file1#txt 
- ，使用四部分名称. 
选择 *
 FROM  txtsrv ... [distqry#txt]; 

现在，您将获得表中的数据，并且可以将其复制到临时表中.根据需要处理它并创建文本
这是另一组代码，用于将文本数据写入文件并从SQL Server创建文件.

  DECLARE   @ Text   AS   VARCHAR ( 100 )
 DECLARE   @ Cmd   AS   VARCHAR ( 100 )
 SET   @ Text  = ' 你好，世界
 SET   @ Cmd  = '  echo' +  @ Text  + ' > e:\ AppTextFile.txt'
打印  @ cmd 
执行 Master.dbo.xp_CmdShell  @ Cmd  

Hi all

Just want to right a stored procedure, that will read six column from .txt file and then insert into temp table , than i have to do some transformation on data , than i want to write this data back in to four files

so i have a column whos values are A,B,C,D so i have to write 4 files and i want to run this from job agent which is not a problem , and is there a way in stored procedure to directly read or paste file from ftp server

Please any help would be much appriciated


Thanks

解决方案

Hey,
I''ve two set of codes which might help you to resolve the issue.
First one is to read the Data from a Text File.

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, N'Jet 4.0', 
   N'Microsoft.Jet.OLEDB.4.0',
   N'c:\data\',
   NULL,
   N'Text';
GO

----Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, sa, NULL;
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv;
GO

--Query one of the tables: file1#txt
--using a four-part name. 
SELECT * 
FROM txtsrv...[distqry#txt];

Now you''ll get the data in a table and that you can copy to a temp table. Process it however you wanted and create the text
and Here is another set of code to write text Data into a file and create a file from SQL Server.

DECLARE @Text AS VARCHAR(100)
DECLARE @Cmd AS VARCHAR(100)
SET @Text = 'Hello world'
SET @Cmd ='echo ' +  @Text + ' > e:\AppTextFile.txt'
print @cmd
EXECUTE Master.dbo.xp_CmdShell  @Cmd

这篇关于如何在ssms，存储过程中从文件读取数据以及将数据写入文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！