Output results of query to multiple text files to be later run into another DB

• JKSQL

  SSCrazy Eights

  Points: 8481

  More actions

  March 19, 2019 at 4:03 pm

  #371755

  We have two DBs A and B. 
  A is prior to upgrade and B is after upgrade. 
  B's xml data got messed up upon upgrade. 
  I was going to use BCP to make smaller files that can be iterated through in an order.  I do not want to overload Server B with the command.  When I did this in SSMS it was 200mb in statements.  I want to limit each .sql file to 1000 transactions.
  
SELECT 
  'UPDATE [sample]. SET [XMLPattern]='+Char(39)+CAST([XMLPattern] AS NVARCHAR(MAX))+Char(39)+'WHERE [Table_Id]='+CAST([Table_Id] AS NVARCHAR(50)) as UpdateStatement
FROM [BusinessMgmt].[Structures]
WHERE Table_Id Between 1 AND 1000


  I was looking at this link and thought there might be a better way. I need to export chunks of data into multiple text files.  I would have a control loop grabbing a start and end parameter, and then export.  Any ideas would be appreciated.

• JKSQL

  SSCrazy Eights

  Points: 8481

  More actions

  March 20, 2019 at 1:56 pm

  #2025058

  I solved it with what I have below.  Since I am going to get the DB locally I can take a few liberties with outputing files and such - 

  You need to enable these on your instance also make sure you have permission -
  
sp_configure 'show advanced options', 1

  GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE;
GO


  This is a generic sproc I got from the internet a long time ago.  Not sure where credit is due - 

  
--------------------------------------------------------
-- Writes to a file
--------------------------------------------------------
CREATE PROCEDURE [dbo].[FileWriteText]
     @File_Name varchar(1000)
    ,@Text nvarchar(max)
    ,@Append bit = 0

  AS

  --------------------------------------------------------
-- Procedure Name: [dbo].[FileWriteText]
--------------------------------------------------------
BEGIN
    DECLARE
         @fso int
        ,@ts int
        ,@rv int

      EXEC @rv = sp_oacreate
        "scripting.filesystemobject"
        ,@fso OUTPUT
        ,1

      IF @rv <> 0 goto ErrorCode

      IF @appEND = 1
    BEGIN
        -- Open the text stream for appEND, will fail IF the file doesn't exist
        EXEC @rv = sp_oamethod @fso,"opentextfile", @ts OUTPUT, @file_name, 8
        IF @rv <> 0 goto ErrorCode
    END
    ELSE
    BEGIN
        -- Create a new text file, overwriing IF necessary
        EXEC @rv = sp_oamethod @fso,"createtextfile", @ts OUTPUT, @file_name, -1
        IF @rv <> 0 goto ErrorCode
    END

      EXEC @rv = sp_oamethod @ts,"write",null ,@text
    IF @rv <> 0 goto ErrorCode

      EXEC @rv = sp_oamethod @ts,"close"
    IF @rv <> 0 goto ErrorCode

      EXEC sp_oadestroy @ts
    EXEC sp_oadestroy @fso

      RETURN 0

      ErrorCode:
    DECLARE
         @es varchar(512)
        ,@ed varchar(512)

      EXEC sp_oageterrorinfo null, @es OUTPUT, @ed OUTPUT
    RAISERROR(@ed,16,1)
    EXEC sp_oadestroy @ts
    EXEC sp_oadestroy @fso
    RETURN 1
END
GO

  Then my code to generate multiple files with the data paging through

  
DECLARE
  @Path Varchar(256) = 'C:\',
  @SQL as Nvarchar(Max),
  @OutputFileName varchar(1500)='test',
  @OutputFile varchar(1500)='',
  @Q char(1)= '''',
  @Low bigint, @High BigInt,
  @MaterialCount int=0,
  @FileCount tinyint=0,
  @MaterialOutputCountPerFile tinyint =5
  
 SET @OutputFile = @path+@OutputFileName+'.sql'

   EXEC FileWriteText
   @File_Name = @OutputFile
  ,@Text = 'SET NOCOUNT ON'
  ,@Append = 0

   WHILE EXISTS (SELECT Top 1 1 FROM dbo.table WHERE Field1= 0)
 BEGIN
  -- This counter was put in place to handle big strings
  SET @MaterialCount = @MaterialCount + 1
  
  IF @MaterialCount = @MaterialOutputCountPerFile
  BEGIN
   SET @MaterialCount = 0
   SET @FileCount = @FileCount + 1
   SET @OutputFile = @path + @OutputFileName+'_'+CONVERT(Varchar(10),@FileCount)+'.sql'
   SET @SQL = 'SET NOCOUNT ON' + CHAR(13)
            
   EXEC FileWriteText
     @File_Name = @OutputFile
    ,@Text = @SQL
    ,@Append = 0
  END
  SET @SQL=''
  SELECT
  @Low=MIN(Field2)
  ,@High=MAX(Field2)
  FROM (
  SELECT TOP 100
   Field2
  FROM dbo.table
  WHERE Field1 =0
  ORDER BY Field2) PageLookup

    SELECT
    @SQL=@SQL + CHAR(13) +' UPDATE [dbo]. SET [Field3]='+Char(39)+REPLACE(CAST([Field3] AS NVARCHAR(MAX)),'''','''''')+Char(39)+'WHERE [Field2]='+CAST([Field2] AS NVARCHAR(50))
  FROM dbo.table
  WHERE Field2 BETWEEN @Low AND @High

   EXEC FileWriteText
   @File_Name = @OutputFile
  ,@Text = @SQL
  ,@Append = 1

    UPDATE dbo.table
  SET Field1 = 1
  WHERE Field2 BETWEEN @Low AND @High    
 END

  Works really well outputting 4mb files that I can then wrap in an EXE to deploy to a customer.