Why can't you do it this way? I am not sure why you are using variables in the select statement:
ALTER PROCEDURE [dbo].[sp_CopyFileScorerChanges]
-- Add the parameters for the stored procedure here
@SourceTable NVARCHAR(100),
@TargetTable NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @zSQL NVARCHAR(4000)
SET @zSQL = 'INSERT INTO ' + @TargetTable + '(
[FilePath],
[CurrentCategory],
[CurrentSubCategory],
[CurrentBpm],
[OldCategory],
[OldSubCategory],
[OldBpm]
)
SELECT
[FilePath],
[CurrentCategory],
[CurrentSubCategory],
[CurrentBpm],
[OldCategory],
[OldSubCategory],
[OldBpm]
FROM ' + @SourceTable +
'WHERE CurrentCategory OldCategory'
EXEC (@zSQL)
END