CODEWITHSUNDEEP
sql-server
charlie_cat
charlie_cat

Reputation: 1850

Write the output of a select into a table

I have this statement:

 insert into Admin.VersionHistory --do not know what to put here
 select COUNT(*) as cnt
 from membership.members as mm 
 left join aspnet_membership as asp
 on mm.aspnetuserid=asp.userid 
 left join trade.tradesmen as tr 
 on tr.memberid=mm.memberid  
 where asp.isapproved = 0 and tr.ImportDPN IS NOT NULL and tr.importDPN <> ''

and it gives me a total of 179956. I want to write this total to another table called Admin.VersionHistory which has id(autoinc), version(varchar) and date(sysdate) columns,

How can I do this please? Thanks

Upvotes: 0

Views: 78

Answers (3)

RMN
RMN

Reputation: 752

INSERT INTO Admin.VersionHistory (ColCount, version, Sysdate)
VALUES(  

(SELECT COUNT(*) AS cnt  FROM membership.members AS mm     
lLEFT JOIN aspnet_membership AS asp ON mm.aspnetuserid = asp.userid   
LEFT JOIN trade.tradesmen AS tr ON tr.memberid=mm.memberid   
WHERE   
asp.isapproved = 0 AND   
tr.ImportDPN IS NOT NULL AND   
tr.importDPN <> ''),   
VERSIONVALUE, GETDATE())

Though I have tried similar thing in Oracle I guess it should not be very different in SQL.

Give it a shot.

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

You should be able to do this:

INSERT INTO Admin.VersionHistory(ColumnName) 
SELECT COUNT(*)
-- the resut of your query here

Upvotes: 0

Sasa Popovic
Sasa Popovic

Reputation: 53

Your query returns resultset with one column and one row. It could be inserted in some table that has one integer column. Your target table is not suitable for this because it has three columns.

Upvotes: 1

Related Questions