Solved

results of a query into a new table into sql

Posted on 2016-09-23
5
44 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
Comment Utility
SELECT [...] INTO newTable FROM oldTable

Open in new window

0
 

Author Comment

by:jamesmetcalf74
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 125 total points
Comment Utility
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:
ScottPletcher earned 250 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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

16 Experts available now in Live!

Get 1:1 Help Now