Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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;
share|improve this answer
    
You are awesome, will mark as answer as soon as allowed –  Overmind.900 Nov 27 '13 at 20:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.