|
RJS wrote:
Is there any way to do a multiple inserts in a stored procedure in SQL2000?
You could write a while loop inside the stored procedure, there are many ways of doing something like this.
Nick Parker
May your glass be ever full.
May the roof over your head be always strong.
And may you be in heaven half an hour before the devil knows you’re dead. - Irish Blessing
|
|
|
|
|
Can you give more details?
Are these just INSERTS into multiple tables?
Are the INSERTS dependent on each other?
Cheers,
Simon
"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)
|
|
|
|
|
Simon,
thanks for the reply.
The scenario is like this:
I have (let's say) 3 records or more in my .net page. Example:
"John", "123 Anywhere", "City1"
"Rudy", "345 Anywhere", "City2"
"Tom", "335 Anywhere", "City2"
...
...
Is it possible to do a multiple inserts in a stored procedure to save those 3 records or more in a table? If so, how do I pass the parameters since I will not know what is the maximum records that will be sent at once? Thanks. 
|
|
|
|
|
What you would like to do is the basically pass a recordset to a stored procedure in SQL Server 2000. (?)
The only way I know of to do this is to create an Xml file, pass it as a text (ntext in .Net ) parameter to the stored procedure. And then use the OPENXML SQL statement, which enables you to treat an XML file as a table (check OPENXML in Books Online).
You can then easily use something like
INSERT INTO MyTable (val1, val2, val3)
SELECT val1, val2, val3
FROM OPENXML(...)
It very performant and works like a charm!
Hope this helps,
Morty
|
|
|
|
|
That's one way, Morten.
Another, which is very dependent on the type of data you're passing through, could pass a delimited list (a big string) to the stored proc and use some of the following code:
<br />
DECLARE @Array varchar(50)<br />
DECLARE @iStart int<br />
DECLARE @iEleFound int<br />
DECLARE @iMaxEleLen int<br />
DECLARE @iNextDelimIndex int<br />
DECLARE @iEleLen int<br />
<br />
SET @Array = '1,2,3,4,5,6,7,8,9,65,66,67,100,101,102,654321'<br />
SET @iStart = 0<br />
SET @iEleFound = 0<br />
SET @iMaxEleLen = 5<br />
SET @iNextDelimIndex=0<br />
SET @iEleLen =0<br />
<br />
print '---------'<br />
print 'LENGTH of string: ' + CAST(LEN(@Array) AS VARCHAR)<br />
while CHARINDEX ( ',' , @Array,@iStart ) > 0 -- for each element in the array<br />
begin<br />
SET @iEleLen =1 --default <br />
--if not ',' or ''<br />
if (CAST(SUBSTRING(@Array, @iStart + 1, 1) AS char(1)) <> ',') and not(len (SUBSTRING (@Array, @iStart + 1, 1)) = 0)<br />
begin--element found at this index<br />
<br />
SET @iNextDelimIndex = CHARINDEX ( ',' , @Array,@iStart+1) <br />
if(@iNextDelimIndex=0)<br />
begin<br />
SET @iNextDelimIndex = CAST (LEN(@Array) AS VARCHAR)+1<br />
print 'latest element is next:'<br />
end <br />
SET @iEleLen = @iNextDelimIndex - @iStart<br />
--DB function would occur here<br />
print 'NEXT DELIM LOCATION-->' + CAST(@iNextDelimIndex AS VARCHAR(10))<br />
print 'VALUE FOUND-->' + RTRIM( CAST(SUBSTRING(@Array, @iStart + 1, @iNextDelimIndex - @iStart-1) AS char(10)) ) + '<--'<br />
<br />
<br />
SET @iEleFound = @iEleFound + 1 <br />
<br />
<br />
end<br />
<br />
else --no element found at this index<br />
begin<br />
print 'do nothing'<br />
end<br />
<br />
SET @iStart = @iStart + @iEleLen<br />
<br />
end --if (CAST(SUBSTRING(@Array, @iStart + 1, 1) AS char(1)) <> ',') and not(len (SUBSTRING (@Array, @iStart + 1, 1)) = 0)<br />
<br />
print '------ ELEMENTS FOUND: ' + CAST(@iEleFound AS CHAR(2)) + ' ---------'<br />
Note: not fully tested, but should give you a starting point.
Cheers,
Simon
"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)
|
|
|
|
|
This is a followup to my previous question in the VisualC++ forum[^]. As no one was able to help me there (so far) and I have traced my problem to a failed SQLGetCursorName() call, I decided that this may be a better forum for my question.
I traced my problem into the CRecordSet::BuildUpdateSQL() function (Line 3394 of dbcore.cpp)
if (m_strCursorName.IsEmpty())
{
RETCODE nRetCode;
TCHAR szCursorName[MAX_CURSOR_NAME+1];
SWORD nLength = _countof(szCursorName)-1;
AFX_SQL_SYNC(::SQLGetCursorName(m_hstmt,
reinterpret_cast<SQLTCHAR *>(szCursorName), _countof(szCursorName), &nLength));
if (!Check(nRetCode))
ThrowDBException(nRetCode);
m_strCursorName = szCursorName;
}
m_strUpdateSQL += m_strCursorName;
Now according to MSDN[^]:
<quote>
Cursor names are used only in positioned update and delete statements (for example, UPDATE table-name ...WHERE CURRENT OF cursor-name). For more information, see "Positioned Update and Delete Statements" in Chapter 12: Updating Data. If the application does not call SQLSetCursorName to define a cursor name, the driver generates a name. This name begins with the letters SQL_CUR.
Note In ODBC 2.x, when there was no open cursor and no name had been set by a call to SQLSetCursorName, a call to SQLGetCursorName returned SQLSTATE HY015 (No cursor name available). In ODBC 3.x, this is no longer true; regardless of when SQLGetCursorName is called, the driver returns the cursor name.
SQLGetCursorName returns the name of a cursor whether or not the name was created explicitly or implicitly. A cursor name is implicitly generated if SQLSetCursorName is not called. SQLSetCursorName can be called to rename a cursor on a statement as long as the cursor is in an allocated or prepared state.
A cursor name that is set either explicitly or implicitly remains set until the StatementHandle with which it is associated is dropped, using SQLFreeHandle with a HandleType of SQL_HANDLE_STMT.
</quote>
So now I can assume that MyODBC is an ODBC version 2.x because of the exception thrown. Now my question is, where would I place a call to SQLSetCursorName() and where do I get the 'statementhandle' needed in the call?
And here I thought this would be easy 
CPUA 0x5041
Sonork 100.11743 Chicken Little
"So it can now be written in stone as a testament to humanities achievments "PJ did Pi at CP"." Colin Davies
Within you lies the power for good - Use it!
|
|
|
|
|
Hello,
I'm using an .mdb file as a database for a project i'm working on. I have a series of words that I have to loop through and for each word I have to:
1) Check if the word is already in the table (SELECT COUNT(*) ... )
2) If it is, then update count (UPDATE ... )
3) If does not exists, create entry (INSERT INTO ... )
I'm using OLE DB's CDynamicAccessor with SQL queries but the search part (SELECT COUNT(*) WHERE word = 'blabla') is very slow. Is there a way to make this any faster? I need something like "find first occurence of... ".
By the way, maybe it's because I set the tables poorly (I didn't use indexes and such...) maybe the problem's there!
Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
I never used CDynamicAccessor before, but you are hitting too much the database.
use:
UPDATE table SET count = count + 1 WHERE word = 'blabla'
Also, check if there is an index on the word field.
With OLEDB you should have something similar to ADO's RecordsAffected. Then, if RecordsAffected == 0, you execute INSERT INTO
Q261186 - Computer Randomly Plays Classical Music
|
|
|
|
|
Success!  It's faster but not enough... sometimes it will take up to 20 seconds to go through about 1700 words in a table that contains only 764 words (it will likely contain 10X that amount eventually).
I did set an index for the word field... is there anything else I could do to improve speed and performance?
Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
LukeV wrote:
that contains only 764 words (it will likely contain 10X that amount eventually).
Only 7000 words!? I supposed you have much, much more data than this!
Well, load the entire table to memory with a SELECT * (a std::map would be just fine), drop it and rewrite the whole table to disk...
The whole process should take only 2 or 3 seconds this way.
Q261186 - Computer Randomly Plays Classical Music
|
|
|
|
|
Daniel Turini wrote:
Only 7000 words!? I supposed you have much, much more data than this!
It could be more, since the table will grow with time. I wouldn't be suprised to see it around 10-20k but I don't think it will grow any bigger than that...
Daniel Turini wrote:
drop it and rewrite the whole table to disk...
The whole process should take only 2 or 3 seconds this way.
So perhaps what I should do is the get the table at the beginning of the app, update the map and rewrite the table to disk at the end of the program... But if the app crashes, I'll lose everything and I don't know if it would consume too much memory when the table grows...
I'll let you know how it turns out. Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
LukeV wrote:
By the way, maybe it's because I set the tables poorly (I didn't use indexes and such...) maybe the problem's there!
Why should you expect database to be fast if you don't bother to set up indexes? Why database at all, if you don't index your data? Just store everything in a large text file.
You should realize that there's no magic in programming. Search algorithms are deterministic - and you are required to organize data into tables and indexes based your logical dependencies and search criteria.
BTW, are there any reasons you yse dynamic accessors and not static? This will also cost you some ticks.
Vagif Abilov
MCP (Visual C++)
Oslo, Norway
Hex is for sissies. Real men use binary. And the most hardcore types use only zeros - uppercase zeros and lowercase zeros.
Tomasz Sowinski
|
|
|
|
|
Vagif Abilov wrote:
Why should you expect database to be fast if you don't bother to set up indexes? Why database at all, if you don't index your data? Just store everything in a large text file.
I know...  It's just that I never had a project that required a huge database so speed wasn't a concern.
Vagif Abilov wrote:
BTW, are there any reasons you yse dynamic accessors and not static? This will also cost you some ticks.
I'll give that a try as well... Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
Access isn't the fastest DB, but you should be able to achieve adequate performance for 20k rows of a relatively small table.
First, make sure you used indexes and use the right one. If the column 'word' contains a unique list of words, use the "Indexed - no duplicates" type of index.
Second, check the data type and size you used. It looks like you are using a "Text" data type which should be OK, but check the length of the column. The default is 50. Is this more than you need, if so, use a smaller value. Access always uses varchar data but you can force it to fixed-width by pre-filling the column with non-space data (such as the under-score _).
Third, the select count(*) statement will require a full table-scan unless the "indexed - no duplicates" option is used on the column. If you just need to know if the item is in the table, just do a "SELECT WORD from table where word = 'blablabla'". This would generate better performance if not using a unique index.
Fourth, consider removing the "select" statememt altogether. Just do an UPDATE and if it fails check the SQLCODE and perform an INSERT.
Sixth, if this is a single threaded GUI app you could just open a recordset against the table and disconnect it from the DB.
Seventh, if this is a GUI app (not ASP based) you can just load the whole word list into memory. If the maximum length of a word is 50 bytes and you can have 30K words, then you will need 1.5mb of ram.
Eighth, if you use an in-memory list and you don't want to risk losing updates, add a new table to your db called "Update_History" with an auto-number column and a 'Word' column. Each time the in-memory list is incremented, insert a record into this table. When your app finihes normally, walk through this table and perform the UPDATEs/INSERTs against your original table and then delete the table contents. When you app launches, check this table and if it is not empty just do the updates then.
|
|
|
|
|
What I found out is that it's real slow when I'm debugging the app and like 10X faster when I just run the app (not from the debugger) DOH! 
But I'm positive that it used to be slower than that... so the changes you people suggested proved to be worthy. Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
Hi all
I have aan XSD file that represents a schema of a set of tables in my DB. (it was generated with the MSDatasetGenerator, but I can't use the code it created, only the xsd.)
I create a dataset and use the ReadXmlSchema method to fill the schema info from the file to the dataset. now I populate this dataset with information from another source (not with any dataadapter, I'm working straight with the table collection, I have another xml file with the rules for that).
Now I need a way to update the data in the DB. the schema is the same as the db, same table names, same columns, just insert all the data to the DB tables.
No hardcoded update command, or insert commands.
I tried using SQLXML but with no success.
any ideas?
thanks in advance
Noam Ben Haim
Web Developer
Intel
noam.ben.chaim@intel.com
|
|
|
|
|
Hello all,
I am trying to connect from ASP.NEt to a MySql server.
I have the MySql Driver 3.51 installed.
I have created a DSN also.
Which set of components can I use to do the connectivity ?
I also has trouble with the connection string 
Thanks in advance
Smitha
It's easy to sit there and say you'd like to have more money. And I guess that's what I like about it. It's easy. Just sitting there, rocking back and forth, wanting that money.
|
|
|
|
|
Just as a note, there are _a few_ managed providers (ie like System.Data.SqlClient) for MySql that should work well, and probably a lot faster than an ODBC approach.
I don't remember any links, but search sourceforge.net (or google) or something
Morty
|
|
|
|
|
Thanks Morty
Tweety
It's easy to sit there and say you'd like to have more money. And I guess that's what I like about it. It's easy. Just sitting there, rocking back and forth, wanting that money.
|
|
|
|
|
Hi, I need to install (MICROSOFT EXCEL DRIVER (*.xls) in a computer with win98. Anybody knows what files do I need or when can I get information about it?
My program uses :
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=TRUE;CREATE_DB=\"%s\";DBQ=%s", sDriver, sExcelFile, sExcelFile);
where sDriver must be MICROSOFT EXCEL DRIVER
|
|
|
|
|
Jet 4.0[^] is what you need, I believe.
Paul
Why don't you take a good look at yourself and describe what you see - Led Zeppelin, Misty Mountain Hop
|
|
|
|
|
Ok, thank you.
But if I want to redistribute my app, is there an easier way to do it? I don't want to force to the user to download Jet4.0 , is it not possible to install only excel driver easily?
Thanks.
|
|
|
|
|
José Luis Sogorb wrote:
is it not possible to install only excel driver easily?
That is considered "easily" . It's a 3-ish Mb download and you can even wrap it in your own installer. You probably can find a way to isolate the Excel files and registry entries but it really isn't recommended.
Paul
Why don't you take a good look at yourself and describe what you see - Led Zeppelin, Misty Mountain Hop
|
|
|
|
|
Could someonehelp me to write this stored procedure?
I have two datetime column and ID column(its integer).I pass an ID to stored procedure and want to check if today is between these two date,and return true or false.
Thanks
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
<br />
-- create the test table<br />
create table t1(ID int, date1 datetime, date2 datetime)<br />
<br />
-- insert test rows<br />
insert into t1 (ID, date1, date2) values (1, '15 Jan 2001', '20 Feb 2003')<br />
insert into t1 (ID, date1, date2) values (2, '01 Sep 2000', '19 Apr 2001')<br />
<br />
-- drop the proc if it exists<br />
if object_id('sp_CheckDates') is not null drop procedure sp_CheckDates<br />
go<br />
-- create the proc<br />
create procedure sp_CheckDates @ID int<br />
as<br />
declare @Date1 datetime<br />
declare @Date2 datetime<br />
select @Date1 = date1, @Date2 = date2 from t1 where ID = @ID<br />
<br />
if @Date1 is not null and @Date2 is not null begin<br />
if datediff(dd, @Date1, getdate()) > 0 and datediff(dd, @Date2, getdate()) < 0 begin<br />
print 'Bingo!'<br />
return 0<br />
end else begin<br />
raiserror ('Date not in specified range', 16, 1)<br />
return 1<br />
end <br />
end else begin<br />
raiserror('Specified ID does not exist.', 16, 1)<br />
return 1<br />
end<br />
go<br />
<br />
-- test execution<br />
exec sp_CheckDates @ID=1 <br />
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
