SQL Server Portal

“Can you add one column in the middle of the table via script, (if yes) how would you do it ?
The answer to this question is “No”. Either you need to add the column in the end of the table or you need to do it via SSMS.
But the question is why you can do it through Sql Server Management Studio (SSMS) not from the script. So Lets go through SSMS to find out how SSMS can do it.

Lets proceed it step by step :
Step -1
Lets Create a Sample table.

Create Table Test_Table
(
[Customer ID] int,
[Customer Name] varchar(50)
)

Step -2
Now, open the Table in a design mode.

Step -3
Add one column namely “Customer Code” in the middle of the table.

Step -4
Click on “generate change script” button and view the script.

Step -5
Now, we have change script.

In this script, SQL create a new temporary table insert all data in it and then drop the original table and then rename it to the original name.

/* To prevent any potential data loss issues, you should review this script in
detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Test_Table
(
[Customer ID] int NULL,
[Customer Code] int NULL,
[Customer Name] varchar(50) NULL
)  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Test_Table)
EXEC('INSERT INTO dbo.Tmp_Test_Table ([Customer ID], [Customer Name])
SELECT [Customer ID], [Customer Name] FROM dbo.Test_Table WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Test_Table
GO
EXECUTE sp_rename N'dbo.Tmp_Test_Table', N'Test_Table', 'OBJECT'
GO
COMMIT

Conclusion :
In the above script, you can see how SQL added column in the middle of the table, So you can also add the column in the middle of the table via script as well.