Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

Generate SQL script from Excel

I'm comfortable with VBScript and ADO in ASP but have not worked with VBA in Excel.  What I'd like to do is generate an SQL script from an Excel document.  Each row in the document contains all of the parameters needed for each SQL statement.

Dim string_Column1, string_Column2
Dim array_OfRemainingColumnsThatActuallyContainData

'' BEGIN OUTPUT FOR EACH ROW

UPDATE table
SET Column1=string_Column1, Column2=string_Column2
WHERE UniqueID IN (array_OfRemainingColumnsThatActuallyContainData)
GO

Move.Next
0
ptpovo
Asked:
ptpovo
1 Solution
 
TimCotteeCommented:
ptpovo, here is an Excel VBA example which takes one sheet and generates the SQL statements in the second sheet.

It will probably need some modification for your particular environment but you should be able to figure it out.

Public Sub CreateSQL()
    Dim lngRow As Long
    Dim lngCol As Long
    Dim strRemaining As String
    lngRow = 1
    With Sheets(1)
        Do While .Cells(lngRow, 1).Value <> ""
            lngCol = 3
            strRemaining = ""
            Do While .Cells(lngRow, lngCol).Value <> ""
                strRemaining = strRemaining & .Cells(lngRow, lngCol).Value & ", "
                lngCol = lngCol + 1
            Loop
            strRemaining = Left(strRemaining, Len(strRemaining) - 2)
            Sheets(2).Cells(lngRow, 1).Value = "UPDATE MyTable Set Column1 = '" & .Cells(lngRow, 1) & "', Column2 = '" & .Cells(lngRow, 2) & "'" & _
                " WHERE UniqueID IN (" & strRemaining & ")"
            lngRow = lngRow + 1
        Loop
    End With
End Sub
0
 
ptpovoAuthor Commented:
Sorry I've let this one sit for awhile, is this a Macro or a Module?  How do I execute it?
0
 
DanRollinsCommented:
Hi ptpovo,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept TimCottee's comment(s) as an answer.

ptpovo, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.
==========
DanRollins -- EE database cleanup volunteer
0
 
Computer101Commented:
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now