June 13, 2018 at 5:02 pm
If I have a complicated SQL query with joins and unions and with statement, and I would like to use the result of the query to insert and update 3 or 4 different tables, how can I store the query and reuse it?
I don't want to repeat the query to do each update and insert, for it has so many lines.
Should I store it in a temp table, is that the only choice?
Thanks,
June 13, 2018 at 5:36 pm
sqlfriends - Wednesday, June 13, 2018 5:02 PMIf I have a complicated SQL query with joins and unions and with statement, and I would like to use the result of the query to insert and update 3 or 4 different tables, how can I store the query and reuse it?
I don't want to repeat the query to do each update and insert, for it has so many lines.
Should I store it in a temp table, is that the only choice?Thanks,
Do you mean store the results of the query? You can use a temp table. Other options would be a table variable if it's not a lot of data. You can use a static table to use like a staging or work table which you may want to do if this is a regular process.
Sue
June 13, 2018 at 5:46 pm
Thanks Sue. that is helpful. SQL is set based, these are the options I think reasonable.
By comparison to Oracle, if I can ask here, what other options availble in PL_SQL for the same case I described in original post?
So probably create a cursor, then process row by row, do insert first, then update , correct? any other options?
Thanks
June 13, 2018 at 6:08 pm
sqlfriends - Wednesday, June 13, 2018 5:46 PMThanks Sue. that is helpful. SQL is set based, these are the options I think reasonable.By comparison to Oracle, if I can ask here, what other options availble in PL_SQL for the same case I described in original post?
So probably create a cursor, then process row by row, do insert first, then update , correct? any other options?
Thanks
Not necessarily. Either one support the insert into...select ...from worktable (or temp table). Same syntax as that's just standard SQL.
Table variables would be PL/SQL and those aren't the same between the two. So you wouldn't use table variables for this in Oracle. You'd have other options in Oracle if you are using PL/SQL as that has the extensions of the procedural methods, objects, etc.
Both have temp tables but the are implemented differently - work essentially the same. Oracles are static tables when you create them but the data is the temporary part and that can be session or transaction based. But the insert into a table for either one's temp table would be just that same standard SQL.
Sue
June 14, 2018 at 12:50 pm
Starting with SQL Server 2008 user-defined table type is an option allowing passing "tables" to and from procedures so that could be an option.
June 15, 2018 at 8:34 am
Joe Torre - Thursday, June 14, 2018 12:50 PMStarting with SQL Server 2008 user-defined table type is an option allowing passing "tables" to and from procedures so that could be an option.
As they are read only I believe that is to procedures not from.
June 15, 2018 at 12:07 pm
sqlfriends - Wednesday, June 13, 2018 5:02 PMIf I have a complicated SQL query with joins and unions and with statement, and I would like to use the result of the query to insert and update 3 or 4 different tables, how can I store the query and reuse it?
I don't want to repeat the query to do each update and insert, for it has so many lines.
Should I store it in a temp table, is that the only choice?Thanks,
Your narrative makes it sound like you might want to use a view. The text of the query would materialize inside the statement for you invoke it, and the optimizer could drop out any code that was dead for that particular statement.
Please post DDL and follow ANSI/ISO standards when asking for help.
June 15, 2018 at 12:08 pm
Thank for the correction Lynn
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply