November 17, 2004 at 6:08 pm
Hi there,
I want to insert ~50,000 records (all in one go) using store proc and passing parrameters of store proc by sql query
below is a scenario
exec CustomerInsert <Parameters>
<parameters> = SELECT abc.id, efg.name,............hij.category FROM abc INNER JOIN efg on efg.id = abc.id INNER JOIN hid.tid = efg.tid
or like
Exec CustomerInsert (select .......... from........)
Is this possible?????
Thanks
November 17, 2004 at 7:27 pm
If you are thinking about passing the entire SQL string as a parameter of the stored procedure, what is the point of even having the stored procedure? Why don't you just execute the SQL string?
A stored procedure is generally used where there is an ongoing requirement to repeatedly run it. Your requirement - to insert ~50,000 records - sounds like a one-off. If it is, you are better off just writing a custom bit of SQL script and executing it via QA and not clogging up your dbs with unused stored procs.
If I am wrong, please expand a bit on what you are trying to achieve and why, because this is not clear from your message.
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 17, 2004 at 8:13 pm
there is some calculation in the store proc which i can't do it on my select statement, and i ve got all the values of parameter through the select statement.
i am confused about what to do, here is the scenario i want to insert 50,000 records in one go, i have all the values except the value of primary key, which is been calculated in this 'CustomerInsert' store proc.
so now i have two ways either doing one-off
INSERT tblCustomer select ........from....., but then i don't have customerid to insert.
or
exec customerInsert (select........from.....)
Hope its clear now
November 19, 2004 at 3:36 am
I'm not really sure I follow... Can you not do the calculation of the primary key as part of the SELECT statement or is it too complicated. If that's the case can't you write an INSERT trigger to do the calculation on the fly?
OR... if that wont work how about doing it in a couple of steps....inserting your 50,000 rows into a temporary table; do an update of the temporary table to calculate all the primary key values; then just do a INSERT into your "real" table by doing a straight SELECT from the temp...
Does that help or make things more muddy?
Sam
November 19, 2004 at 6:15 pm
Sam's idea is good, only you should use a real table instead of a temporary one; Like dynamic SQL, using temp tables is bad for optimization.
Signature is NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply