Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Create INSERT statements from Excel Expand / Collapse
Author
Message
Posted Tuesday, May 3, 2011 1:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:44 PM
Points: 13, Visits: 272
I don't see the script in the article. Am I missing anything?

Stan
Post #1102638
Posted Tuesday, May 3, 2011 2:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 3:53 AM
Points: 39, Visits: 17
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
Posted Wednesday, November 16, 2011 4:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, August 2, 2012 3:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #1339035
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse