|
| |
|
|
seth
Posts: 312
From: canada
Status: offline
 |
from array to sql insert statement - 5/17/2005 12:49:29
hi, i created an single array of 1082 items, i need to put this items in to a sql table, array outputs as cellArray(i)
the table has 6 columns and each record's info has to be grabbed from the array, the info is in blocks of 6 array items.
for example:
cellArray(46)
cellArray(47)
cellArray(48)
cellArray(49)
cellArray(50)
cellArray(54)
is one record.
cellArray(59)
cellArray(60)
cellArray(61)
cellArray(62)
cellArray(63)
cellArray(67)
is the next record values
the the next record would be
cellArray(72)
cellArray(73)
cellArray(74)
cellArray(75)
cellArray(76)
cellArray(80)
now the tricky part that i cant seem to do is create an sql statement with the formula to pull earch record's worth of array values, the first array value needed for insert starts at cellArray(46) and the last array value needed is cellArray(1081)
this is what i'm tring to do effeciently:
sql = "INSERT INTO table_name (a1, a2, a3, a4, a5, a6)
VALUES ([cellArray(46,59,72...1073 ], [cellArray(47,60,73....1074], [cellArray(48,61,74...1075 ], [cellArray(49,62,75...1076 ], [cellArray(50,63,76...1077 ], [cellArray(54,67,80...1081 ])
//observations:
a1,a2,a3,a4,a5,a6 = increment by 13 for next records value (each record's block of 6 items start at 46 and increments by 13 - 46,59,72.)
a6 array items are + 4 above the consecutive 5 items
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: from array to sql insert statement - 5/17/2005 13:15:57
How about something like this:
<%
'assuming you already defined and filled cellarray()
DIM valueA1, valueA2, valueA3, valueA4, valueA5, valueA6, myStartNumber
valueA1 = ""
valueA2 = ""
valueA3 = ""
valueA4 = ""
valueA5 = ""
valueA6 = ""
myStartNumber = 46
FOR i = 0 TO 79
'it looks like you need 80 sets of records
'to make it dynamic would require some fancy calc but can be done
valueA1 = valueA1 & cellArray(myStartNumber + (13*i)) & ","
valueA2 = valueA2 & cellArray(myStartNumber + (13*i)+1) & ","
valueA3 = valueA3 & cellArray(myStartNumber + (13*i)+2) & ","
valueA4 = valueA4 & cellArray(myStartNumber + (13*i)+3) & ","
valueA5 = valueA5 & cellArray(myStartNumber + (13*i)+4) & ","
valueA6 = valueA6 & cellArray(myStartNumber + (13*i)+8) & ","
NEXT
'remove trailing commas
valueA1 = left(valueA1,len(valueA1)-1)
valueA2 = left(valueA1,len(valueA2)-1)
valueA3 = left(valueA1,len(valueA3)-1)
valueA4 = left(valueA1,len(valueA4)-1)
valueA5 = left(valueA1,len(valueA5)-1)
valueA6 = left(valueA1,len(valueA6)-1)
sql = "INSERT INTO table_name (a1, a2, a3, a4, a5, a6)
VALUES ('" & valueA1 & "','" & valueA2 & "','" & valueA3 & "','" & valueA4 & "','" & valueA5 & "','" & valueA6 & "')"
%>
Haven't tested the syntax at all but that's how I'd approach it. You do have patterns in there and it really boils down to an excersize in finding where the pattern repeats and find the ordinal for it (but you knew that already didn't you  )
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
seth
Posts: 312
From: canada
Status: offline
|
RE: from array to sql insert statement - 5/19/2005 12:22:04
i keep getting error of:
Microsoft OLE DB Provider for SQL Server error '80040e57'
String or binary data would be truncated.
/testing2.asp, line 178
and 178 is the insert into db call: set rs2 = db2.Execute(sSQL)
- would this be a db issue?
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: from array to sql insert statement - 5/19/2005 12:42:22
Usually that error means you're trying to put too much data into a field. For instance, you're trying to put a 300 character textarea into a standard Access text field (255 char max).
What I'd suggest is to do this before the line that errors:
Response.write(sSQL)
Response.end
Then see if it's what you expect. If it is what you expect, you'll have to increase the field size(s) in the SQL Server DB.
That make any sense? Hope it helps.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
seth
Posts: 312
From: canada
Status: offline
|
RE: from array to sql insert statement - 5/19/2005 12:50:05
I got it working, thanks - your code gave way for this working one:
For iPos=45 to Ubound(cellArray)-1 step 13
sSQL = "INSERT INTO todays_filings (a1, a2, a3, a4, a5, a6) VALUES ('" & cellArray(iPos) & "', " & _
"'" & cellArray(iPos+1) & "', " & _
"'" & cellArray(iPos+2) & "', " & _
"'" & cellArray(iPos+3) & "', " & _
"'" & cellArray(iPos+4) & "', " & _
"'" & cellArray(iPos+8) & "')"
response.write(sSQL & "<br/>")
set rs2 = db2.Execute(sSQL)
next
/// now the next big issue is that of duplicate records - in my case, a duplicate record is when the ALL values are the same in each columns from record to record, columns (a1,a2,a3,a4,a5, a6)
How can i have the sql2k db table not add a duplicate record. Can this be done in the sSQl statement - can DISTINCT be used with INSERT?
thanks
< Message edited by seth -- 5/19/2005 12:59:10 >
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: from array to sql insert statement - 5/19/2005 13:06:26
Does that really give you what you want? I guess I misunderstood your original description. Oh well. 
As to the duplicates, there are 2 ways that I can think of:
1. First is to check for duplicates before you add them. That would require quite a change to what you're currently doing, 'cause right now, you're reading, inserting, reading, inserting, etc. AFAIK you'd have to read 'em all into an anrray or something first, then do a lot of comparing, then do the inserts.
2. This probably is an easier way: I'd just bulk insert 'em all. Then build a stored procedure that removes dup's from the table. You'd trigger the stored procedure once you exit your INSERT loop.
Those make any sense?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
