将插入的表数据导出到SQL Server中的.txt文件 [英] export inserted table data to .txt file in SQL server

问题描述

我想将插入表的数据（临时表具有插入表的注释）导出到.txt文件，我在触发器内部使用了这种方法

I want to export data of inserted table( temporary table have note of inserted data of the table) to .txt file, I used like this inside trigger

create trigger monitorTrigger on test 
for insert 
as
declare @sql varchar(8000)

SELECT @sql = 'bcp "select * from inserted" queryout I:\File\mytest.txt -c -t -T -S YAMUNA\SQLEXPRESS'

exec xp_cmdshell @sql

go

这不起作用，因为我没有给出完整的上下文（表示database.shemaName.tableName）插入表格。但是相同的代码也适用于普通表，因为我给出了完整的上下文，

this is not working since I didn't give full context(means database.shemaName.tableName) of inserted table. But the same code is working with normal table since I give full context as

declare @sql varchar(8000)

SELECT @sql = 'bcp "select * from test2.dbo.test" queryout I:\File\mytest.txt -c -t -T -S YAMUNA\SQLEXPRESS'

exec xp_cmdshell @sql

我不知道如何查询<$ c $中插入的表c> bcp ，有人知道吗？

I don't know how to query inserted table in bcp, anyone have any idea?

推荐答案

您可以创建另一个表来临时存储在调用 bcp 之前从 INSERTTED 返回结果。

You can create another table for temporary storing the results from INSERTED before calling bcp.

create trigger monitorTrigger on test 
AFTER insert 
as
declare @sql varchar(8000)

--delete it every time
TRUNCATE TABLE test2.dbo.tempInserted

--populate it from inserted
INSERT INTO test2.dbo.tempInserted
SELECT * FROM INSERTED

--use it in bcp
SELECT @sql = 'bcp "select * from test2.dbo.tempInserted" queryout I:\File\mytest.txt -c -t -T -S YAMUNA\SQLEXPRESS'

exec xp_cmdshell @sql

编辑：
显然这不起作用，因为表 tempInserted 在<$时被锁定了c $ c> bcp 被调用。

这是一个变通办法，也许不是最优雅的解决方案，但应该可以解决（如果您不是在速成版上）。您可以使用触发器仅将插入的数据存储到此表中，并且可以创建一个定期运行的工作（假设每隔5分钟运行一次），然后从该表中读取，复制到文件并删除。

Here is a workaround idea, maybe not the most elegant solution but should work (if you are not on express edition). You can use trigger just to store the inserted data into this table and you can create a job that runs periodically (every 5 mins let's say) and read from that table, copy to file and delete.

所以触发器就是：

create trigger monitorTrigger on test 
AFTER insert 
as
BEGIN
  INSERT INTO test2.dbo.tempInserted
  SELECT * FROM INSERTED
END

和存储过程以复制到文件-您可以从作业中运行：

and Stored Procedure to copy to file - that you can run from the job:

CREATE PROC transferToFile 
AS
BEGIN
 declare @sql varchar(8000)

 SELECT @sql = 'bcp "select * from test2.dbo.tempInserted" queryout I:\File\mytest.txt -c -t -T -S YAMUNA\SQLEXPRESS'

 exec xp_cmdshell @sql

 --delete at the end
 TRUNCATE TABLE test2.dbo.tempInserted
END

这篇关于将插入的表数据导出到SQL Server中的.txt文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！