将插入的表数据导出到 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:Filemytest.txt -c -t -T -S YAMUNASQLEXPRESS'

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:Filemytest.txt -c -t -T -S YAMUNASQLEXPRESS'

exec xp_cmdshell @sql

我不知道如何在bcp中查询插入的表，有人知道吗?

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

推荐答案

在调用 bcp 之前，您可以创建另一个表来临时存储 INSERTED 的结果.

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:Filemytest.txt -c -t -T -S YAMUNASQLEXPRESS'

exec xp_cmdshell @sql

显然这行不通，因为表 tempInserted 在调用 bcp 时被锁定.

Apparently this will not work, because table tempInserted is locked at the time bcp is called.

这是一个变通办法，也许不是最优雅的解决方案，但应该可行(如果您不是使用 Express 版本).您可以使用触发器将插入的数据存储到该表中，您可以创建一个定期运行的作业(假设每 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:Filemytest.txt -c -t -T -S YAMUNASQLEXPRESS'

 exec xp_cmdshell @sql

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

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