On an existing table, I want to add 47 columns using alter table script

select Distinct FieldName From customFields

Above query will list 47 rows and these row names I need to create in another table (table name is Equipment) as Column.

I am using MS SQL 2017. Please help.

  • Do you mean create another table dynamically with column names based on the values of FieldName? Are all the data types the same? – Dan Guzman Oct 12 '18 at 13:12

Assuming you are storing the name of these columns in this table customFields and you want them all to be int:

DECLARE @cmd nvarchar(max) = N'ALTER TABLE dbo.Equipment ADD $ int;',
        @sql nvarchar(max) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + REPLACE(@cmd, N'$', QUOTENAME(FieldName))
  FROM dbo.customFields
  GROUP BY FieldName;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Note that printing the output to check it might not show the entire script because SSMS limits its output by default. For workarounds see this tip.

If what you actually mean is you want to create an exact copy of customFields, you can do that much simpler:

SELECT * INTO dbo.Equipment FROM dbo.customFields;

There are some exceptions, like if you have computed columns, don't want the IDENTITY property copied, etc. but this should cover a lot of cases. If you have a particular use case, please make your question more specific.


If they have different data types, you can build it from INFORMATION_SCHEMA.COLUMNS

Something like the following (bare bones, edit as needed for your needs and data types)

SELECT 'ALTER TABLE Equipment ADD ' + column_name + ' ' + 
       CASE WHEN data_type IN ('varchar', 'char') THEN data_type + '(' + CONVERT(VARCHAR(10), character_maximum_length) + ') '
            ELSE data_type END AS [new_field]
WHERE table_name = 'customFields'
ORDER BY ordinal_position
  • 1
    I think the names of the new columns are stored as values in the column FieldName, not as columns in the table. – Aaron Bertrand Oct 12 '18 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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