Insert query with a column that is the total number of records from another table

I have a query that inserts data from another table, into an empty table (the table is cleared once a day, and new data is put in once a day). Currently it looks like this:

INSERT INTO new 
       (website, 
       company, 
       address, 
       city, 
       state, 
       zip
SELECT f.website, 
       f.business, 
       f.[address line 1], 
       f.city, 
       f.state, 
       f.[zip code]
FROM   [finaltable] f;

What I would like to do, is include a row called “COUNT” on the insert, that would be total number of records of the table “finaltable”.

Something like:

INSERT INTO new 
       (website, 
       company, 
       address, 
       city, 
       state, 
       zip,
       count
SELECT f.website, 
       f.business, 
       f.[address line 1], 
       f.city, 
       f.state, 
       f.[zip code],
       (SELECT COUNT(*) FROM finaltable) AS COUNT
FROM   [finaltable] f;

But I have no idea how I would go about that, I’m not super familiar with subqueries like this.

Answer

One way is to use windowed COUNT:

INSERT INTO new 
       (website, 
       company, 
       address, 
       city, 
       state, 
       zip,
       [count])
SELECT f.website, 
       f.business, 
       f.[address line 1], 
       f.city, 
       f.state, 
       f.[zip code],
       [count] = COUNT(*) OVER()  -- count of rows returned by `SELECT`
FROM   [finaltable] f;

db<>fiddle demo