About us |  Help us improve
intelliVERB - Get a fair search of the Internet !
Visual studio .net
SQL Server
Oracle
Main  >  DML Commands Email this page to friend
DELETE
FROM Clause
INSERT
Sample table structure
SELECT
SELECT FOR XML
UPDATE
 
 
 
 
 
TIPTop
 To refer to a Datasource using a linked server name
linkedservename.catalog.schema.objectname
Myserver.EmployeeDB.dbo.EmployeeTable

SqlSample table structureTop

--EMPLOYEE TABLE

CREATE TABLE [Employee] (
 [Empl_Id] [int] NOT NULL ,
 [First_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Last_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [DOB] [datetime] NULL ,
 CONSTRAINT [PK_Employee] PRIMARY KEY  CLUSTERED
 (
  [Empl_Id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

--MANAGER TABLE

CREATE TABLE [Manager] (
 [Mgr_Id] [int] IDENTITY (1, 1) NOT NULL ,
 [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [First_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Last_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [DOB] [datetime] NULL ,
 CONSTRAINT [PK_Manager] PRIMARY KEY  CLUSTERED
 (
  [Mgr_Id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO


SqlINSERTTop

--INSERT...INTO

--INSERT...SELECT
INSERT INTO MANAGER
 SELECT 'OFFICE MANAGER', FIRST_NAME, LAST_NAME, DOB
 FROM EMPLOYEE WHERE EMPL_ID=0457124


TIPTop
The view is expanded before the query optimizer processes the query. View hints are allowed only in SELECT statements, and cannot be used in UPDATE, DELETE, and INSERT statements

SqlFROM ClauseTop

--table_source (table, view)
SELECT * FROM myServer.EmployeeDB.dbo.EmployeeTable (NOLOCK)

--with
SELECT *
FROM OPENXML(@XHandle,'/Employees/Employee',1)
WITH ( Empl_Id int'@Empl_Id',
       Fist_Name varchar(50) '@Fist_Name',
       Last_Name varchar(50) '@Last_Name',
       DOB datetime '@DOB')

-- rowset_function
SELECT *
FROM OPENROWSET('SQLOLEDB','myServer';'myUserId';'MyPWD',
   'SELECT * FROM EmployeeDB.dbo.Employee')

-- user_defined_function
SELECT * FROM ::getEmployeeList

-- joined_table
SELECT * FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.Dept_Id=D.Dept_Id


SqlSELECTTop

--SELECT FROM XML

create procedure ep_Insert_Employee_XML(@XmlDoc text) as

declare @XHandle int
exec sp_xml_preparedocument @XHandle output,@XmlDoc

INSERT INTO EMPLOYEE
SELECT *
FROM OPENXML(@XHandle,'/Employees/Employee',1)
WITH ( Empl_Id int'@Empl_Id',
       Fist_Name varchar(50) '@Fist_Name',
       Last_Name varchar(50) '@Last_Name',
       DOB datetime '@DOB')

exec sp_xml_removedocument @XHandle

-- run
exec ep_Insert_Employee_XML  
'<Employees>
<Employee Empl_Id="451201" Fist_Name="Elena" Last_Name="Pazolla" DOB="03/04/1970" />
<Employee Empl_Id="421410" Fist_Name="Janet" Last_Name="Martin" DOB="07/09/1972" />
</Employees>'
--Results: (2 row(s) affected)


SqlDELETETop

--WHERE
DELETE FROM EMPLOYEE WHERE Empl_Id=451275

--WHERE CURRENT OF
DECLARE Empl_Cursor CURSOR FAST_FORWARD FOR
   SELECT First_Name, Last_Name, Active FROM  Employee
OPEN Empl_Cursor
 
FETCH Empl_Cursor into  @First_Name,  
   @Last_Name,@Active
 
 
While @@Fetch_Status = 0  
BEGIN
       --Delete all inactive employee
      IF @Active =0
         DELETE FROM Employee WHERE CURRENT OF Empl_Cursor
END


SqlUPDATETop

To be implemented


SqlSELECT FOR XMLTop

--XML EXPLICIT
SELECT 1 AS TAG,
 NULL AS PARENT,
 Empl_Id AS [Employee!1!EmplId],
 First_Name AS [Employee!1!FirstName!element],
 Last_Name AS [Employee!1!LastName!element],
 DOB AS [Employee!1!DOB!element]
FROM EMPLOYEE
FOR XML EXPLICIT

--result
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-------------------------------------------------------------------
<Employee EmplId="123456">
 <FirstName>Milton</FirstName>
 <LastName>Gomez</LastName>
 <DOB>1978-01-01T00:00:00</DOB>
</Employee>
<Employee EmplId="125454">
 <FirstName>Jamila</FirstName>
 <LastName>Sharid</LastName>
 <DOB>1970-03-06T00:00:00</DOB>
</Employee>
<Employee EmplId="451200">
 <FirstName>Irene</FirstName>
 <LastName>Johnson</LastName>
 <DOB>1972-12-04T00:00:00</DOB>
</Employee>
<Employee EmplId="574122">
 <FirstName>Ronald</FirstName>
 <LastName>Briggs</LastName>
 <DOB>1971-04-07T00:00:00</DOB>
</Employee>

(4 row(s) affected)

--XML RAW
SELECT * FROM EMPLOYEE
FOR XML RAW

--result
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------
<row Empl_Id="123456" First_Name="Milton" Last_Name="Gomez"
DOB="1978-01-01T00:00:00"/>
<row Empl_Id="125454" First_Name="Jamila" Last_Name="Sharid"
DOB="1970-03-06T00:00:00"/>
<row Empl_Id="451200" First_Name="Irene" Last_Name="Johnson"
DOB="1972-12-04T00:00:00"/>
<row Empl_Id="574122" First_Name="Ronald" Last_Name="Briggs"
DOB="1971-04-07T00:00:00"/>

(4 row(s) affected)

 
 
Make a secure donation now with PayPal!