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.
FieldName
? Are all the data types the same? – Dan Guzman Oct 12 '18 at 13:12