通过BCP分隔的HL7导出SQL数据

Question

来自SQL输出的以下HL7消息存储在$Message变量中：

MSH|^~\&|System|System CRM|SYS|System CRM|20210222143236||ADT^A04|CRM001|P|2.4|||AL|NE
EVN||20210222143236
PID||9999999997^^^^NHSN|Ben^Smith^^^Mr||||||12A Cherry Avenue^Middleton^London^Greater London^E170RA^NSP^P|||
PD1|||^Amazing Surgery, Amazing Health Centre, London, E16 0RA|^DR. Fix
PV1|1|O

我试图为它创建txt文件的输出：

DECLARE @Message NVARCHAR(MAX)

SET @Message = 'SELECT Message FROM #HL7'

DECLARE @sql VARCHAR(1000);
SELECT @sql = 'bcp ' + @Message + ' queryout "D:\HL7\Test.txt" -c -t|^ , -T -S'+ @@servername
PRINT @sql
EXEC master.dbo.xp_cmdshell @sql 

但是，我似乎得到了以下错误

' ' is not recognized as an internal or external command,
operable program or batch file.

当移除\^\i得到：

Copy direction must be either 'in', 'out' or 'format'.

请有人帮我一下，我认为分隔符引起了问题

Answer 1

请尝试以下查询：

SELECT @sql = 'bcp "' + @Message + '" queryout D:\HL7\Test.txt -c -t"|"^ , -T -S'+ @@servername

我刚刚通过以下查询导出了数据：

DECLARE @Message NVARCHAR(MAX)

SET @Message = 'SELECT *FROM test.dbo.table_name'

DECLARE @sql VARCHAR(1000);
SELECT @sql = 'bcp "' + @Message + '" queryout D:\HL7\Test.txt -c -t"|"^ , -T -S'+ @@servername
PRINT @sql
EXEC master.dbo.xp_cmdshell @sql 

​

​

Answer 2

下面是您正在尝试的一个工作实现：

-----------------------------------------------------------------
IF OBJECT_ID (N'dbo.spSaveTextToFile') IS NOT NULL
   DROP PROCEDURE dbo.spSaveTextToFile
GO
CREATE PROCEDURE spSaveTextToFile
  @TheString VARCHAR(MAX),
  @Filename VARCHAR(255),
  @Unicode INT=0
AS
  SET NOCOUNT ON
  DECLARE @MySpecialTempTable VARCHAR(255)
  DECLARE @Command NVARCHAR(4000)
  DECLARE @RESULT INT
 
--firstly we create a global temp table with a unique name
  SELECT  @MySpecialTempTable = '##temp'
       + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
--then we create it using dynamic SQL, & insert a single row
--in it with the MAX Varchar stocked with the string we want
  SELECT  @Command = 'create table ['
       + @MySpecialTempTable
       + '] (MyID int identity(1,1), Bulkcol varchar(MAX))
insert into ['
       + @MySpecialTempTable
       + '](BulkCol) select @TheString'
  EXECUTE sp_ExecuteSQL @command, N'@TheString varchar(MAX)',
           @TheString
 
--then we execute the BCP to save the file
  SELECT  @Command = 'bcp "select BulkCol from ['
          + @MySpecialTempTable + ']'
          + '" queryout '
          + @Filename + ' '
         + CASE WHEN @Unicode=0 THEN '-c' ELSE '-w' END
          + ' -T -S' + @@servername
  EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT
  EXECUTE ( 'Drop table ' + @MySpecialTempTable )
  RETURN @result
  GO

从文本文件的TSQL看菲尔因素