CODEWITHSUNDEEP
sqlsql-servert-sql
UserSN
UserSN

Reputation: 1013

SQL Server 2014: INSERT INTO values & subqueries

I'm trying to insert values into 1 table by entering the values and getting 1 from a sub query, i'm not sure how to combine the STATIC VALUES for SaleEventID, PoolName & CurrentUPB with the SELECT statment that gives me value for PoolID

I will scale this up to include more columns where I could need to include additional select sub-queries.

What is the proper format?

SELECT DISTINCT [PoolID] 
FROM TESTLOANS
WHERE SaleEventID = 0

Tried combining them like this but it's not working.

INSERT INTO TESTPOOLS (SaleEventID, PoolID, PoolName, CurrentUPB)
VALUES ('55', [POOLID], 'SouthernFL', '45,000')

SELECT DISTINCT [PoolID] 
FROM TESTLOANS
WHERE SaleEventID = 55

UPDATE: now to return the CurrentUPB value I can use

SELECT SUM(CurrentUPB) CurrentUPB 
FROM TESTLOANS
WHERE SaleEventID = 0

If I have a few more of these type of select statements that give me the values for some of my INSERTS, How do I add those into the main query?

Upvotes: 2

Views: 729

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use the select as-is and include the static values in the same statement.

INSERT INTO TESTPOOLS (SaleEventID, PoolID, PoolName, CurrentUPB)
SELECT DISTINCT '55', [POOLID], 'SouthernFL', '45,000'
FROM TESTLOANS
WHERE SaleEventID=0

Edit: Use window functions to get the counts or sums.

For eg:

INSERT INTO TESTPOOLS (SaleEventID, PoolID, PoolName, CurrentUPB,somecount)
SELECT DISTINCT '55', [POOLID], 'SouthernFL'
,sum(currentUPB) over(partition by poolid) --if you don't need the sum by poolid just use sum(currentUPB) over()
,count(*) over(partition by poolid) --if you don't need the count by poolid just use count(*) over()
FROM TESTLOANS
WHERE SaleEventID=0

Upvotes: 5

Related Questions