SQL Server - how to check if the records are already exists or not in that table

Asked By rama chandran on 22-Sep-11 12:20 AM
Hi,

i wrote a trigger, there i want i check if the same records are exists or not in that destination table. if not exists means i want to insert, otherwise dont insert.
Web Star replied to rama chandran on 22-Sep-11 12:30 AM
YOu just check existance of those record in query before insert data
If Not Exists(Select * from destinationtable Where id = 12)
Begin
Insert into destinationtable  Values('12','amit')
End
smr replied to rama chandran on 22-Sep-11 12:35 AM
HI

You should write a stored procedure for this

see this

IF EXISTS(SELECT FROM TBL_Subscribers WHERE email = @adres)
BEGIN
--emaild exist, so write code accordingly
END
ELSE
BEGIN
--emaild does not exist, so write code accordingly
END


refer
http://forums.asp.net/t/1578461.aspx/1?How+can+i+check+if+record+already+exist+in+sql+server+database
aneesa replied to rama chandran on 22-Sep-11 12:45 AM

01.CREATE TRIGGER [dbo].[TO_DESTINSATION_TABLE]
02.   ON  [dbo].[SOURCE_TABLE]
03.   AFTER INSERT
04.AS
05.BEGIN
06.  -- SET NOCOUNT ON added to prevent extra result sets from
07.  -- interfering with SELECT statements.
08.  SET NOCOUNT ON;
09.   DECLARE @SUBJECTID INT
10.   SELECT @SUBJECTID=SUBJECTID FROM inserted
11.   DECLARE @SUBJECTNAME VARCHAR(50)
12.   SELECT @SUBJECTNAME=SUBJECTNAME FROM inserted
13.   DECLARE @SHORTNAME VARCHAR(50)
14.   SELECT @SHORTNAME=SHORTNAME FROM inserted
15.   DECLARE @SHORTCODE VARCHAR(50)
16.   SELECT @SHORTCODE=SHORTCODE FROM inserted
17.  IF NOT EXISTS (SELECT * FROM  DESTINSATION_TABLE WHERE SUBJECTID=@SUBJECTID AND SUBJECTNAME=@SUBJECTNAME AND SHORTNAME=@SHORTNAME AND SHORTCODE=@SHORTCODE)
18.  BEGIN
19.  INSERT INTO  DESTINSATION_TABLE (SUBJECTID,SUBJECTNAME,SHORTNAME,SHORTCODE)VALUES(@SUBJECTID,@SUBJECTNAME,@SHORTNAME,@SHORTCODE)
20.  END
21.   
22.END

Suchit shah replied to rama chandran on 22-Sep-11 01:30 AM
USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
WHERE EXISTS
(SELECT *
    FROM HumanResources.Department AS d
    JOIN HumanResources.EmployeeDepartmentHistory AS edh
     ON d.DepartmentID = edh.DepartmentID
    WHERE e.BusinessEntityID = edh.BusinessEntityID
    AND d.Name LIKE 'P%');
GO

Also refer below link which will u to understand ur concept

http://www.techrepublic.com/article/put-sql-servers-exists-construct-to-good-use/6180272
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78926
http://www.techrepublic.com/article/put-sql-servers-exists-construct-to-good-use/6180272
http://stackoverflow.com/questions/3189293/check-if-record-exists-update-if-not-insert-stored-procedure
http://stackoverflow.com/questions/167576/sql-server-check-if-table-exists
http://www.digitalcoding.com/Code-Snippets/VB/Visual-Basic-Code-Snippet-Check-Record-Exists-in-SQL-Server-Database.html
http://www.techonthenet.com/sql/exists.php
Suchit shah replied to rama chandran on 22-Sep-11 01:31 AM
USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
WHERE EXISTS
(SELECT *
    FROM HumanResources.Department AS d
    JOIN HumanResources.EmployeeDepartmentHistory AS edh
     ON d.DepartmentID = edh.DepartmentID
    WHERE e.BusinessEntityID = edh.BusinessEntityID
    AND d.Name LIKE 'P%');
GO

Also refer below link which will u to understand ur concept

http://www.techrepublic.com/article/put-sql-servers-exists-construct-to-good-use/6180272
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78926
http://www.techrepublic.com/article/put-sql-servers-exists-construct-to-good-use/6180272
http://stackoverflow.com/questions/3189293/check-if-record-exists-update-if-not-insert-stored-procedure
http://stackoverflow.com/questions/167576/sql-server-check-if-table-exists
http://www.digitalcoding.com/Code-Snippets/VB/Visual-Basic-Code-Snippet-Check-Record-Exists-in-SQL-Server-Database.html
http://www.techonthenet.com/sql/exists.php
rama chandran replied to smr on 22-Sep-11 03:23 AM
no i want to check in trigger
Asked By rama chandran on 22-Sep-11 03:34 AM
i tried but error is coming, this is my trigger

create trigger test on tblFromtest
after insert
as
begin
insert into tbltest(empid,empname,salary,dob) 
select * from tblFromtest
truncate table tblFromtest
end

inside this trigger i want to check, if the tbltest doesnt having the same values, then only the values should insert, otherwise it should not insert. how to check.
dipa ahuja replied to rama chandran on 22-Sep-11 03:59 AM
Untitled document
ALTER PROCEDURE dbo.spWithReturnValue    
    (
    @Email varchar(50),
    @exists int=1 output
    )
    
AS
    /* SET NOCOUNT ON */    
 
     IF EXISTS (SELECT Email FROM users WHERE Email = @Email)  
     select @exists       
       
    ELSE
      set @exists=0
      select @exists        
RETURN
 
 
Use in .CS Code:
 
protected void btnCheck_Click(object sender, EventArgs e)
{
   string email = TextBox1.Text;
   int exist = 0;
   string ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
   SqlConnection conn = new SqlConnection(ConnString);
 
   conn.Open();
   SqlCommand comm = new SqlCommand("spWithReturnValue", conn);
   comm.Parameters.AddWithValue("Email", email);
   comm.CommandType = CommandType.StoredProcedure;
 
   //returning the output
 
   exist = int.Parse(comm.ExecuteScalar().ToString());
   if (exist == 1)
   {
     LblResult.Text = "<font color='green'>Email Exist in table</font>";
   }
   else
   {
     LblResult.Text = "<font color='red'>Email Not Exist</font>";      
  }
}
smr replied to rama chandran on 22-Sep-11 04:41 AM
hi

A trigger is a named program unit that is stored in the database and fired (executed) in response to a specified event. The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:

  • A database manipulation (DML) statement (DELETEINSERT, or UPDATE)

  • A database definition (DDL) statement (CREATEALTER, or DROP)

  • A database operation (SERVERERRORLOGONLOGOFFSTARTUP, or SHUTDOWN)

The trigger is said to be defined on the table, view, schema, or database.

Syntax:



                                   .-NO CASCADE-.             
>>-CREATE TRIGGER--trigger-name--+-+------------+--BEFORE-+----->
                                 +-AFTER------------------+   
                                 '-INSTEAD OF-------------'   

>--+-INSERT--------------------------+--ON--+-table-name-+------>
   +-DELETE--------------------------+      '-view-name--'   
   '-UPDATE--+---------------------+-'                       
             |     .-,-----------. |                         
             |     V             | |                         
             '-OF----column-name-+-'                         

>--+----------------------------------------------------------------+-->
   |              .-----------------------------------------------. |   
   |              V  (1)   (2)         .-AS-.                     | |   
   '-REFERENCING----------------+-OLD--+----+--correlation-name-+-+-'   
                                |      .-AS-.                   |       
                                +-NEW--+----+--correlation-name-+       
                                |            .-AS-.             |       
                                +-OLD TABLE--+----+--identifier-+       
                                |            .-AS-.             |       
                                '-NEW TABLE--+----+--identifier-'       

>--+-FOR EACH ROW-------------+--| triggered-action |----------><
   |  (3)                     |                         
   '-------FOR EACH STATEMENT-'                         

triggered-action

|--+------------------------------------+----------------------->
   |  (4)                               |   
   '-------WHEN--(--search-condition--)-'   

>--+--------+--| SQL-procedure-statement |----------------------|
   '-label:-'                                

SQL-procedure-statement

|--+-CALL----------------------------------------------+--------|
   +-Compound SQL (Dynamic)----------------------------+   
   +-FOR-----------------------------------------------+   
   +-+-----------------------------------+--fullselect-+   
   | |       .-,-----------------------. |             |   
   | |       V                         | |             |   
   | '-WITH----common-table-expression-+-'             |   
   +-GET DIAGNOSTICS-----------------------------------+   
   +-IF------------------------------------------------+   
   +-INSERT--------------------------------------------+   
   +-ITERATE-------------------------------------------+   
   +-LEAVE---------------------------------------------+   
   +-MERGE---------------------------------------------+   
   +-searched-delete-----------------------------------+   
   +-searched-update-----------------------------------+   
   +-SET Variable--------------------------------------+   
   +-SIGNAL--------------------------------------------+   
   '-WHILE---------------------------------------------'   
refer
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0000931.htm
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm