SQL Server avoid repeating the collation type multiple times in a query

You can once create a view using column definitions like this:

FirstName COLLATE SQL_Latin1_General_CI_AS, ...

Then anytime you'd like to use User tables, you can use this view instead.

Collations are per each string column / expression (XML datatype excluded). So in this sense, no, Collations cannot be set per Table or query.

I need to address the collation difference in my query by explicitly specifying the collation for varchar fields.

Um, why? You shouldn't need to deal with this for an INSERT statement. Collation conflicts arise either when comparing two columns, or combining two or more columns in a UNION. As you can see with the test below (just run it in a Database that is not tempdb), there are two tables, each in a different database, each having a different Collation -- one being a SQL Server Collation and one being a Windows Collation even -- and there is no error. If you are getting an error, please provide more details in the question.

--DROP TABLE #tmp;
CREATE TABLE #tmp (Col1 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS);
INSERT INTO #tmp ([Col1]) VALUES ('n');

-- DROP TABLE dbo.Tmp;
CREATE TABLE dbo.Tmp (Col1 VARCHAR(50) COLLATE Hebrew_100_CS_AI);

INSERT INTO dbo.Tmp ([Col1])
  SELECT t.[Col1]
  FROM #tmp t;