Solved

results of a query into a new table into sql

Posted on 2016-09-23
5
48 Views
Last Modified: 2016-09-28
I usually copy and paste the results of a query into excel and then data import the excel into sql for my newly constructed table.
Sometimes the data types and formatting get screwy.  I have a new query that results a ton of columns so putting them into excel and reformatting them is a pain in the butt.

I have no destination table created for the results of this query.  I the resulting table to be permanent so would like to avoid temp tables unless that would be an intermediary step.  I would also like to forgo creating the new table before i insert it into one.  That being said...
any ideas.  i need the columns names and datatypes.

im open to anysuggestions...
if i have to create a table i can.  my query has over a hundred columns though.

Thanks a ton!
0
Comment
Question by:jamesmetcalf74
5 Comments
 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 125 total points
ID: 41813233
SELECT [...] INTO newTable FROM oldTable

Open in new window

0
 

Author Comment

by:jamesmetcalf74
ID: 41813263
well that was ridiculously easy

ill give you the best answer regardless in a day or so but now i have a follow question.

so now i want to run the same query with a different select variable... ie.. giving me different results but the exact same output structure of rows and columns.   how do i  keep adding content to the same database that i just created..... from the very first query
0
 
LVL 77

Expert Comment

by:arnold
ID: 41813414
You need to provide more detail, an example of the dataset etc.
Is the change includes additional different columns
Are you trying to pull data from multiple tables and add them into this single table?

Have you considered creating a view if the purpose is temporary for query?

Depending on how you define the table, you can repeat the queries with different constraints and the sata will be added.......
0
 
LVL 25

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 125 total points
ID: 41815235
For the first time

Use below.

SELECT [...] INTO newTable FROM oldTable

After that each time use below

Insert into newTable SELECT [...] From old table

Enjoy!
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 41816380
First, create the table structure you need, without any data:

SELECT TOP (0) ...
INTO dbo.new_table
FROM dbo.existing_table
/*INNER JOIN ...*/

Doing a SELECT ... INTO with lots of actual INSERTs can lock up the system tables (at least in SQL versions before 2012, maybe, maybe not, after that).

If needed, ALTER any columns to match the final result of what you need:
ALTER TABLE dbo.new_table
    ALTER COLUMN column_name new_data_type NOT NULL;

Then create the appropriate clustered index:
CREATE /*UNIQUE*/ CLUSTERED INDEX new_table__CL ON dbo.new_table ( col1, col2 ) WITH ( FILLFACTOR = 99, SORT_IN_TEMPDB = ON ) ON [PRIMARY]; /*change filegroup name if needed*/

Finally, load the data into the table:

INSERT INTO dbo.new_table WITH (TABLOCK) /* TABLOCK helps performance by allowing minimal logging */
SELECT ...
FROM dbo.existing_table
/*INNER JOIN ...*/
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now