Data Manipulation Language Commands in SQL Server

Insert:

Appends a new record to the end of an existing table. The new record contains data listed in the INSERT command.

Syntax:

INSERT [INTO] TABLE [table name] VALUES ([values][,….n])
INSERT [INTO] TABLE [table name]([col name][,….n]) VALUES ([values][,..n])

Example:

INSERT INTO TABLE emp VALUES(1,'DBA')
INSERT INTO TABLE emp (empname,empno) VALUES ('DBA',1)

Update:

Update table changes the data in the existing table. It is used to modify one or more records in a table. Use WHERE clause to limit which records are updated.

Syntax:

UPDATE TABLE [table name] SET [colname]=[values][,….n] WHERE [col name]=[value]

Example:

UPDATE TABLE emp SET empname='ABCD' WHERE empno=1

Delete:

Marks records in a table for deletion using SQL syntax. Deletes one or more records from a table. If where clause is not given then all the records in the table will be deleted. If you want to delete a specific row then specify in the WHERE clause.

Syntax:

DELETE [TOP [expression]|[PERCENT]]FROM [table name] [WHERE [condn]]

Example:

DELETE FROM emp WHERE empno=2

Select:

Specifies the criteria on which a query is based and issues the query. Retrieves the rows from the database and enables the selection of one or more rows or columns from one or many tables.

Syntax:

SELECT [select_list] [INTO [new table name]] FROM [table source] [WHERE [condn] [GROUP BY [group_by_exp]] [HAVING [search condn]] [ORDER BY [order exp] ASC|DESC]

Example:

SELECT * INTO emp1 FROM emp
SELECT TOP 10 FROM emp
SELECT * from emp WHERE empno=1
SELECT * FROM emp ORDER by empno
    • Below are the differences between Delete and Truncate Statements:
Delete Truncate
Record Level Operation or Row Level Operation ie. we can use WHERE clause in the delete statement. Table Level Operation. We cannot use the WHERE Clause in the truncate statement. When this statement is used all the records in the table are deleted.
When a Delete statement is used Delete Trigger is fired. When a Truncate statement is used no Trigger is fired.
Identity Column will not be reset. Identity Column in reset.
Logged Operation (the statement that is executed is stored in the Log File). Non Logged or Semi-Logged Operation (Only the information of the deallocated pages is stored in the log file).
Delete is a DML Command. Truncate is a DDL Command.
We can execute a DELETE statement for a table, even though the table is referenced by a Foreign Key Constraint We cannot execute a Truncate statement for a table, if the table is referenced by a Foreign Key Constraint.

Below are the different options available with Sysobjects table. We can find this table in every database in SQL Server:

--Displays all the objects in the corresponding database
SELECT * FROM sysobjects

--Displays all the user defined objects in the corresponding database.
SELECT * FROM sysobjects WHERE xtype=’u’

--Displays all the views in the corresponding database.
SELECT * FROM sysobjects WHERE xtype=’v’

--Displays all the system tables in the corresponding database.
SELECT * FROM sysobjects WHERE xtype=’s’

--Displays all the primary key columns in the corresponding database.
SELECT * FROM sysobjects WHERE xtype=’pk’

--Displays all the foreign keys in the corresponding database.
SELECT * FROM sysobjects WHERE xtype=’f’

--Displays all the user default in the corresponding database.
SELECT * FROM sysobjects WHERE xtype=’df’

--Displays all the procedures in the corresponding database.
SELECT * FROM sysobjects WHERE xtype=’p’

HELP:

--Displays entire information of the table.
SP_HELP [tablename]
SP_HELP emp.

--Displays entire information of the database.
SP_HELPDB [database name]
SP_HELPDB rough1

--Displays the created jobs. All the jobs created in msdb database.
SP_HELP_JOB @JOB_NAME=N’[job name]’
EXEC SP_HELP_JOB @JOB_NAME=N'create1'.

--Displays the constraint of the corresponding database.
SP_HELPCONSTRAINT
SP_HELPCONSTRAINT emp
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: