Updating multiple columns in sql server 2016
JURISDICTION_FIELDALTER COLUMN [DESCRIPTION] NVARCHAR(2000) NULL; If I need to update 20 columns in a table, there will need to be 20 ALTER TABLE statements.Is there a way to update all 20 columns using a single ALTER TABLE statement? You can add multiple columns in a single command, but not alter multiple columns all at once.Here is code to build the table and add some sample data. Update By App3Date ) ud ) Last Update Date FROM ##Test Table tt As we can see the first two solutions have more compact code and therefore it is probably easier for developers to use, but what can be said about performance? Below we are creating a new table and adding a lot more test data into our table: --1 SELECT ID, (SELECT MAX(Last Update Date) FROM (VALUES (Update By App1Date),(Update By App2Date),(Update By App3Date)) AS Update Date(Last Update Date)) AS Last Update Date FROM ##Test Table --2 SELECT ID, MAX(Update Date) AS Last Update Date FROM ##Test Table UNPIVOT ( Update Date FOR Date Val IN ( Update By App1Date, Update By App2Date, Update By App3Date ) ) AS u GROUP BY ID, Name --3 SELECT ID, MAX(Update Date) AS Last Update Date FROM ( SELECT ID, Update By App1Date AS Update Date FROM ##Test Table UNION SELECT ID, Update By App2Date AS Update Date FROM ##Test Table UNION SELECT ID, Update By App3Date AS Update Date FROM ##Test Table ) ud GROUP BY ID --4 SELECT ID, ( SELECT MAX(Update Date) AS Last Update Date FROM ( SELECT tt. We can also see that each query used a totally different execution plan.
So here is the question – -- Adding a Multiple Column with Default Value ALTER TABLE Test Table ADD Multi Col1 VARCHAR(100) NOT NULL DEFAULT('Multi Col Val'), Multi Col2 INT NOT NULL DEFAULT(999) GO -- Retrieve Data from Table SELECT * FROM Test Table GOThis will accept the default value if the newly added columns are not nullable.
statement can be accomplished in one of two ways, primarily depending upon which version of SQL Server you are using.
We’ll briefly explore both options so you can find what works best for you.
Please let me know if you need more clarifications. Copy-and-paste can remove most of the work with that.
Might be even simpler if you're modifying a predictable set of columns, and you query sys.columns to build the basic strings.