Storing query results in SQL table

Hey guys/girls,

I was wondering if there is a way to store the report query results into a SQL database for use within an application. I would like to have the query information used in the report updated to a SQL table for later use. The other catch would be to deploy the information to a separate datasource location than the report is using (because the information will be stored in a different location than where the report is gathering information.

Thanks!

Answer this question

Storing query results in SQL table

WinFormsUser13232
BTW, it is like IM'ing on here! :)
csi_hugh
I got this to work:

INSERT INTO TestTable
(Test)
SELECT Test2
FROM TestTable2

Now my new issue is that I can't set up a datasource to point only to the server, so I can SELECT from one database and INSERT to another. You have to specify an Initial Catalog and without it, it won't let you select from anything. I tried MYDatabasename.TestTable and it won't accept it.
krisvenki
How do you can I write a stored procedure to push data to a database that is on a separate SQL server installation

Thanks for the idea!
redneon
OK, thats the threepart name using

INSERT INTO DatabaseName.SchemaOrObjectOwner.Objectname
(...Do the rest here...)

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---
LouArnold
Hi,

sure. the easiest thing would be to use a stored procedure which would insert the results first in a table and then afterwards within the same procedure insert the data in a persisted table.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---
JR-J
Also, the databases are on the same server for now. I just want to pull from one and insert to another.
Shivapc
If its on a separate installation you will have to create a linked server. Then you can simply use the following syntax to insert data:

INSERT INTO LinkedServerName.DatabaseName.ObjectOwnerORSchema.ObjectName
SELECT (...)

More about creating linked servers can be found in the BOL.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---
louissan
YOu are missing two parts in the frou part name, you additional have to prefix the linked server name and the database name you want to pull data from / insert data into. (Just see the above mentioned samples for doing this)

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---
Trashey
SELECT Test
FROM SSRS.dbo.TestTable

That worked to select from it. Why do you have to prefix with "dbo"
Ranal
What exactly is the dbo Database-owner or database-object
Chris Coddington
What else would work other than database-object
esamsalah
What do you mean by that You can access any database object like a stored procedure, table etc.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---
Kendal Walton
Thats like Instant Messaging here :-)

YOu will have to specify the owner of the object, that SQL Server can find it. You can also use the name without using a owner like:

Select * from DatabaseName..ObjectName

whereas SQL Server will look for the Schema that is dedicated to the current logged in user.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---
Alan Robbins
I tried this method and now I am receiving an error "Invalid object name". I have "Public" set to allow insert and delete and I am pulling from one table in the same database and pushing it to another.

INSERT INTO TestTable.Test
SELECT TestTable2.Test2
FROM TestTable2

BJ
Storing query results in SQL table

Answer this question