results of a query into a new table into sql

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!
jamesmetcalf74Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
funwithdotnetConnect With a Mentor Commented:
SELECT [...] INTO newTable FROM oldTable

Open in new window

0
 
jamesmetcalf74Author Commented:
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
 
arnoldCommented:
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
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
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
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.