How to add a
table with a column in SQL Servers without deleting the inventory data?
Adding Single
Columns
|
ALTER TABLE dbo.tblTest ADD
NewColumn
int NULL
|
Description:
A new column is
very easy to create in sql server table >designer
In the SQL
Designer, after adding a column, click Generate Change script
In the dialog you
can copy the SQL script code ->
The line with
Alter Table xxx ADD NewColumn INT
Is crucial
Open the code in
the SQL script Editor and run it with F5
Result:
A new column was
added, but the old data was preserved
T-SQL script for
modifying or adding a column
|
/* 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
ALTER TABLE
dbo.tblTest ADD
NewColumn int NULL
GO
ALTER TABLE dbo.tblTest SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
|
SQL T-SQL script
to create the table
|
USE [Demo]
GO
/****** Object: Table [dbo].[tblProducts] script Date: 16.11.2021 10:41:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTest](
[IDTest]
[int] IDENTITY(1,1) NOT NULL,
[Test]
[nvarchar](50) NULL,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
IDTest
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON
[PRIMARY]
GO
|