Many developers come up with a common question of whether a
workaround can be made on changing the column structure. The straight answer to
them is a “No” for an immediate fix. However, there are methods to rearrange a newly
inserted column for an already existing table.
Probably in big organizations, there would be a requirement
to create a new column for an already existing table. This new column could be as
per the requirement linked to the applications created by the organization. In
general terms, a new column added for an application in the middle of the
table obviously makes no sense and is considered unethical for table modification.
However, in case the situation is too worse to just proceed and implement a new
column to the existing table, then here are some workaround.
Method 1: Drop and Recreate Table If No Data Available in
the Table
Chances are high that a table would have been created and
flushed with data and truncated. In such cases, if you find no data in the
table where you need to add a new column then just drop the table and recreate
with the new column added in the table structure.
Warning: Please do not drop the table in case you find data
loaded in it.
Method 2: Create a View for the Table
The simplest form is to create a view for the base table in whichever order you desire.
Method 3: Use GUI by Using Design
1. Expand the database where the table is located.
2. Right-click on “Tables” and select “Filter” to
select the desired table.
3. Right-click on your table and click “Design”.
4. Here, you will be able to reorder the table
structure based on your requirement.
Method 4: Create a New Table and Rename the Table by
Dropping Old One
Create a new table say with “_1” with whichever table order
of your choice. Once the table is created, import the entire data present in
your table to a newly created “_1” table. After transferring the data to a new
table, Rename the table with “exec sp_rename 'schema.old_table_name',
'new_table_name' “ command. This can produce a warning message, but nothing to
worry about.
Note: Please do not use this method for critical tables.
0 comments:
Post a Comment