I do some work that involves translating resources for a webpage. These are all stored in a SQL Server table that looks like this (plus a few irrelevant constraints).

CREATE TABLE [dbo].[tblResources](
    [lResourceID] [int] IDENTITY(1000,1) NOT NULL,
    [lLocaleID] [int] NOT NULL,
    [txtResourceKey] [varchar](255) NOT NULL,
    [memText] [nvarchar](max) NOT NULL,
    [txtLastModifiedUsername] [varchar](255) NULL,
    [dtLastModifiedDate] [datetime] NULL
);

The translations come down from business people up above, almost always in an Excel spreadsheet. We've been transferring them by hand, but that takes a long time, and we also have a lot of different databases/tables for the given sites, and which table these should go into is generally poorly defined by the business people. Lastly, the keys are often already present, but not always. This is also never indicated by the business people. I've created a template excel spreadsheet with some VBA macros that will generate the SQL from a spreadsheet they give us. This is the current format of the spreadsheet that they give us, but I've added the "Generate SQL" button I've added.

When you click the button a user form pops up to ask you which tables you want to add it to, if the change should be deployed to our staging sites as well, what the username you want logged (we track changes), and the output file name. This is powered by this VBA on the sheet

Option Explicit

Private Sub CommandButton1_Click()
    GenerateSqlUserForm.Show
End Sub

And this VBA for the user form

Option Explicit

Private Sub GenerateSQLCommandButton_Click()
    Sheet1.Activate
    
    Dim localeIds(7) As String
    localeIds(0) = "@US_LOCALE"
    localeIds(1) = "@UK_LOCALE"
    localeIds(2) = "@DE_LOCALE"
    localeIds(3) = "@JP_LOCALE"
    localeIds(4) = "@IT_LOCALE"
    localeIds(5) = "@FR_LOCALE"
    localeIds(6) = "@ES_LOCALE"
    
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim stream As TextStream
    Dim filename As String
    filename = FileNameTextBox.Value
    
    If Not filename Like "*.sql" Then
        filename = filename & ".sql"
    End If
    
    Set stream = fso.CreateTextFile(Format("{0}\{1}", ActiveWorkbook.Path, filename), True, True)
    
    stream.WriteLine "BEGIN TRANSACTION"
    stream.WriteLine vbTab & "CREATE TABLE #Resources ("
    stream.WriteLine vbTab & vbTab & "lLocaleID int NOT NULL,"
    stream.WriteLine vbTab & vbTab & "txtResourceKey varchar(255) NOT NULL,"
    stream.WriteLine vbTab & vbTab & "memText nvarchar(max) NOT NULL,"
    stream.WriteLine vbTab & vbTab & "txtLastModifiedUsername varchar(255) NULL"
    stream.WriteLine vbTab & ");"
    stream.WriteLine ""
    stream.WriteLine vbTab & "DECLARE " & Format("{0} int = {1}", localeIds(0), 0)
    Dim i As Integer
    For i = 1 To 6
        stream.WriteLine vbTab & vbTab & Format(", {0} int = {1}", localeIds(i), i)
    Next i
    stream.WriteLine vbTab & ";"
    stream.WriteLine ""
    stream.WriteLine vbTab & "DECLARE @username varchar(255) = '" & UsernameTextBox.Value & "';"
    
    Dim insertTemplate As String
    insertTemplate = "INSERT INTO #Resources VALUES({0}, '{1}', N'{2}', @username);"
    
    With Worksheets(1)
        Dim row As Integer, locale As Integer, resourceText As String
        row = 7
        Do Until .Cells(row, 1).Value2 = ""
            Dim resourceKey As String
            resourceKey = .Cells(row, 1).Value2
            Dim rowCells As Range
            Set rowCells = Range(GetRange("B", row, "H", row))
            Dim colCell As Range
            
            locale = 0
            For Each colCell In rowCells.Cells
                resourceText = colCell.Value2
                
                If Not IsNull(resourceText) And resourceText <> "" Then
                    stream.WriteLine vbTab & Format(insertTemplate, localeIds(locale), resourceKey, resourceText)
                End If
                locale = locale + 1
            Next colCell
            
            row = row + 1
        Loop
    End With
        
    stream.WriteLine vbTab & "GO"
    
    If FirstTableNameCheckBox.Value Then
        AddToTextFile stream, FirstTableNameCheckBox.Caption, UseStagingCheckBox.Value
    End If
    If SecondTableNameCheckBox.Value Then
        AddToTextFile stream, SecondTableNameCheckBox.Caption, UseStagingCheckBox.Value
    End If
    If ThirdTableNameCheckBox.Value Then
        AddToTextFile stream, ThirdTableNameCheckBox.Caption, UseStagingCheckBox.Value
    End If
    If FourthTableNameCheckBox.Value Then
        AddToTextFile stream, FourthTableNameCheckBox.Caption, UseStagingCheckBox.Value
    End If
    
    stream.WriteLine ""
    stream.WriteLine vbTab & "DROP TABLE #Resources;"
    stream.WriteLine "IF @@TRANCOUNT > 0 COMMIT TRANSACTION"
    stream.WriteLine "GO"
    
    stream.Close
    
    GenerateSqlUserForm.Hide
End Sub

' http://stackoverflow.com/a/31730589/3076272'
Private Function Format(ParamArray arr() As Variant) As String

    Dim i As Long
    Dim temp As String

    temp = CStr(arr(0))
    For i = 1 To UBound(arr)
        temp = Replace(temp, "{" & i - 1 & "}", CStr(arr(i)))
    Next

    Format = temp
End Function

Private Function GetRange(firstColumn, firstRow, lastColumn, lastRow) As String
    GetRange = Format("{0}{1}:{2}{3}", firstColumn, firstRow, lastColumn, lastRow)
End Function

Private Sub AddToTextFile(textfile, tableName, useStaging)
    AddToTextFileInternal textfile, tableName
    If useStaging Then
        AddToTextFileInternal textfile, tableName & "Staging"
    End If
End Sub

Private Sub AddToTextFileInternal(textfile, tableName)
    textfile.WriteLine ""
    textfile.WriteLine vbTab & Format("USE {0};", tableName)
    textfile.WriteLine vbTab & "GO"
    textfile.WriteLine ""
    
    textfile.WriteLine vbTab & "MERGE tblResources AS Target"
    textfile.WriteLine vbTab & vbTab & "USING #Resources AS Source"
    textfile.WriteLine vbTab & "ON Target.lLocaleID = Source.lLocaleID"
    textfile.WriteLine vbTab & vbTab & "AND Target.txtResourceKey COLLATE DATABASE_DEFAULT = Source.txtResourceKey COLLATE DATABASE_DEFAULT"
    textfile.WriteLine vbTab & "WHEN MATCHED"
    textfile.WriteLine vbTab & vbTab & "THEN UPDATE SET"
    textfile.WriteLine vbTab & vbTab & vbTab & "Target.memText = Source.memText,"
    textfile.WriteLine vbTab & vbTab & vbTab & "Target.txtLastModifiedUsername = Source.txtLastModifiedUsername,"
    textfile.WriteLine vbTab & vbTab & vbTab & "Target.dtLastModifiedDate = GETDATE()"
    textfile.WriteLine vbTab & "WHEN NOT MATCHED BY TARGET"
    textfile.WriteLine vbTab & vbTab & "THEN"
    textfile.WriteLine vbTab & vbTab & vbTab & "INSERT (lLocaleID, txtResourceKey, memText, txtLastModifiedUsername, dtLastModifiedDate)"
    textfile.WriteLine vbTab & vbTab & vbTab & "VALUES (Source.lLocaleID, Source.txtResourceKey, Source.memText, Source.txtLastModifiedUsername, GETDATE());"
    textfile.WriteLine vbTab & "GO"
    
End Sub

It then generates SQL like this

BEGIN TRANSACTION
    CREATE TABLE #Resources (
        lLocaleID int NOT NULL,
        txtResourceKey varchar(255) NOT NULL,
        memText nvarchar(max) NOT NULL,
        txtLastModifiedUsername varchar(255) NULL
    );

    DECLARE @US_LOCALE int = 0
        , @UK_LOCALE int = 1
        , @DE_LOCALE int = 2
        , @JP_LOCALE int = 3
        , @IT_LOCALE int = 4
        , @FR_LOCALE int = 5
        , @ES_LOCALE int = 6
    ;

    DECLARE @username varchar(255) = 'daniel.obermiller';
    INSERT INTO #Resources VALUES(@US_LOCALE, 'supercool.resourcekey', N'cool', @username);
    INSERT INTO #Resources VALUES(@UK_LOCALE, 'supercool.resourcekey', N'cool', @username);
    INSERT INTO #Resources VALUES(@DE_LOCALE, 'supercool.resourcekey', N'kühl', @username);
    INSERT INTO #Resources VALUES(@JP_LOCALE, 'supercool.resourcekey', N'クール', @username);
    INSERT INTO #Resources VALUES(@IT_LOCALE, 'supercool.resourcekey', N'fresco', @username);
    INSERT INTO #Resources VALUES(@FR_LOCALE, 'supercool.resourcekey', N'frais', @username);
    INSERT INTO #Resources VALUES(@ES_LOCALE, 'supercool.resourcekey', N'guay', @username);
    GO

    USE FirstTable;
    GO

    MERGE tblResources AS Target
        USING #Resources AS Source
    ON Target.lLocaleID = Source.lLocaleID
        AND Target.txtResourceKey COLLATE DATABASE_DEFAULT = Source.txtResourceKey COLLATE DATABASE_DEFAULT
    WHEN MATCHED
        THEN UPDATE SET
            Target.memText = Source.memText,
            Target.txtLastModifiedUsername = Source.txtLastModifiedUsername,
            Target.dtLastModifiedDate = GETDATE()
    WHEN NOT MATCHED BY TARGET
        THEN
            INSERT (lLocaleID, txtResourceKey, memText, txtLastModifiedUsername, dtLastModifiedDate)
            VALUES (Source.lLocaleID, Source.txtResourceKey, Source.memText, Source.txtLastModifiedUsername, GETDATE());
    GO

    USE FirstTableStaging;
    GO

    MERGE tblResources AS Target
        USING #Resources AS Source
    ON Target.lLocaleID = Source.lLocaleID
        AND Target.txtResourceKey COLLATE DATABASE_DEFAULT = Source.txtResourceKey COLLATE DATABASE_DEFAULT
    WHEN MATCHED
        THEN UPDATE SET
            Target.memText = Source.memText,
            Target.txtLastModifiedUsername = Source.txtLastModifiedUsername,
            Target.dtLastModifiedDate = GETDATE()
    WHEN NOT MATCHED BY TARGET
        THEN
            INSERT (lLocaleID, txtResourceKey, memText, txtLastModifiedUsername, dtLastModifiedDate)
            VALUES (Source.lLocaleID, Source.txtResourceKey, Source.memText, Source.txtLastModifiedUsername, GETDATE());
    GO

    USE SecondTable;
    GO

    MERGE tblResources AS Target
        USING #Resources AS Source
    ON Target.lLocaleID = Source.lLocaleID
        AND Target.txtResourceKey COLLATE DATABASE_DEFAULT = Source.txtResourceKey COLLATE DATABASE_DEFAULT
    WHEN MATCHED
        THEN UPDATE SET
            Target.memText = Source.memText,
            Target.txtLastModifiedUsername = Source.txtLastModifiedUsername,
            Target.dtLastModifiedDate = GETDATE()
    WHEN NOT MATCHED BY TARGET
        THEN
            INSERT (lLocaleID, txtResourceKey, memText, txtLastModifiedUsername, dtLastModifiedDate)
            VALUES (Source.lLocaleID, Source.txtResourceKey, Source.memText, Source.txtLastModifiedUsername, GETDATE());
    GO

    USE SecondTableStaging;
    GO

    MERGE tblResources AS Target
        USING #Resources AS Source
    ON Target.lLocaleID = Source.lLocaleID
        AND Target.txtResourceKey COLLATE DATABASE_DEFAULT = Source.txtResourceKey COLLATE DATABASE_DEFAULT
    WHEN MATCHED
        THEN UPDATE SET
            Target.memText = Source.memText,
            Target.txtLastModifiedUsername = Source.txtLastModifiedUsername,
            Target.dtLastModifiedDate = GETDATE()
    WHEN NOT MATCHED BY TARGET
        THEN
            INSERT (lLocaleID, txtResourceKey, memText, txtLastModifiedUsername, dtLastModifiedDate)
            VALUES (Source.lLocaleID, Source.txtResourceKey, Source.memText, Source.txtLastModifiedUsername, GETDATE());
    GO

    DROP TABLE #Resources;
IF @@TRANCOUNT > 0 COMMIT TRANSACTION
GO

I'd love feedback as to:

• How is the VBA? It looks messy to me, but I'm not very familiar with VBA. This seemed like about the best I could do.
• How is the generated SQL? I'd be happy to take suggestions to improve the readability, quality, or performance of the SQL.

Also, I've changed the names of some of the tables for business reasons - they have much better names (referring to the FirstTableNameCheckBox, etc checkboxes).