Wikibooks: Microsoft SQL Server/Table manipulation

• Format: Book
• Language: English
• Subjects: DML
• Online Access: https://en.wikibooks.org/wiki/Microsoft_SQL_Server/Table_manipulation

= Introduction = The wt DDL and wt DML respect the [[SQL 86]] norm. However in addition to the requests SELECT UPDATE INSERT we find MERGE since the 2008 version https //msdn.microsoft.com/en us/library/bb510625.aspx . = Create a table = In SSMS a right click on the folder Tables of a database allows to add one. A right click on a particular table lets choose between Modify the table structure (add a column modify a type). Select its 1 000 first records ( TOP ) or the 1 000 last ( ORDER BY id DESC ). Edit its 200 first. Otherwise in SQL one must enter https //msdn.microsoft.com/en us/library/ms174979.aspx CREATE TABLE [dbo].[table1] ( [Nom] [varchar](250) NULL [Prénom] [varchar](250) NULL [identifiant] [int] IDENTITY(1 1) NOT NULL) Filling the first columns https //msdn.microsoft.com/en us/library/ms174335.aspx INSERT INTO table1 VALUES ( Doe Jane 1) ( Doe John 2) To aim some other columns the fields must be precised. For example by filling the first name the last name will be null INSERT INTO table1 (First name id) VALUES ( Jane 3) From another table INSERT INTO table1 (First name id) SELECT First name ID FROM table2 Update UPDATE table1 SET First name = Janet WHERE ID = 3 UPDATE table1 SET First name = t2.First name Last name = t2.Last name FROM table1 t1 INNER JOIN table2 t2 on t1.ID = t2.ID t1 = Create an index = The software PK abbreviation means wt primary key . To create a wt foreign key drop down the table in the menu Keys right click new foreign key . the list of all the table foreign keys appears in a small window (named by default FK . for foreign key ). In General Tables and columns specification click on . to select the table and its field to link. = Add a unique id = Normally each table should own at least one unique id (primary key). However it s impossible to modify an existing column to attribute the property AUTOINCREMENT needed to such a key. So to add one ALTER TABLE table1 ADD id int NOT NULL IDENTITY (1 1) PRIMARY KEY = Copy a table = The selection below clones a table with the same fields ...