Query SQL Server

Berikut merupakan kumpulan sql server query yang pernah aku pakai selama bekerja, mulai dari basic query sampai dengan tingkat midle level. Dengan mencatut mudah-mudahan kita tak kesusahan dalam menghapal semua query, tp cukup hanya dengan memahami dan melihat kembali jika ada yang terlupa :) semoga bermanfaat. terima kasih..
--creat db
create database dbname
create table tablename (column1 varchar(100));

-- query primery key
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

--pk with constrain 
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)


--insert not spesifik column
INSERT INTO Customers
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

--insert with spesifik/bisa 1colom saja
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

--insert into from selected query
INSERT INTO dts_menu(UserId) SELECT userid from aspnet_membership where userid NOT IN(select userid from dts_menu)

--update
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';

--add edit delete column 
ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name
ALTER COLUMN column_name datatype

ALTER TABLE table_name
DROP COLUMN column_name

--delete isi kolom
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';


--select top 1
SELECT TOP 1 * from tamu

--loop
http://msdn.microsoft.com/en-us//library/ms178642.aspx
--http://stackoverflow.com/questions/4487546/do-while-loop-in-sql-server-2008
--http://blog.sqlauthority.com/2007/10/24/sql-server-simple-example-of-while-loop-with-continue-and-break-keywords/

--fungsi in dan on dalam sql dan persamaannya
SELECT*FROM MAHASISWA WHERE NAME IN ('ILVAN','ISHA')
SELECT*FROM MAHASISWA WHERE NAME NOT IN ('ILVAN','ISHA')  

--sama dengan-->
SELECT*FROM MAHASISWA WHERE NAME ='ILVAN' AND NAME='ISHA'

SELECT A.*,B.* FROM MAHASISWA A LEFT JOIN MATAKULIAH B ON a.ID AND B.ID_MAHASISWA

--menggabungkan dua buah table dengan format field sama sehingga field jd tergabung akan tetapi keep dupicate, letakkan diantara dua query tersebut-->
Union All
--without duplicate-->
Union
contoh
SELECT a.kd_bu,a.nm_bu,max(a.status)as status
FROM (SELECT a.kd_bu,a.nm_bu,1 status FROM dts_bu a  left JOIN dts_setup_user b ON b.kd_bu=a.kd_bu WHERE b.user_id='81500436-DB88-4F8E-9C10-BA186891F43C' GROUP BY a.kd_bu,a.nm_bu 
UNION ALL 
SELECT a.kd_bu,a.nm_bu,0 status FROM dts_bu a GROUP BY a.kd_bu,a.nm_bu ) a 
group BY a.kd_bu,a.nm_bu


--remove duplicate
Select Distinct  * from nama_table --DISTICT ALL
SELECT DISTINCT ID_TAMU FROM checkin  --DISTINCT SELECTED FIELD

<> == tidak sama dengan


--between and convert date
SELECT sysid, kd_bu,Tanggal,Status FROM dts_log where kd_bu='ss101'
and tanggal between convert(datetime,'01-01-2013',103) AND convert(varchar(10),'01-01-2015',103)

--melihat struktur dalam sebuat table/view/sp
sp_help mahasiswa

select Branch.Name into #branchx from Branch --field name saja
select * into #branchx1 from Branch --smua kolom

--contoh deklarasi dan pemanggilan parameter
declare @brachx as varchar(50)
set @brachx='JKT00'
select*from Branch where Branch.Branch=@brachx

--get list directory
CREATE table #directory (s varchar(1000));
DECLARE @cmd varchar(2000)

--gw comment biar jgn warna teks putih dibawah. aslinya ga kekoment
insert #directory exec master..xp_cmdshell @cmd SELECT * FROM #directory drop table #directory 


--case
SELECT case id_kamar WHEN '1' THEN 'yes' ELSE 'no'END as abc FROM kamar


--fungsi cast untuk tampilin date aja dari tipe datetime, bs jg ke data_type lain, contoh cast: 
select kd_bu as [Kode BU],cast(tgl_proses as date(10))as [tanggal login] from dts_upload

--convert datetime ke varchar dan format british style. dd/MM/yyyy
select kd_bu as [Kode BU], tgl_proses as [Tanggal Prosses] from dts_upload --where tgl_proses=@tgl
where convert(varchar(10),tgl_proses,103)='23/01/2014'

--format tanggal dateformat bisa dipakek di query, maka smua format date akan dirubah ke british style
SET DATEFORMAT dmy
exec sp_filtertanggal '22/12/2012'


--max
SELECT MAX(sysid) as id from dts_download
--count
select COUNT(sysid) as id FROM dts_download
--sum
select SUM(sysid) as id from dts_download
--top 1
SELECT top 1 sysid as id FROM dts_download ORDER BY sysid DESC
--round?

--set identity insert on off
DECLARE @TagID int
BEGIN TRANSACTION
SET IDENTITY_INSERT dts_download ON  
 SELECT @TagID = coalesce((select max(sysid) + 1 from dts_download), 1)
 commit
Insert into dts_download(sysid,kd_bu,[user_id],username,data,[status],[current_date],tgl_proses) 
VALUES(@TagID,'ss102','d3519197-c2e8-4c53-82de-fce435c64b89','admin','datea','0','10/02/2014','01/01/2013')

--update table with inner join column
update am set am.isadmin=@PisAdmin,am.Username2=@Pusername2, am.email=@pemail FROM aspnet_Membership am inner join aspnet_users au ON au.UserId=am.UserId where au.userid='blabala'

contoh trigger?
create trigger nama_trigger
after insert
on table_A
for each row
insert into table_B(id,nama,pekerjaan)
values(new.id,new.nama,new.pekerjaan)

--memindahkan data table dan table dari dari satu database ke lain
insert into sea..a
select * from tes..b


--untuk melihat descripsi suatu table dimana codenya ada pda table lain
select * from vType where vType in (select distinct vtype from nvoucher) 

--untuk parameter all
declare @param1 varchar(50)
set @param1=''
select*from ktp as kt
where (nama =@param1 OR '' =@param1)


errror when exporting report
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (357)" failed because error code 0xC0209076 occurred, and the error row disposition on "input "Destination Input" (357)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)


--CULOUMTABLE untuk mengetahui nama colom ada ditable mana saja 
select t.name as TABLE_NAME ,c.name from sys.tables t inner join sys.syscolumns c on t.object_id = c.id 
where c.name like '%subjecttonoclaimf%'

-- melihat nama table 
SELECT* FROM sys.tables where name LIKE '%branc%'


--menghitung jumlah row 
select int_re_usu as Qtd_Respostas from tb_questionario_voar_resposta  group by int_re_usu
Select @@RowCount
atau
Select Count(*) from 
(select int_re_usu as Qtd_Respostas from tb_questionario_voar_resposta  group by int_re_usu) q1

--melihat tanggal terakhir create and modified
SELECT * FROM sys.objects where create_date > '2013-04-16' 
SELECT * FROM sys.tables where modify_date > '2013-04-16' 

--tak boleh ada spasi nama kolom atau ada spasi di akhir kolom di nama fields ketika akan diimport 


Select #VoucherP.Voucher, #VoucherP.Branch, #VoucherP.DebtorF, #VoucherP.CreditorF, Max(#VoucherP.RefNo+'/'+#VoucherP.DocNo) as DocNo, Max(#VoucherP.Voucher) as RefNo, Max(#VouchrP.Remarks) as Remarks, Max(#VoucherP.Currency) as Currency, Max(#VoucherP.SL_Cat) as SL_Cat, Max(#VoucherP.Date) as Date, Max(#VoucherP.Rate) as Rate, Max(#VoucherP.VType) as VType, Max(#VoucherP.ProAcc) as ProAcc, Max(#VoucherP.ComAcc) as ComAcc, sum(SL.Nominal) as Nominal_CC from #VoucherP Left Join pSLSum SL on (SL.Voucher=#VoucherP.Voucher and SL.Account between '48500000' and '48500000')  Group By #VoucherP.Voucher, #VoucherP.Branch, #VoucherP.DebtorF, #VoucherP.CreditorF


SELECT*FROM MAHASISWA WHERE NAME IN ('ILVAN','ISHA')
SELECT*FROM MAHASISWA WHERE NAME ='ILVAN' AND NAME='ISHA'

SELECT A.*,B.* FROM MAHASISWA A LEFT JOIN MATAKULIAH B ON a.ID AND B.ID_MAHASISWA

Union All--> menggabungkan dua buah table dengan format field sama sehingga field jd tergabung

Distinct--> remove duplicate

<> tidak sama dengan


select Branch.Name into #branchx from Branch --field name saja
select * into #branchx1 from Branch --smua kolom

-- melihat value dalam database
DECLARE @SearchStr nvarchar(100)
 SET @SearchStr = 'your parameter here :)'
 
 DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

 SET NOCOUNT ON

 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
 SET  @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

 WHILE @TableName IS NOT NULL
 BEGIN
  SET @ColumnName = ''
  SET @TableName = 
  (
   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
   FROM  INFORMATION_SCHEMA.TABLES
   WHERE   TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(
       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 'IsMSShipped'
             ) = 0
  )

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
   SET @ColumnName =
   (
    SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
     AND TABLE_NAME = PARSENAME(@TableName, 1)
     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
     AND QUOTENAME(COLUMN_NAME) > @ColumnName
   )
 
   IF @ColumnName IS NOT NULL
   BEGIN
    INSERT INTO @Results
    EXEC
    (
     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
     FROM ' + @TableName + ' (NOLOCK) ' +
     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    )
   END
  END 
 END

 SELECT ColumnName, ColumnValue FROM @Results
 
 
--EXTRAK ATAU FILTER TANGGAL DATETIME
SELECT DATEPART(MONTH, TABLENAME.COLUMNNAME)FROM TABLENAME
Select datepart(month,ContestAchievementtbl.periode) from ContestAchievementtbl

--memilih tahun dari datetime
USE AdventureWorks2008
SELECT  * FROM Sales.SalesOrderHeader WHERE Year(OrderDate)=2004
 
 
 
--CONCAT, MENYATUKAN DUA FIELD MENJADI SATU KOLOM, CONTOH FNAME LNAME == IKHSAN SAIDI
SELECT CONCAT(' ',region_name,store_name) FROM Geography
WHERE store_name = 'Boston'; 
--//ATAU
SELECT region_name + ' ' + store_name FROM Geography
WHERE store_name = 'Boston';


--MELIHAT TANGGAL AWAL BULAN DAN AKHIR BULAN
DECLARE @Month smallint
SET @Month = 9
SELECT DAY(DATEADD(Month, DATEDIFF(Month, -1, getdate()) - 2, -1) + 1) as FirstDayofMonth,
DAY(DATEADD(Month, DATEDIFF(Month,0,getdate())+@Month, -1)) as LastDayOfMonth

--GROUP BY DAN HAVING //HAVING SAMA SEPERTI WHERE SETELAH GROUPING / SELECT //hampir semua count/sum harus memakai group by
SELECT id_tamu, SUM(lama_menginap) FROM checkin GROUP BY id_tamu HAVING SUM(lama_menginap) >= 10

--group by - order by //digroup dlu baru di order
SELECT year=YEAR(OrderDate), Orders=COUNT(*) 
from Sales.SalesOrderHeader GROUP BY YEAR(OrderDate) ORDER BY year(orderdate) 

--ORDER
SELECT NIM, StudentName, Score FROM Student ORDER BY Score DESC

--TRUNCATE //PERBEDAAN TRUNCATE DAN DELETE ADALAH. TRUNCATE AKAN MENGHAPUS AUTO NUMBER, AKAN MULAI DARI 0 LAGI
TRUNCATE TABLE table_name

--FORMAT() TANGGAL
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate
FROM Products;

--CONVER DATETIME KE BEBERAPA FORMAT
SELECT CONVERT(nvarchar, getDate(), 102)


--AND OR //AND KETIKA BEDA FIELD DAN OR KETIKA SAMA FIELD. LIHAT CITY
SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

-- AKHIRAN AWALAN
SELECT ID=BusinessEntityID, FullName=FirstName + ' '+ LastName
INTO ListA
FROM AdventureWorks2008.Person.Person WHERE LEFT(LastName,1) = 'A'

SELECT*from listA where RIGHT(id,2)='73'

--IMPORT TXT FILE INTO SQL
bulk insert [dbo].[tamu]
from 'C:\Users\Public\datatamu.txt'
with (fieldterminator = ',', rowterminator = '\n')
go


--foreach for loop1

SELECT
    RowNum = ROW_NUMBER() OVER(ORDER BY CustomerID)
    ,*
INTO #Customers
FROM SalesLT.Customer

DECLARE @MaxRownum INT
SET @MaxRownum = (SELECT MAX(RowNum) FROM #Customers)

DECLARE @Iter INT
SET @Iter = (SELECT MIN(RowNum) FROM #Customers)

WHILE @Iter <= @MaxRownum
BEGIN
    SELECT *
    FROM #Customers
    WHERE RowNum = @Iter
   
    -- run your operation here
   
    SET @Iter = @Iter + 1
END

DROP TABLE #Customers


--foreach for loop2

DECLARE @i int
DECLARE @employee_id int
DECLARE @employee_table TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , employee_id int
)


-- populate employee table
INSERT @employee_table
SELECT distinct employee_id FROM SomeTable


-- enumerate the table
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @employee_table)
IF @numrows > 0
    WHILE (@i <= (SELECT MAX(idx) FROM @employee_table))
    BEGIN


        -- get the next employee primary key
        SET @employee_id = (SELECT employee_id FROM @employee_table WHERE idx = @i)


 --
        -- do something with this employee
 --
    

        -- increment counter for next employee
        SET @i = @i + 1
    END

--foreach for loop3


Declare @f1 varchar(max) 
--DECLARE @sSQL nvarchar(max)
--set nocount on
--select @sSQL = field1 from temp1

    declare CURSOR_TESTE cursor for 
 
    select   
        s
    from ##Import
    
    open CURSOR_TESTE  
 
    fetch next from CURSOR_TESTE into @f1  
      
    while @@fetch_status = 0  
    begin 
    
    BEGIN TRY
          EXEC(@f1)
          END TRY
 BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;
END CATCH; 

        fetch next from CURSOR_TESTE into @f1 
    end 
 
    close CURSOR_TESTE  
    deallocate CURSOR_TESTE 


 --untuk melihat direktory dan sub directory folder
 
 EXEC master..xp_subdirs 'C:\\Inetpub' 
EXEC master..xp_dirtree 'C:\\Inetpub' ,2

CREATE TABLE #TempTable (Subdirectory VARCHAR(512), Depth INT);
INSERT INTO #TempTable (Subdirectory, Depth)
EXEC xp_dirtree 'C:\Inetpub'
SELECT Subdirectory, Depth
FROM #TempTable;
DROP TABLE #TempTable;


 
--untuk melihat baris yang error ketika import  tambah tag error file
bulk insert [dbo].[tamu]
from 'C:\Users\Public\datatamu.txt'
with (fieldterminator = ',', rowterminator = '\n',errorfile='c:\myrubbishdata.log')
go

--JIKA ADA TIPE DATA UNIQIDENTIFIER YG GA BISA DIIMPORT MAKA BISA DIBIKIN TEMPORARY TABLE TANPA ID"UNIQIDENTIFIER" BARU DI INSERT INTO 
insert [dbo].[Courses]
  (code, description, instructor, date, venue, duration)
select 
   code, description, instructor, cast(date as date), venue,
   duration
from [dbo].[CoursesTemp]


DECLARE @sys_usr char(30)
SET @sys_usr = SYSTEM_USER
SELECT 'The current system user is: '+ @sys_usr
GO

SELECT SYSTEM_USER, HOST_NAME() ;
SELECT SUSER_SNAME()
EXEC sp_who2;

DECLARE @retTable TABLE (
 SPID int not null
 , Status varchar (255) not null
 , Login varchar (255) not null
 , HostName varchar (255) not null
 , BlkBy varchar(10) not null
 , DBName varchar (255) null
 , Command varchar (255) not null
 , CPUTime int not null
 , DiskIO int not null
 , LastBatch varchar (255) not null
 , ProgramName varchar (255) null
 , SPID2 int not null
 , REQUESTID INT
)  

INSERT INTO @retTable EXEC sp_who2  

SELECT Status, Login, HostName, DBName, Command, CPUTime, ProgramName -- *
  FROM @retTable
  --WHERE Login not like 'sa%' -- if not intereted in sa
  ORDER BY Login, HostName
  
  
  
-----------------------------------------------------------
ALTER PROCEDURE MyProcedure
@DBName sysname
--@tablename sys.tables

AS
DECLARE @cmd nvarchar(2000)
SET @cmd = N'SELECT * FROM ' + @DBName + '.dbo.checkin'
exec (@cmd)
GO
--EXEC MyProcedure hotel

-----------------------------------------------------------sosro
USE hotel
Exec sp_help checkin
------------------------------------------
SELECT name, system_type_id, user_type_id, max_length
 FROM sys.types WHERE name IN (N'sysname', N'nvarchar');
 ------------------
 SELECT x = USER_NAME() INTO #foo;
EXEC tempdb..sp_help '#foo';
-------------
declare @dbname nvarchar(255);    
set @dbname = 'db1';    
if @dbname = 'db1'
 use db1;
else if @dbname = 'db2'
 use db2;
 --//dinamis-->
declare @sql nvarchar(255);
set @sql = 'using '+@dbname+'; exec mydatabase..do_work_proc;';

set @sql='select * from '+@dbname+'.sys.tables';

------------------------
--SQL INJECTION
--CREAT INDEX
--START TRANSACTION. //Digunakan untuk menandakan awal dari transaksi database, tanpa memperdulikan apakah akan berhasil atau gagal.
--COMMIT.//Menyebabkan seluruh perubahan data yang dilakukan pada Start Transaction dikonfirmasi secara permanen.
--ROLLBACK.//Membatalkan transaksi data.



--TIPE DATA DAN KETERANGAN============================================================================

Bit            Integer dengan nilai 0 atau 1
Int           Nilai integer dengan nilai antara -2^31 atau (-2.147.483.648) sampai 2^31-1 atau (+2.147.384.647)
Tinyint         0-255
Decimal atau Numeric   Angka antara -10^38-1 sampai 10^38-1
Money          Nilai yang berhubungan dengan mata uang dari -2^63( -922.377.203.685.477,5808) sampai 2^63-1(922.377.203.685.477,5807)
Smallmoney       -214,748.3648 hingga 214,748.3647
Float          -214.748,3648 sampai 1.79E+308
Real          -3.40E+308 sampai 3.40E+38
DateTime         1 Januari 1973 sampai 31 Desember 9999
SmallDateTime       1 Januari 1900 sampai 6 juni 2079, dengan ketelitian hingga 1 menit
Time         00:00:00.0000000 - 23:59:59.9999999
Uniqueidentifier      6F9619FF-8B86-D011-B42D-00C04FC964FF
Xml          Tipe data ini dapat menyimpan data hingga 2Gb
Char          Jumlah karakter tetap dengan ukuran maksimal 8000 karakter
Varchar         Jumlah karakter variabel dengan ukuran maksimal 8000 karakter
Text          Jumlah karakter variabel dengan ukuran maksimal 2.147.483.647 karakter
NChar          Jumlah karakter tetap dengan ukuran maksimal 4000 karakter
NVarchar         Jumlah karakter variabel dengan ukuran maksimal 4000 karakter
NText          Jumlah karakter variabel dengan ukuran maksimal 1.073.741.823 karakter
Cursor         ---
Binary          Jumlah biner tetap dengan ukuran maksimal 8000 bytes
Varbinary         Jumlah biner variabel dengan ukuran maksimal 8000 bytes
Image          Jumlah karakter variabel dengan ukuran maksimal 2.147.483.647 bytes



--------------------------------------------------------exec dari beberapa variable
declare @slc as nvarchar(max)
SET @slc = ' begin tran; select * from '
SELECT @slc = @slc  + field1 + ';' FROM Temp1
SET @slc = @slc + 'commit tran'
PRINT(@slc)
exec(@slc)
------------------------------------------


--cek table exist
create procedure check_table_exists
@aTableName varchar(50)
as
return (select count(*) from sysobjects where type = 'U' and name = @aTableName)
go

--more sql function @ http://www.w3schools.com/sql/sql_functions.asp

BERIKUT MERUPAKAN KONVERSI DATATYPE SQL SERVER KE C#

1 comments: