I don't have a 5gb file to test against, so I have no idea if this proposed solution will work for you. Your data may not lend itself to this proposed solution, but I thought I'd offer it anyway.
I created a table called SqlCommands
USE [Test]
GO
CREATE TABLE [dbo].[SqlCommands](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TextData] [varchar](max) NOT NULL,
CONSTRAINT [PK_SqlCommands] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
For testing purposes, I used the SSMS scripting wizard to script one of my small tables (data) as 'inserts' into a test file.
I then used a Powershell script to read that test file (which are basically SQL insert commands into the above SqlCommands table. The table has an identity column and the Powershell script only references the 'TextData' column. The ID identity column will allow me to select from this table in the correct order. Here is the Powershell script:
$database = "test"
$server = "."
$table = "dbo.SqlCommands"
$ff="C:\Test\TestData.txt"
write-host "Input file used is $ff"
$inrec=""
$counter=0
if ([System.IO.File]::Exists($ff))
{
$obj=new-object System.IO.StreamReader($ff)
$inrec=$obj.ReadLine()
while ($inrec -ne $null)
{
#replace single quotes with double quotes so the insert will work for literals
$inrec = $inrec -Replace("'","''")
Invoke-SqlCmd -Database $database -ServerInstance $server -Query "insert into $table (TextData) values('$inrec')"
$counter++
$inrec=$obj.ReadLine()
}
$obj.close()
write-host "$counter lines read"
}
else
{
write-host "Source file ($ff) does not exist."
}
write-host "All done now!"
Now, I have my SQL file in a table. I wrote a cursor to select from the table ordering by ID to make sure I'm executing the commands in the correct order. The cursor script reads each row from the SqlCommands table and checks to see if it is a 'GO' statement ('GO' statements were automatically added by the scripting wizard when I scripted the table data). If the row is NOT a 'GO', the current row is concatenated to @cmd and the next row is read. If the current row is a 'GO', I execute the accumulated @cmd and blank @cmd out for the next series of statements. The EXEC is wrapped inside a TRY/CATCH. In the event of an error, I'm able to display the row number (Identity ID column) so I can run selects against the SqlCommands table 'around' that row number to find any problems.
SET NOCOUNT ON
DECLARE @cmd VARCHAR(max) = ' '
DECLARE @Id INT
DECLARE @textdata VARCHAR(max)
DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT ID
,TextData
FROM [Test].[dbo].[SqlCommands]
ORDER BY ID
DECLARE @count INT
SET @count = 0
OPEN _CURSOR
FETCH NEXT
FROM _CURSOR
INTO @Id
,@textdata
WHILE @@FETCH_STATUS = 0
BEGIN
IF @textdata = 'GO'
BEGIN
BEGIN TRY
--print @cmd
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT 'Error occurred at line ' + convert(VARCHAR(100), @Id)
PRINT error_message()
END CATCH
SET @cmd = ' '
END
ELSE
BEGIN
SET @cmd += @textdata
END
FETCH NEXT
FROM _CURSOR
INTO @Id
,@textdata
END --End While
CLOSE _CURSOR
DEALLOCATE _CURSOR
PRINT @count
If any errors exist, I can use the row number to try and find the error
select * from [Test].[dbo].[SqlCommands] where id between 25 and 30
