[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

results of a query into a new table into sql

Posted on 2016-09-23
5
Medium Priority
?
62 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 500 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 80

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 32

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

Enjoy!
0
 
LVL 70

Accepted Solution

by:
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 */
SELECT ...
FROM dbo.existing_table
/*INNER JOIN ...*/
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

650 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