March 19, 2019 at 4:03 pm
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.
March 20, 2019 at 1:56 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply