Code snippet -personal

 

Creating private database, which is accessed by private user.

CREATE DATABASE A

GO

 

CREATE LOGIN user_A with password='rajan'

Go

 

USE A

GO

CREATE USER user_A for login user_A;

GO

EXEC sp_addrolemember 'db_owner', 'user_A'

GO

 

 

--Step 1: (create a new user)

create LOGIN rajan WITH PASSWORD='rajan@123', CHECK_POLICY = OFF;

 

 

-- Step 2:(deny view to any database)

USE master;

GO

DENY VIEW ANY DATABASE TO rajan;

 

 

 -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below)

USE master;

GO

ALTER AUTHORIZATION ON DATABASE::PPIVDummy TO rajan;

GO

 

 

 

 

 

 

 

Deleting or dropping user

DROP LOGIN login_name ;

 

 

 

Creating temporary table,inserting values from existing table into temporary table, updating the temporary table and finally inserting the temporary table into a permanent table

 

 

SELECT * FROM dbo.PDXTerminals

 

SELECT * INTO #temp FROM dbo.PDXTerminals --- creating temporary table and fetching its format and data from PDXTerminals Table

 

DELETE FROM #temp WHERE IsOur='T'

SELECT * FROM #temp    --- manipulating Temporary table

DELETE FROM #temp WHERE AcquirerId<>'405019'

UPDATE #temp SET AcquirerId='rajan'

 

Insert into PDXTerminals  -Finally uploading the data(bulk) into PDXTerminals table

Select * from #temp

 

Note: create table or just create is not going to create temporary table … it is done just by select * into #temp from respective table

 

 

Using Identity after datatype

 

ID INT IDENTITY –it is an auto increment process of id’s (user should not input id number                         it is auto generated)

  PRIMARY KEY,

 

 

Creating view only from a single table

CREATE VIEW V_byap

 AS

SELECT * FROM dbo.Byap

 

 

while loop --break and continue

 

if else else if

 

cursor fetch

close cursor

deallocate cursor

 

print

 

EXEC para1,para2, para3 OUTPUT

 

try cache block