Create INSERT statements from Excel

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Arun Mallick » Create INSERT statements from Excel

14 posts, Page 2 of 2

««1 2

Create INSERT statements from Excel

Rate Topic

Display Mode

Topic Options

Author

Message

suslikovich

Posted Tuesday, May 03, 2011 1:01 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 12, Visits: 222

I don't see the script in the article. Am I missing anything?

Stan

Post #1102638

damir.sovic

Posted Tuesday, May 03, 2011 2:01 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 4:44 AM
Points: 39, Visits: 16

suslikovich (5/3/2011)

I don't see the script in the article. Am I missing anything?

Stan

Actualy, the script we are talking about is in the article at:
http://www.sqlservercentral.com/scripts/insert/66001/

few lines after conclusion.

Post #1102683

travbowls

Posted Wednesday, November 16, 2011 4:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 17, 2011 2:36 PM
Points: 1, Visits: 2

In case anyone is interested I modified the script to be used for writing updates as well. It assumes that the fields which make up your primary key are the first X columns in the excel file. This is needed so they don't try to get updated, and so they appear in the where clause. It prompts for the number of unique columns. I'm sure some of my syntax can be cleaned up, this it my first time with VB.

Sub CreateUpdateScript()
Dim Row As Integer
Dim Col As Integer

'To store all the columns available in the current active sheet
Dim ColNames(100) As String

Col = 1
Row = 1
Dim ColCount As Integer
ColCount = 0
'Get Columns from the sheet
Do Until ActiveSheet.Cells(Row, Col) = "" 'Loop until you find a blank.
ColNames(ColCount) = ActiveSheet.Cells(Row, Col)
ColCount = ColCount + 1
Col = Col + 1
Loop
ColCount = ColCount - 1

'Inputs for the starting and ending point for the rows
Row = InputBox("Give the starting Row No.")
Dim MaxRow As Integer
MaxRow = InputBox("Give the Maximum Row No.")

Dim keyCols As Integer
keyCols = InputBox("Give the Number of unique id columns.")

'File to save the generated insert statements
File = "c:\\UpdateCode.txt"
fHandle = FreeFile()
Open File For Output As fHandle

Dim CellColCount As Integer
Dim StringStore As String
Dim whereClause As String

Do While Row <= MaxRow
StringStore = ""
whereClause = ""
CellColCount = 0
'ActiveSheet.Name will give the current active sheet name
'this can be treated as table name in the database
StringStore = StringStore + "update " + ActiveSheet.Name + " SET "

Do While CellColCount <= ColCount

If CellColCount>keyCols Then
StringStore = StringStore + ColNames(CellColCount) + "= '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"
End If

If CellColCount < keyCols-1 Then
whereClause = whereClause + ColNames(CellColCount) + "= '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "' AND "
End If

If CellColCount = keyCols-1 Then
whereClause = whereClause + ColNames(CellColCount) + "= '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"
End If

If (CellColCount <> ColCount) AND (cellColCount > keyCols) Then
StringStore = StringStore + " , "
End If
CellColCount = CellColCount + 1
Loop

Print #fHandle, StringStore + " WHERE " + whereClause
Print #fHandle, " "
Row = Row + 1
Loop

Close #fHandle
MsgBox ("Successfully Done")
End Sub

Post #1207191

C# Gnu

Posted Thursday, August 02, 2012 3:45 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 AM
Points: 224, Visits: 629

My project tool will build SQL statements for you too.
The tool will also validate and send the data directly from Excel if you wish.

The app will generate Insert statements using a template and merge code system.

Here is an example template :

INSERT INTO Customers
(
    "Short Name", 
    "Name", 
    "Contact First Name", 
    "Contact Surname", 
    "Address", 
    "Town", 
    "Zip/PostCode", 
    "Days Credit", 
    "FirstOrder"
)
VALUES
(    '{XL-merge:Short Name}',
    '{XL-merge:Name}',
    '{XL-merge:Contact First Name}',
    '{XL-merge:Contact Surname}',
    '{XL-merge:Address}',
    '{XL-merge:Town}',
    '{XL-merge:Zip/PostCode}',
    {XL-merge:Days Credit},
    '{XL-merge:FirstOrder}')

Templates are auto generated after you select a destination table or procedure.

The application will validate all of your data by wrapping the operation in Transaction, and reporting any errors and the reason for the error against each row.

You can modify the template SQL as you wish - changing it to Update or conditional insert / update or execute stored procedure if you wish.

Its free for 60 days and could save you allot of time ;)

C# Gnu
____________________________________________________

Excel Database Tasks V2 ..
Multi user data edit Task based solution - Excel with SQL Server / Access / MySQL ..

Version 2.5 released - See Blog

Post #1339035

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions