Column has invalid width: 0 and default column type with SELECT INTO #TMP

We had an update statement that we needed to break out the select part and use that to insert into a temporary table so we could do some calculations to populate a certain field. Then we used the temporary table in the original update statement to populate the actual data. We came across a couple of problem where we were putting data into a temporary table to be used later.  The column we are going to calculate we wanted to blank out so we could populate it with our new calculation queries.

For the following SQL Server will assume that the column type with be "int" which was the first problem since the data we are calculating and inserting into the field is varchar:

SELECT
 null as sales_territory
INTO #tmp
FROM salesreps

When working on this problem we came across another error message "Column '' has invalid width: 0" when we change the code to what is below.  Looks likeSQL errors since it doesn't know how large the varchar column should be and tries to assign 0 since '' has zero characters.

SELECT
 '' as sales_territory
INTO #tmp
FROM salesreps

To correct the two issues above we used the following:

SELECT
CONVERT(varchar(10),null) as sales_territory
INTO #tmp
FROM salesreps

Then we calculate the new sales_territory information and use the temporary table in the update statement.