Sql-server – Alter table and Create 40+ Column in existing table in single script

sql server

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.

Best Answer

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.