Select Into with typed alias column

Question

I'm using SQL Server 2012 Express and I'm trying to run a command to create a new table from another

I'd like to do something like this

SELECT 
   [RowID], [Account], [Broker], [AlgorithmName], [AlgorithmId],
   [Time], [XMLValue], 'Status' AS [ReportType] AS [varchar](100) NOT NULL
INTO [dbo].[temp_AlgorithmLog]
FROM [dbo].[AlgorithmLog_OLD]

The alias column is the part that doesn't work. 'Status' AS [ReportType] AS [varchar](100) NOT NULL

I would like to use a SELECT INTO rather then CREATE TABLE and INSERT INTO SELECT

Any suggestions on how to do this if it can be done this way?

Thanks in advance

Answer 1

Not sure where you guessed at that syntax, or why you are trying to put single quotes around a column definition (which makes it look like a string literal). Here is one way to make sure that the new column has a specific type:

SELECT ...other columns... ,
  [Status] = CONVERT(VARCHAR(100), ReportType)
INTO dbo.temp_AlgorithmLog
FROM dbo.AlgorithmLog_OLD;

The new column will be nullable regardless of the old column's nullability. If the old column is nullable and you don't want the new column to be nullable, first you need to ensure that there are no NULL values, and then you can alter the table after the fact:

ALTER TABLE dbo.temp_AlgorithmLog
  ALTER COLUMN [Status] VARCHAR(100) NOT NULL;