使用BULK从网络文件导入Proc定义 [英] Import Proc definition from network file with BULK

问题描述

我正在尝试使用提供的物理文件在MS SQL中创建1000多种Procs，作为Network上旧版迁移的一部分。现在，我计划将sp与动态SQL一起使用，以便像下面的代码段那样遍历所有代码，我对BULK ROWTERMINATOR有问题，所以我只是用ZZZZ对其进行了屏蔽，是否还有其他正确的方法将其设置为NONE，因此所有字符串将被加载到单行中以进行运行。在字段中也使用Nvarchar（Max）。

I'm trying to create 1000+ Procs in MS SQL from supplied physical files as part of legacy migration located on Network . For now I plan to use sp with dynamic SQL to loop over all of them like in segment below, I had problem with BULK ROWTERMINATOR, so I just dummied it with bunch of ZZZZ, is there any other correct way to set it to NONE, so all string will be loaded into single row for run. I also use Nvarchar(Max) for my field.

DROP TABLE IF EXISTS #imp;
CREATE TABLE #imp (Col varchar(max))
BULK INSERT  #imp
FROM '//TFSNetwork/log/Install/sp_Test02.sql'
  WITH (ROWTERMINATOR = '\nzzzzzzzzzZZZ')      ---<< ?????
select top 1 @Sql = Col from #imp
 EXEC (@sql);

----------------------------------------------------sp_Test02.sql
CREATE PROCEDURE [dbo].[sp_Test]   
AS  
BEGIN  
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET NOCOUNT ON;
    SELECT GETDATE() AS TS
END

-----------------------------------------------------------------

Load whole file into single row/column

推荐答案

ROWTERMINATOR ='\n'是默认使用的语言，这就是为什么您一度忽略它的原因。不要以为我们可以或将要更改此行为，而是使用您的Z组合）。
可以用另一个BULK完成同样的事情，在这种情况下，不需要任何ROWTERM选项。

ROWTERMINATOR = '\n' is what used by default ,that's why you get it once omitted at all. Don't think we can or will want to change this behavior rather use your Z combo). Same thing can be done with another BULK , in this case no need any ROWTERM options.

declare @myFile varchar(max) 
select @myFile = BulkColumn
from openrowset(BULK '//Network/Path/Test02.sql', single_blob) x;
SELECT @myFile

这篇关于使用BULK从网络文件导入Proc定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！