0

My query result runs in 3+ million row items. How can I directly save the query as a table? I have tried to save results in .csv and then import that .csv file but then it only imports the first million rows and not the complete data.

Divya
  • 5
  • 2
  • What do you mean "save the query as a table"? As in create a table with the data in it? Have you considered using the `INTO` clause? – Thom A May 31 '21 at 11:05
  • @Larnu I mean I want the results to be saved as dbo.results in databaseX>Tables – Divya May 31 '21 at 11:15
  • It worked. I very new to SQL, apologies if this was too silly. Thanks @Larnu – Divya May 31 '21 at 11:19

2 Answers2

0

You can use the SELECT INTO syntax to save the results of a query as a new table.

SELECT *
INTO newtable
FROM oldtable
WHERE condition
restlessmodem
  • 418
  • 3
  • 12
  • 1
    SQL Server doesn't support `IN` to define the database in an `INTO`; you would need to use 3 part naming if you want the table to be created in a different database. – Thom A May 31 '21 at 11:17
  • @Larnu you are right, I removed it from the answer – restlessmodem May 31 '21 at 11:20
0

If you want to save the result of a query in another table of your database at run time then use "Select into Clause"

Lets assume your query with 3 million + records is

Select * from employees

If you want the result of above query to be inserted into another table, say employee_new, in database during runtime then modify your above query as below.

Select * 
Into employee_new
from employees

table employee_new created this way will have same column names as in your select statement. However, you will not have control on column datatypes of employee_new table.

If you want your columns to be of particular datatype, create a table before hand and use "INSERT INTO ...SELECT" statement like this. (I am assuming you are generating 3 columns, one of each int, varchar, datetime type, from your select query )

Create Table employee_new (col1 int, col2 varchar(10), col3 datetime)

Insert into employee_new (Col1, col2, col3)
Select * from employees
PankajSanwal
  • 956
  • 7
  • 14