{"id":9445,"date":"2022-08-04T11:14:55","date_gmt":"2022-08-04T09:14:55","guid":{"rendered":"https:\/\/expert-only.com\/?page_id=9445"},"modified":"2022-08-30T10:45:31","modified_gmt":"2022-08-30T08:45:31","slug":"manage-sql-server-tables","status":"publish","type":"page","link":"https:\/\/expert-only.com\/en\/manage-sql-server-tables\/","title":{"rendered":"Manage SQL Server tables"},"content":{"rendered":"\n

The tutorials in this section explains how to create and manage SQL Server tables. And shows the syntax by example. These queries allow you to manage SQL Server tables, which are the core of Microsoft’s relational databases.<\/p>\n\n\n\n

In other words, how to create, modify, copy, dump and delete one or more tables. That is, how to manipulate these objects with T-SQL commands.<\/p>\n\n\n\n

Basic SQL Server<\/mark> examples for creating and managing tables<\/h2>\n\n\n\n

Here are some computer tutorials in French to manage tables stored in SQL Server, i.e. in the Microsoft DBMS. The first part explains the basic queries for management.<\/p>\n\n\n\n

The second part of the tutorials explains advanced concepts such as table partitioning for example.<\/p>\n\n\n\n

This example script uses the CREATE TABLE command to create a sales table. The script uses columns to store the time at month level, the employee number and the sales amounts.<\/p>\n\n\n\n

T-SQL example to create an MS SQL table<\/h3>\n\n\n\n

This table uses several data types to show a range of formats available with SQL Server<\/a>. However, it also makes sense to calculate amounts on the fly with the VAT value stored once in another table, for example with the year.<\/p>\n\n\n\n

First, how do you create a SQL Server table with the CREATE TABLE command?<\/p>\n\n\n\n

-- Test if the table already exists\n-- If it doaes then delete the table\nIF EXISTS(\n   SELECT 1 FROM sys.objects\n   WHERE object_id = object_id(N'[dbo].[SALES]')\n      AND type in (N'U')\n)\nDROP TABLE [dbo].[Sales]\nGO\n\n-- Create the example table to store SALES\nCREATE TABLE [dbo].[Sales] (\n   [MonthName] nvarchar(20),\n   [Amount] numeric(8)\n)\nGO\n<\/pre>\n\n\n
\n
\"Script
Script to manage SQL tables with a CREATE TABLE SQL Server script<\/figcaption><\/figure><\/div>\n\n\n
After the table creation, insert a few rows in the table to handle data for the first three months of the calendar year.<\/p>\n\n\n\n
-- Insert sales data for our example\nINSERT INTO dbo.Sales (MonthName, Amount) VALUES ( N'January', 1000);\nINSERT INTO dbo.Sales (MonthName, Amount) VALUES ( N'February', 2000);\nINSERT INTO dbo.Sales (MonthName, Amount) VALUES ( N'March', 3000);\nGO\n\n-- Select results to check table contents\nSELECT    *\nFROM    dbo.Sales;\n<\/pre>\n\n\n
\n
\"Script
Script to insert data into SQL Server tables with the INSERT INTO statement<\/figcaption><\/figure><\/div>\n\n\n
Script to modify a table (ALTER TABLE)<\/h3>\n\n\n\n
Next, how to modify a SQL Server table with ALTER<\/a> TABLE to add or remove a column for example. Or to change the size of a text field or the type of a column.<\/p>\n\n\n\n
First, to modify a table and add constraints to control the integrity of the data entered, use the ALTER TABLE and ADD CONSTRAINT commands.<\/p>\n\n\n\n
Add a constraint on the Sales table to check that the month name column is not null.<\/p>\n\n\n\n
ALTER TABLE [dbo][SALES] WITH CHECK\nADD CONSTRAINT Month_check CHECK (MonthName IS NOT NULL);\nGO<\/pre>\n\n\n\n
It is now possible to insert only non-zero values for the month column into the sales table.<\/p>\n\n\n\n
-- Insert three more rows in the sales table\nINSERT INTO [DBO].[SALES] VALUES ('March', 1000);\nINSERT INTO [DBO].[SALES] VALUES ('April', 2000);\nINSERT INTO [DBO].[SALES] VALUES ('May', 3000);\n\n-- This line will not be inserted because there is a null value for the month\nINSERT INTO [DBO].[SALES] VALUES (null, 5000);\n<\/pre>\n\n\n\n
If the month is null then this SQL Server error appears:<\/p>\n\n\n\n
The INSERT statement conflicted with the CHECK constraint “Month_check”.
The conflict occurred in database “Expert-Only”, table “dbo.SALES”, column ‘MonthName’.<\/em><\/p>\n\n\n\n
INSERT INTO query from a SELECT with SQL Server<\/h3>\n\n\n\n
To go further and insert rows from a selection query, use the Insert Into query directly from a SELECT<\/a> statement.<\/p>\n\n\n\n
INSERT INTO [dbo].[SALES] ([MonthName], [Amount])\nSELECT N'January', '1', 1000 UNION ALL\nSELECT N'February', '2', 2000 UNION ALL\nSELECT N'March', '3', 3000;\n<\/pre>\n\n\n\n
Adding a column to a SQL table (ALTER COLUMN)<\/h3>\n\n\n\n
Then, to evolve a table, adapt this example to change the length of the columns with an ALTER COLUMN script.<\/p>\n\n\n\n
-- Change the length of the month name from 20 to 100 characters\nALTER TABLE [dbo].[SALES].\nALTER COLUMN [MonthName] NVARCHAR(100);\nGO<\/pre>\n\n\n\n
Deleting data from a SQL table with Microsoft (DELETE FROM)<\/h3>\n\n\n\n
Third, to delete data with a filter, use this script and adapt it to the needs of the project. This script deletes the data for the first three months, January, February and March.<\/p>\n\n\n\n
DELETE FROM [DBO][SALES]\nWHERE MonthName in ('January', 'February', 'March');\n<\/pre>\n\n\n\n
Empty all data from an MSSQL table (TRUNCATE TABLE)<\/h3>\n\n\n\n
Furthermore, before completely deleting a table and thus the contents and structure, it is possible to empty the contents of the MS SQL table with the TRUNCATE TABLE<\/a> command.<\/p>\n\n\n\n
To clear all data from a SQL Server table without any filters, use the TRUNCATE TABLE script.<\/p>\n\n\n\n
TRUNCATE TABLE [dbo].[SALES];\n<\/pre>\n\n\n\n
Check if a table exists and delete it (IF EXISTS and DROP TABLE)<\/h3>\n\n\n\n
It is possible to test if a SQL Server table exists before deleting it<\/a> to avoid errors like “Can’t find the object because it doesn’t exist or you don’t have the necessary permissions<\/a>“?<\/p>\n\n\n\n
IF EXISTS (\n\tSELECT 1 FROM sys.objects\n\tWHERE object_id = object_id(N'[dbo].[SALES]')\n\tAND type in (N'U') )\nBEGIN\n\tDROP TABLE [dbo].[SALES]\nEND;\nGO\n<\/pre>\n\n\n\n
Delete a SQL Server table (DROP TABLE)<\/h3>\n\n\n\n
To completely delete a SQL Server table, use the T-SQL DROP TABLE command. This means that the structure and contents are deleted. Note that this action is irreversible and that it is therefore necessary to save the code of the table beforehand.<\/p>\n\n\n\n
Use this T-SQL command to delete a SQL Server table, i.e. the contents of the table and its structure.<\/p>\n\n\n\n
DROP TABLE [dbo].[SALES];\nGO<\/pre>\n\n\n\n
\n
Check if table exists in SQL Server<\/a><\/blockquote>