2uzhan.com
Advertisement
Now Place:2uzhan.com » Create table and insert data based on the return of another query

Create table and insert data based on the return of another query

Microsoft SQL Server @ October 1, 2008   Views:0

Not sure if this is possible, but I want to run a query on a linked Oracle database.
Get the result.

Build a new table (tmp or not), create the columns as per the fields returned by the query and insert the data.

Is this possible?

I've done some google searching and couldn't find anything, well tbh not sure what to search on.

*** Edit: the reason I'm asking is that I'm lazy.. ***

I've been tasked with two projects.

1) - Importing the vmquery output from Netbackup.. About 50 columns of data and didn't fancy doing it all manually.

2) - Importing the output of another query which again hasn't been formalised, but will be a lot of fields across several tables.

*** Confession over ***

Cheers.

--------------Solutions-------------

Code:

IF object_id('dbo.temp_table99') IS NOT NULL BEGIN
  DROP TABLE dbo.temp_table99
END
GO

SELECT comma, separated, list, of, columns, not, the, dreaded, select, star
INTO   dbo.temp_table99
FROM   [linked_server_name].[database].[schema].[table]

SELECT *
FROM   dbo.temp_table99

Cheers, that works for one of em...... Can a permi table be built from the temp one?

Also can the same thing be done via bulk insert, or do you need the format file?

The above isn't a temporary table.
Temporary tables are can be identified by their # (or ##) prefix.

If the table exists then all you have to do is change the INSERT syntax.

Code:

INSERT INTO dbo.my_table99 (col1, col2)
SELECT col1
     , col2
FROM   dbo.my_other_table

Ah ha... didn't spot that, cheers again.

Tags:
© 2018 2uzhan.com Contact