Alter table and Create 40+ Column in existing table in single script

Question

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.

Answer 1

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.

Answer 2

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]
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'customFields'
ORDER BY ordinal_position