×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Insert results of huge query into my table with Stored Proc

Insert results of huge query into my table with Stored Proc

Insert results of huge query into my table with Stored Proc

(OP)
Spent the last 2 weeks creating and tweaking a 150 line query pulling stuff from all over the place. My internal customer kept changing his mind what he needed.

Found that I need to write the whole thing to a table on SQL Server; so created a table this morning.

Started a stored proc and put a little data in the table to be sure I could TRUNCATE it.

Now, I am at the threshold and don't know what to do next. Can I copy the query into the proc and directly squirt it across? ponder

Thanks


Alan
smurf

RE: Insert results of huge query into my table with Stored Proc

Yes, you can, whatever a query creates as its result set can be put into a table by prepending INSERT INTO targettable before your SELECT query, so in the simplest case, a table copy is made by

CODE

INSERT INTO copy SELECT * FROM original 

The only precondition is obvious: The table has to function as storing the result, it has to have the right columns and data types.

As a side note, I would suggest you make yourself more familiar with the syntax notation used in the documentation of T-SQL, it's a specification of syntax not only valid for T-SQL or any SQL dialect but also used in many programming languages and it helps you see the options and variations in the syntax of commands.

And last not least, even if you'd only define a stored proc that delivers some result normally called by an outside clientside programming language, creating an associative array in PHP or a DataTable in a .NET language or anything else (in legacy times an ADO.REcordset in VB or VBA code), you can also make use of that stored procedure as the part of an INSERT statement as in

CODE

INSERT INTO targettable EXECUTE dbo.YourStoredProc 

So, overall, I don't know what to say, if your customer is a developer complaining that your stored proc isn't doing the last step of putting the result into a table instead of returning it, that could have been fixed by himself using the last syntax. And as you don't seem to know this, well... it's good you're asking, but all that is in the T-SQL documentation, even if you don't like to parse the syntax diagram and only look into the samples given, just take the time during the holidays and have a bit of reading through the basic four commands of SQL making up the life cycle of data: INSERT, SELECT, UPDATE and DELETE. If you think you know everything about these, be sure you'll be surprised about options like an OUTPUT clause and many more details. But this is all minimum knowledge you should have.

https://docs.microsoft.com/de-de/sql/t-sql/stateme...

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close