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
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;