?
Solved

Loading query results directly into a database in sql server 2005

Posted on 2011-04-29
7
Medium Priority
?
388 Views
Last Modified: 2012-05-11
I have fired a query against a linked server from SQL Server 2005.
I would like to load the results directly into  a local database table by creating the table dynamically, is this possible without first saving the results as a csv file and then loading into a pre created table in the local database?
0
Comment
Question by:blossompark
  • 4
  • 2
7 Comments
 
LVL 9

Expert Comment

by:kaminda
ID: 35490140
Yes you can do this as you are executing the query from your local server.
0
 

Author Comment

by:blossompark
ID: 35490176
Hi Kaminada, thanks for your response,

I have fired the following query at a linked server which returns a complete table

SELECT  * FROM OPENQUERY(PROD,'
select *  from PEOPLE
 ')

But do I have to create a table in the target database before I can load the results of the query?
This table has about 50 columns so i am trying to avoid manually creating it, just hoping there is some sort of "smart" way to create a table on the fly if you understand me
0
 
LVL 3

Assisted Solution

by:Mrugesh1
Mrugesh1 earned 200 total points
ID: 35490180
Refer below query...

Insert into localTable SELECT  * FROM OPENQUERY(PROD,'
select *  from PEOPLE
 ')
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:blossompark
ID: 35490201
Hi Mrugesh1, thanks for your response.
This will get the data into a local table in memory....how do i load "localTable into a database?
0
 
LVL 9

Accepted Solution

by:
kaminda earned 1800 total points
ID: 35490216
Try this


SELECT  * INTO yournewtablename FROM OPENQUERY(PROD,'
select *  from PEOPLE
 ')

0
 

Author Comment

by:blossompark
ID: 35490236
Hi Kaminda, thanks for that,
running that query now,,,takes about 20 mins so will update you when finished
0
 

Author Closing Comment

by:blossompark
ID: 35490387
Hi kaminda, perfect...worked ...and so simple!!! thank you!!


Hi Mrugesh1...thanks for your contribution!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Loops Section Overview
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question