Output Full Result Set to a Table - Article

Question

Output Full Result Set to a Table - Article

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 30, 2016 at 12:36 pm

#337811

Does anyone know of a good article on Outputting a Full Result Set to a Table?
I have the SQL Task created but is about it.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

This is quite a vague question, and solutions can vary depending on what you're doing and where the data is coming from. Could you give a little more information about what your aims and goals are, and what you're trying to achieve? Even a broad sweep of something like "I have 20 Excel files, which are delivered on a weekly basis, need to be transferred into a database and relationship between keys achieved. What's the best way of getting all the data into SQL tables, most likely staging, and I will do the relational building from there" would give us at least some idea of what you want to do.

Thanks ^_^

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

DamianC SSCrazy Eights Points: 8023 More actions · Answer 2

Do you mean a stored procedure that returns to 'with results set' in SSIS?

- Damian

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

You mean like using a query/stored procedure as source in the Data Flow task?

That's part of SSIS 101.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

ok, I'm sorry for not being clear.

The following article is great but it does it drops off on returning full record set.

https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets

I need to get the result set into a table.

Thank you!:-)

Edit correct URL

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Thom A SSC Guru Points: 99143 More actions · Answer 5

That article there is talking about returning a single row and then inserting those values into variables. Is that your aim here? Your title says that you want to put a full result set into a table, ergo Move a dataset from one table to another. You wouldn't really need SSIS to do this, depending on your circumstance. For example, this would simply achieve your goal on a very simple matter:

USE DevTestDB;

GO

IF EXISTS (SELECT [name] FROM sys.tables WHERE [name] = 'CurrTables') GO

DROP TABLE CurrTables;

END

GO

SELECT t.name

INTO CurrTables

FROM sys.tables;

GO

Can you provide more information please.

Are you actually looking to loop through an entire dataset and load each row/column to a varaible, so you can achieve something else?

Cheers.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Welsh Corgi (12/1/2016)
ok, I'm sorry for not being clear.
The following article is great but it does it drops off on returning full record set.
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets
I need to get the result set into a table.
Thank you!:-)
Edit correct URL

You don't need an Execute SQL Task, you need a Data Flow Task and use a Query as the Source.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Luis Cazares (12/1/2016)
Welsh Corgi (12/1/2016)
ok, I'm sorry for not being clear.
The following article is great but it does it drops off on returning full record set.
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets
I need to get the result set into a table.
Thank you!:-)
Edit correct URL
You don't need an Execute SQL Task, you need a Data Flow Task and use a Query as the Source.

OK I'm using an Execute SQL Task with a select query.

Please refer to the article.

I assume that I did not do it correctly.

How do I move forward?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Welsh Corgi (12/1/2016)
Luis Cazares (12/1/2016)
Welsh Corgi (12/1/2016)
ok, I'm sorry for not being clear.
The following article is great but it does it drops off on returning full record set.
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets
I need to get the result set into a table.
Thank you!:-)
Edit correct URL
You don't need an Execute SQL Task, you need a Data Flow Task and use a Query as the Source.
OK that is what I'm doing.
How do I move forward?

Stand up, put on foot in front of the other, do the same with the other foot and repeat as necessary.

If that's not what you need, be sure to explain yourself CLEARLY.

Extract Data by Using the OLE DB Source

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thom A SSC Guru Points: 99143 More actions · Answer 9

AS Luis said, you don't need an execute, you need a dataflow. Place a dataflow task on your contro,l flow, open it up. Place an OLE DB source, and an OLE DB Destination. If you need any transformations, place those as well and then, just like the control flow, join up the nodes with the lines 🙂

We really can't give you any more than that as we don't know what you're trying to achieve.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

I just ask for guidance.

I think that it is pretty clear what I want.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

Welsh Corgi (12/1/2016)
I just ask for guidance.
I think that it is pretty clear what I want.

I'm sure that you've been guided to this article before, but this is an example on how it's not clear.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

I also shared with you an article that guides you through the process in my previous post.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thom A SSC Guru Points: 99143 More actions · Answer 12

Welsh Corgi (12/1/2016)
I just ask for guidance.
I think that it is pretty clear what I want.

All you've asked is "How do you get all the rows from a table in SSIS". That doesn't give us any information, as I said. What is your goal? What are you doing with all these rows? Are you putting them into another table? Extracting them into CSV files? Looping round them and sending emails?

Every one of these has a different answer.

Your question is about as clear as "How do I cook?". Without knowing what it is you want to cook, someone would find it very hard to tell anything helpful.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

Thom A (12/1/2016)
That article there is talking about returning a single row and then inserting those values into variables. Is that your aim here? Your title says that you want to put a full result set into a table, ergo Move a dataset from one table to another. You wouldn't really need SSIS to do this, depending on your circumstance. For example, this would simply achieve your goal on a very simple matter:
USE DevTestDB;
GO
IF EXISTS (SELECT [name] FROM sys.tables WHERE [name] = 'CurrTables') GO
DROP TABLE CurrTables;
END
GO
SELECT t.name
INTO CurrTables
FROM sys.tables;
GO
Can you provide more information please.
Are you actually looking to loop through an entire dataset and load each row/column to a varaible, so you can achieve something else?
Cheers.

I believe that you are right. I do not need output to an SSIS Full result Set when I can simply use T-SQL.

Thank you for your courteous response. 😎

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

DamianC SSCrazy Eights Points: 8023 More actions · Answer 14

With ssis you can

Create an sp that performs your select.

Use the sp as the source using with result sets followed by all the columns you require along with their type.

Then map to a destination.

I've used this quite a bit as you can include temp tables without any issues.

- Damian