November 6, 2007 at 6:55 am
Hi,
I am currently building a system to upload cruise ship holiday offers from flat files to SQL Server 2005.
I have uploaded data from a flat file (which was 26MB) into SQL Server 2005 to a table called "Princess1Tmp" for the cruise ship holiday offers, and have achieved this no problem using SSIS.
The next step required me to obtain the cruise ship ID from the "amCruiseShip" table and insert the cruise offers from "Princess1Tmp" into the "cruiseOffers" table with their respective Cruise Ship ID.
I have achieved this part of the process using VB Script and although it works, I was wandering if anyone new any way to speed up the second step.
I have listed my code below, if anyone has any ideas or could point me in the right direction it would be greatly appreciated.
Many Thanks Mark
'##################################################
'########## Princess sub-procedure 1. ######################
'##################################################
sub princessInsert1()
'SQL statement to select data from "Princess1Tmp table".
sql = "SELECT Princess1Tmp.Cruise_Number, Princess1Tmp.Embark_Date, Princess1Tmp.Air_City, Princess1Tmp.Air_Indicator, " &_
"Princess1Tmp.category, Princess1Tmp.Category_Status, Princess1Tmp.Best_Price, " &_
"Princess1Tmp.Brochure_Price, Princess1Tmp.Duration, " &_
"CarnivalGroupVoyageDataTmp.ship_code, CarnivalGroupVoyageDataTmp.brand " &_
"FROM Princess1Tmp " &_
"INNER JOIN CarnivalGroupVoyageDataTmp ON CarnivalGroupVoyageDataTmp.voyage_code = Princess1Tmp.Cruise_Number " &_
"WHERE CarnivalGroupVoyageDataTmp.brand = 'PC'"
set conn = createObject("ADODB.Connection") 'Assign the database connection object to variable.
conn.Open CONNECT 'Open the database connection using the CONNECT variable.
set rs = createObject("ADODB.Recordset") 'Assign the database recordset object to variable.
rs.Open sql, conn 'Open the recordset with the values of the SQL statement.
WScript.StdOut.WriteLine("Princess 1 Script begin") 'Script processing indicator.
if rs("Cruise_Number") <> "" or rs("Best_Price") <> "" or rs("Brochure_Price") <> "" then
while not rs.EOF 'Loop while there are records to process.
strShipCode = rs("ship_code") 'The Ship_Code recordset is in the correct format.
sql1 = "SELECT ID FROM amCruiseShips WHERE cruiseLineID = '13' AND code = '" & strShipCode & "'" 'SQL statement to retrieve ship ID.
set rs1 = createObject("ADODB.Recordset") 'Assign the database recordset object to variable.
rs1.Open sql1, conn 'Open the recordset with the values of the SQL statement.
if not rs1.EOF then 'Loop while there are records to process.
cruiseShipID = rs1("ID") 'Assign Ship ID to variable.
end if
rs1.Close 'Close the recordset object variable.
set rs1 = nothing 'Set the recordset object variable to nothing.
sql = "INSERT INTO CruiseOffersTmp (voyageNumber, departureAirport, duration, departureDate, availability, cabinGrade, " &_
"cruiselineID, cruiseshipID, bestPrice, brochurePrice, uploadEntryDate, flyCruise) " &_
"VALUES " &_
"('" & rs("Cruise_Number") & "', '" & rs("Air_City") & "', '" & rs("Duration") & "', '" & rs("Embark_Date") & "', " &_
"'" & rs("Category_Status") & "', '" & rs("category") & "', '13', '" & cruiseShipID & "', " &_
"convert(money,'" & rs("Best_Price") & "'), convert(money,'" & rs("Brochure_Price") & "'), '" & now() & "', "&_
"'" & rs("Air_Indicator") & "')"
conn.Execute(sql) 'Open the database connection using the sql statement
rs.moveNext() 'Move to the next recordset item.
wend
end if
WScript.StdOut.WriteLine("Princess 1 Script complete") 'Script comlete indicator.
rs.Close 'Close the recordset object variable.
set rs = nothing 'Set the recordset object variable to nothing.
conn.Close 'Close the database connection.
set conn = nothing 'Set the connection object variable to nothing.
end sub
November 6, 2007 at 7:32 am
You could try something like this:
INSERT CruiseOffersTmp
(
voyageNumber,
departureAirport,
duration,
departureDate,
availability,
cabinGrade,
cruiselineID,
cruiseshipID,
bestPrice,
brochurePrice,
uploadEntryDate,
flyCruise
)
SELECT
pt.Cruise_Number,
pt.Embark_Date,
pt.Air_City,
pt.Air_Indicator,
pt.category,
am.cruiseLineId,
pt.Category_Status,
pt.Best_Price,
pt.Brochure_Price,
pt.Duration,
cgv.ship_code,
cgv.brand
FROM Princess1Tmp pt
INNER JOIN
CarnivalGroupVoyageDataTmp cgv ON
cgv.voyage_code = pt.Cruise_Number
inner join
amCruiseShips am on
am.code = cgv.ship_code
WHERE cgv.brand = 'PC' and am.cruiseLineID = 13
[edit]
seeing both on screen at the same time I can see that some of the columns are rearranged but you should get the idea..
[/edit]
November 6, 2007 at 8:57 am
Thanks for your help John, the SQL statement does the insert in 12 seconds.
Good work! 😀
November 6, 2007 at 9:03 am
Good stuff.. but please check the data is consistent with what you expect.. cheers jd..
November 6, 2007 at 9:07 am
Yes it is, the only thing I had to do was add distinct to the select statement and of course rearrange some of the columns.
Thanks again for your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply