Insert results of huge query into my table with Stored Proc

[MacroAlan]

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?

Thanks


Alan

 

[Olaf Doschke]

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

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

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/statements/insert-transact-sql?view=sql-server-2017

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name