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.
--table size;SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.Rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.is_ms_shipped = 0
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceKB DESC;
--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.aspBERIKUT MERUPAKAN KONVERSI DATATYPE SQL SERVER KE C#
manthap
ReplyDelete