SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create INSERT statements from Excel


Create INSERT statements from Excel

Author
Message
suslikovich
suslikovich
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 300
I don't see the script in the article. Am I missing anything?

Stan
damir.sovic
damir.sovic
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 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.
travbowls
travbowls
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
C# Gnu
C# Gnu
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2325 Visits: 659
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-mergeBigGrinays 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 Wink

C# Gnu
____________________________________________________


Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

patrickmnd10
patrickmnd10
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 1
Hi, i liked the insert macro, can you please let me know how to add delete statement with where condition (which is in in 1st (A1) cell. Appreciate your help.

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search