CODEWITHSUNDEEP
sqlsql-servert-sql
S Nash
S Nash

Reputation: 2499

Create a new table based on a query SQL Server

I have a query 

with x as 
(select row_number() over(partition by FirstName order by Investment_DT desc) as rn, *
from [dbSuppHousing].[dbo].[tblABC])
select Login_Name
      ,r.Role_Name
      ,Investment_DT
      ,FirstName     
      ,LastName
      ,Login_Name
      ,Investment_DT
      ,Investment_ID
from x join tblUsers t 
on t.UserName = x.Login_Name

join tblUser_Roles ur on t.User_Id=ur.USER_ID
join tblRoles r on r.Role_Id=ur.Role_ID
where x.rn = 1

order by x.FirstName

I want to insert the result of this query into another table as is.

Typically I use a query like:

insert  into tblABC2
select * from tblABC

But I'm not sure how to do this in this case which is a query which begins with with x as

Upvotes: 0

Views: 86

Answers (3)

SQLDiver
SQLDiver

Reputation: 2018

You simply need to put the insert statement between the common table expression (CTE) and your select statement:

with x as 
(select row_number() over(partition by FirstName order by Investment_DT desc) as rn, *
from [dbSuppHousing].[dbo].[tblABC])

/*
    place insert statement here
*/

select Login_Name
      ,r.Role_Name
      ,Investment_DT
      ,FirstName     
      ,LastName
      ,Login_Name
      ,Investment_DT
      ,Investment_ID
from x join tblUsers t 
on t.UserName = x.Login_Name

join tblUser_Roles ur on t.User_Id=ur.USER_ID
join tblRoles r on r.Role_Id=ur.Role_ID
where x.rn = 1

order by x.FirstName

If you are running this on SQL Server 2008+, you need to ensure that any statement previous to the CTE is terminated with a semicolon.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

with x as 
(select row_number() over(partition by FirstName order by Investment_DT desc) as rn, *
from [dbSuppHousing].[dbo].[tblABC])
select Login_Name
      ,r.Role_Name
      ,Investment_DT
      ,FirstName     
      ,LastName
      ,Login_Name
      ,Investment_DT
      ,Investment_ID
into #temptable
from x join tblUsers t 
on t.UserName = x.Login_Name
join tblUser_Roles ur on t.User_Id=ur.USER_ID
join tblRoles r on r.Role_Id=ur.Role_ID
where x.rn = 1
-- order by x.FirstName

You can use into to insert into the table you need. Also note that you can't do an order by when doing this (which has been commented out).

Upvotes: 1

oryol
oryol

Reputation: 5248

with x as (...) insert into tbl select * from x

So, insert should be directly after all CTEs used in the query.

Upvotes: 0

Related Questions