CODEWITHSUNDEEP
sqlsql-server
mariocatch
mariocatch

Reputation: 8703

Alter table to add dynamic columns based off some previously selected query

Is it possible to alter a table and add columns with a dynamic name/data type based off some previously select query?

The pseudo equivalent for what I'm looking to do in SQL would be:

foreach row in tableA
{
  alter tableB add row.name row.datatype
}

This is for SQL Server.

Upvotes: 0

Views: 92

Answers (2)

Gert-Jan
Gert-Jan

Reputation: 347

As mentioned, you can do this with dynamic sql. Something along these lines:

Declare @SQL1  nvarchar(4000)
SELECT @SQL1=N'ALTER TABLE mytable'+NCHAR(13)+NCHAR(10)
+N' ADD COLUMN '+ my_new_column_name + ' varchar(25)'+NCHAR(13)+NCHAR(10)
-- SELECT LEN(@SQL1), @SQL1
EXECUTE (@SQL1)

Apart from the fact that this is messy, error prone, a security risk, requires high authorization to execute and needs multiple variables for batches bigger than 4000 characters, it is usually also a bad idea from a design point of view (depending on when/why you are doing this).

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31795

Sure, you can do this with dynamic sql.

Upvotes: 1

Related Questions