results of a query into a new table into sql

Posted on 2016-09-23
Medium Priority
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!
Question by:jamesmetcalf74
LVL 12

Assisted Solution

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

Open in new window


Author Comment

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
LVL 81

Expert Comment

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.......
LVL 38

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 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

LVL 70

Accepted Solution

Scott Pletcher earned 1000 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 */
FROM dbo.existing_table
/*INNER JOIN ...*/

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

624 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