Loading query results directly into a database in sql server 2005

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?
blossomparkAsked:
Who is Participating?
 
kamindaConnect With a Mentor Commented:
Try this


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

0
 
kamindaCommented:
Yes you can do this as you are executing the query from your local server.
0
 
blossomparkAuthor Commented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Mrugesh1Connect With a Mentor Commented:
Refer below query...

Insert into localTable SELECT  * FROM OPENQUERY(PROD,'
select *  from PEOPLE
 ')
0
 
blossomparkAuthor Commented:
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
 
blossomparkAuthor Commented:
Hi Kaminda, thanks for that,
running that query now,,,takes about 20 mins so will update you when finished
0
 
blossomparkAuthor Commented:
Hi kaminda, perfect...worked ...and so simple!!! thank you!!


Hi Mrugesh1...thanks for your contribution!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.