T-SQL Scripts for Table

----------------------------------------------------------------------------------------------
Here in many cases we have to first find table contain record or not.
If table contains record than update that row is going on. If table not contain record than insert new row in table.
So in this case we first find that table contains records or not and than we decide to take which operation have to perform on that table.
Like…….
DECLARE @COLUMNID INT
DECLARE @NEWVALUE
DECLARE @cnt INT
SELECT @cnt = COUNT(COLUMNNAME) FROM TABLENAME WHERE COLUMNID = @COLUMNID

IF(@cnt > 0)
BEGIN
UPDATE TABLENAME
SET COLUMNNAME = @NEWVALUE
WHERE COLUMNID = @COLUMNID
END
ELSE
BEGIN
INSERT INTO TABLENAME
(COMUMNNAME)
VALUES(@NEWVALUE)
END

This will done shortly with the use of exists() function that return ‘true’ if select query return records
Otherwise return ‘false’
Like……..

IF EXISTS(SELECT COLUMNNAME FROM TABLENAME WHERE COLUMNID = @COLUMNID)
BEGIN
UPDATE TABLENAME
SET COLUMNNAME = @NEWVALUE
WHERE COLUMNID = @COLUMNID
END
ELSE
BEGIN
INSERT INTO TABLENAME
(COMUMNNAME)
VALUES(@NEWVALUE)
END
----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------
Here sql statements for
1.creating table
2.inserting record
3.updating record
4.deleting record
5.fetching records
6.droping table

For example i take Student as a entity and perform above statements
for it.


1.Creating table

CREATE TABLE [dbo].[Student](
[studentID] [bigint] IDENTITY(1,1) NOT NULL,
[studentName] [varchar](50) NOT NULL,
[studentAge] [int] NOT NULL,
[studentSex] [bit] NOT NULL,
[studentBirthDate] [datetime] NOT NULL,
[studentAddress] [varchar](200) NULL,
CONSTRAINT [PK_StudentMaster] PRIMARY KEY CLUSTERED
(
[studentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


2.Inserting record

INSERT INTO [dbo].[Student]
([studentName]
,[studentAge]
,[studentSex]
,[studentBirthDate]
,[studentAddress])
VALUES
('Paresh Patel'
,25
,'true'
,'1982-06-23 00:00:00.000'
,'Ahmedabad')


3.Updating record

UPDATE [dbo].[Student]
SET [studentName] = 'Nirmit Oza'
,[studentAge] = 25
,[studentSex] = 'false'
,[studentBirthDate] = '1982-06-23 00:00:00.000'
,[studentAddress] = 'Mehsana'
WHERE [studentID] = 1


4.Deleting record

DELETE FROM [dbo].[Student]
WHERE [studentID] = 2


5.Fetching records

SELECT [studentID]
,[studentName]
,[studentAge]
,[studentSex]
,[studentBirthDate]
,[studentAddress]
FROM [dbo].[Student]


6.Droping table

DROP TABLE [dbo].[Student]
----------------------------------------------------------------------------------------------