If target table contains only one row, I presume that each of queries you posted also contains only one row. Because, if they don't, how will you know which row to pick?

Anyway: one option is to use your current queries as CTEs (common table expressions; a new way to use subqueries), cross join them and fetch columns you're interested in.

For example:

with 
current_day as(
select Name, count(cast (id as int))  as current_day from (
    select Name,id from [Table1]
    where CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP)
    union all
    select Name,id from from [Table2]
    where CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP)
) x group by Name),
current_week as(

select Name, count(cast (id as int))  as current_week from (
    select Name,id from [Table1]
    where DATEDIFF(ww, dte_Uploaded, GETDATE()) = 0
    union all
    select Name,id from from [Table2]
    where DATEDIFF(ww, dte_Uploaded, GETDATE()) = 0
) x group by  Name),
current month as
(
select Name, count(cast (id as int))   as current_month from (
    select Name,id from [Table1]
    where DATEDIFF(m, dte_Uploaded, GETDATE()) = 0
    union all
    select Name,id from from [Table2]
    where DATEDIFF(m, dte_Uploaded, GETDATE()) = 0
) x group by  Name)
select a.name, a.current_day, b.current_week, c.current_month
from current_day a cross join current_week b cross join current_month c;

If cross join won't work, then you should know how to properly join results returned by each CTE.

Finally, how to insert values into the target table? Like this:

insert into target (name, current_day, current_week, current_month)
with current_day as (the whole query posted above goes here)
select a.name, a.current_day, b.current_week, c.current_month
from current_day a cross join current_week b cross join current_month c

You might have better performance and improved readability if you calculate proper date ranges in advance and use conditional aggregation to calculate your counts.

When calculating date ranges, it is common to calculate an inclusive start date and an exclusive end date (00:00:00 at the start of the day afterwards). This nicely handles date/time fields without having to truncate potentially indexed column values.

Conditional aggregation combines an aggregate function (in this case COUNT() ) with a CASE expression that either includes or excludes each value from the aggregation. For COUNT() the THEN 1 rows are counted, while the implicit ELSE NULL rows are not.

Try something like:

-- Day, week, and month ranges. (End dates are exclusive.)
DECLARE @today      DATE = CONVERT(DATE, GETDATE())
DECLARE @day_from   DATE = @today
DECLARE @day_to     DATE = DATEADD(day, 1, @day_from)
DECLARE @week_from  DATE = DATETRUNC(week, @today)
DECLARE @week_to    DATE = DATEADD(week, 1, @week_from)
DECLARE @month_from DATE = DATETRUNC(month, @today)
DECLARE @month_to   DATE = DATEADD(month, 1, @month_from)

-- Overall range (to limit initial data retrieval)
DECLARE @range_from DATE = LEAST(@week_from, @month_from)
DECLARE @range_to   DATE = GREATEST(@week_to, @month_to)

SELECT
    Name,
    COUNT(CASE WHEN dt >= @day_from   AND dt < @day_to   THEN 1 END) AS current_day,
    COUNT(CASE WHEN dt >= @week_from  AND dt < @week_to  THEN 1 END) AS current_week,
    COUNT(CASE WHEN dt >= @month_from AND dt < @month_to THEN 1 END) AS current_month
FROM (
    SELECT Name, dte_Uploaded AS dt
    FROM Table1
    WHERE dte_Uploaded >= @range_from AND dte_Uploaded < @range_to
    UNION ALL
    SELECT Name, dte_Uploaded AS dt
    FROM Table2
    WHERE dte_Uploaded >= @range_from AND dte_Uploaded < @range_to
) X
GROUP BY Name

If dte_Uploaded is indexed, the above will efficiently retrieve only those rows needed for the current calculation, rather than a less-efficient table scan.

If you are using older versions of SQL server that do not support the DATETRUNC() , LEAST() , and GREATEST() functions, the following alternate date calculations can be used:

-- Day, week, and month ranges. (End dates are exclusive.)
DECLARE @today      DATE = CONVERT(DATE, GETDATE())
DECLARE @day_from   DATE = @today
DECLARE @day_to     DATE = DATEADD(day, 1, @today)
DECLARE @week_from  DATE = DATEADD(day, 1 - DATEPART(weekday, @today), @today)
DECLARE @week_to    DATE = DATEADD(week, 1, @week_from)
DECLARE @month_from DATE = DATEADD(day, 1 - DAY(@today), @today)
DECLARE @month_to   DATE = DATEADD(month, 1, @month_from)

-- Overall range (to limit initial data retrieval)
DECLARE @range_from DATE = CASE WHEN @week_from < @month_from THEN @week_from ELSE @month_from END
DECLARE @range_to   DATE = CASE WHEN @week_to > @month_to THEN @week_to ELSE @month_to END

Depending how your site defines the start of a week, the week ranges might need to be tweaked.