Wednesday, September 14, 2011

Save SQL Query results into another SQL database

Today I had a requirement to save the results of an SQL query into a sql table. Usually it is easy to export to excel by using the option of copying the results and pasting them to an excel file.

But this time I had over a million rows and I didnot want to use excel to store that huge amount of data. So I followed the following 2 simple steps.

1. Create a new table in a new database:

Use the SSMS to create the database and use the create table query to create the new table as follows:

create table databasename.dbo.tablename

([col1] [varchar(20)], [col2] [int], [col3] [datetime])

2. Create a SQL query using the Insert into command as follows:

Insert into databasename.dbo.tablename

(col1, col2, col3)

select a.col1, b.col2, c.col3 from tab1 a, tab2 b, tab3 c

where a.col1=b.col1 and b.col2=c.col3 and col1='zzz'

This query has inserted data into the new table directly.

1 comment:

Welcome to my site said...

Nice article. Please do pass by my new SQL Geek site--its a bit wild!
ashane
http://www.thesqlgeek.blogspot.com/

Rerunning a SSRS subscription report

Yesterday I had a situation where  a couple of SSRS reports failed due to a network upgrade. The SSRS report was not able to access the SQ...