I am writing a vbs script that parses a series of files and inserts selected information into a database. This script will be run at midnight ever night. So i am faced with this problem:
1. For each record i'd like to insert i need to check if it is there first
2. How can i avoid a new query for each row that i need to insert?
-
Jachin
Complexity=Stupidity
-
blowdart
Peek-a-boo
Jachin wrote:I am writing a vbs script that parses a series of files and inserts selected information into a database. This script will be run at midnight ever night. So i am faced with this problem:
1. For each record i'd like to insert i need to check if it is there first
2. How can i avoid a new query for each row that i need to insert?
Wrap it in a stored procedure that does the check as well? If it's there, don't insert, don't error. Or are you stuck with a database that doesn't do stored procs? If so, depending on how many records, you could pull your unique keys out first, for a large one time hit at startup and then check existance in the script.
-
You could use an INSERT statement with a WHERE clause to check whether the data you plan to insert already exists.
Something like this:
INSERT INTO MyTable (id)
SELECT id
FROM SourceData
WHERE id NOT IN (SELECT id FROM MyTable)
or this:
INSERT INTO MyTable (id)
SELECT id
FROM SourceData
WHERE NOT EXISTS (SELECT id FROM MyTable WHERE MyTable.id = SourceData.id) -
blowdart
Peek-a-boo
gregoryw wrote:You could use an INSERT statement with a WHERE clause to check whether the data you plan to insert already exists.
Something like this:
INSERT INTO MyTable (id)
SELECT id
FROM SourceData
WHERE id NOT IN (SELECT id FROM MyTable)
or this:
INSERT INTO MyTable (id)
SELECT id
FROM SourceData
WHERE NOT EXISTS (SELECT id FROM MyTable WHERE MyTable.id = SourceData.id)
Parameterise it please! @id, then even if you can't use stored procs you can still use ADO parameters and not have to worry about sql injection.
-
W3bbo
Work hard; increase production; prevent accidents, and be happy.
blowdart wrote:you can still use ADO parameters and not have to worry about sql injection.
news to me, please do tell more
-
blowdart
Peek-a-boo
W3bbo wrote:
blowdart wrote:you can still use ADO parameters and not have to worry about sql injection.
news to me, please do tell more
Oh! Really?
Ok, considerinsert into insecticide (description)
heh.
values ('Another channel9 post about it!');
Now I'm sure we all know the dangers here, so I'm not going to explain them.
However you can mitigate by using sql parameters.insert into insecticide (description)
Now it looks just like a stored proc would, correct? Thing is it doesn't have to be one; simple create an ADO command using the line above and attach a parameter to it named @description;
values (@description);using( SqlConnection con = yourConnection )
ADO will take care of escaping and all the other bits and pieces for you. Or it should anyway.
{
con.Open();
using( SqlCommand cmd =
new SqlCommand("insert into insecticide (description) values (@description);",
con) )
{
cmd.Parameters.Add("@description", descriptionValue);
using( SqlDataReader rdr = cmd.ExecuteReader() )
{
...
}
}
}
So even if you're stuck with an older version of MySQL you can still protect yourself.
-
footballism
Another Paradigm Shift!
W3bbo wrote:blowdart wrote:you can still use ADO parameters and not have to worry about sql injection.
news to me, please do tell more
For more infomation about SQL injection attack, please refer to this article in MSDN
Sheva
-
W3bbo
Work hard; increase production; prevent accidents, and be happy.
footballism wrote:
W3bbo wrote: blowdart wrote:you can still use ADO parameters and not have to worry about sql injection.
news to me, please do tell more
For more infomation about SQL injection attack, please refer to this article in MSDN
Sheva
I know about SQL injection, and I'd heard about ADO Params a while back, but I didn't think they were used like the way blowdart demonstrated.
-
blowdart
Peek-a-boo
W3bbo wrote:
I know about SQL injection, and I'd heard about ADO Params a while back, but I didn't think they were used like the way blowdart demonstrated.
Well enjoy
-
Maurits
AKA Matthew van Eerde
Jachin wrote:
1. For each record i'd like to insert i need to check if it is there first
2. How can i avoid a new query for each row that i need to insert?
Depends on the table...
In some circumstances you can get away with declaring a "unique" index on the relevant fields, and checking the "ignore duplicates" box. Then inserts that violate the uniqueness are ignored - no effect, no error. -
Jachin
Complexity=Stupidity
In the end the best approach seemed to be a disconnected recordset. I'm working with about 2500 records so it seems fairly fast.
What I did:
1. created a disconnected recordset
2. used the Find method to see if the values was already there
3. If there, update otherwise insert.
Works a treat too. -
I didn't know about that. That actually makes for cleaner code too, thanks! edit: Quote tag isn't working, d'ohblowdart wrote:Oh! Really?
Thread Closed
This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.