insert lines / append text [begin end between before after mysecondtext blankline file]

SELECT '' UNION ALL SELECT mycolumn FROM mytable	insert blankline above beginning of all the lines
SELECT 'mytext' UNION ALL SELECT mycolumn FROM mytable	insert 'mytext' above all the lines/above beginning of lines
SELECT (CASE WHEN mycolumn LIKE '%mytext%' THEN 'mysecondtext' ELSE '' END)+ mycolumn as mynewcol ,* FROM mytable	if 'mytext' on line, insert word/column 'mysecondtext ' at beginning of line
INSERT INTO mytable(mycolumn) VALUES ('mytext')	insert 'mytext' below end of all lines
SELECT mycolumn FROM mytable UNION ALL SELECT ''	insert blankline below the end of all the lines
SELECT mycolumn FROM mytable UNION ALL SELECT 'mytext'	insert below the end of the lines 'mytext'
SELECT mycolumn FROM mytable UNION ALL SELECT 'mytext' UNION ALL SELECT 'mytext'	insert 2 blanklines and the line with 'mytext' at end of the lines, below the lines
WITH mytmp AS (SELECT id = 1 UNION ALL SELECT id + 1 FROM mytmp WHERE id < 2 )SELECT id FROM mytmp	insert table 2 lines, loop ID 1 to 2
;WITH tmp AS (SELECT 1 AS ID UNION ALL SELECT ID + 1 AS ID FROM tmp WHERE tmp.ID < 2) SELECT * INTO mynewtable FROM tmp	insert new text/table of 2 lines, with numbers 1 to 2
SELECT * INTO T10 FROM (SELECT 1 AS ID)X WHERE 1 = 0;WITH mytmp(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM mytmp WHERE n<10) INSERT T10 SELECT * FROM mytmp	insert 10 lines, with loop ID 1 to 10
WITH X(id) AS (SELECT 1 FROM (SELECT 1 AS mycol) Y UNION ALL SELECT id+1 FROM X WHERE id+1 <= 11)SELECT * INTO T11 FROM X	insert 11 lines, loop
WITH temp AS (SELECT ID=1 UNION ALL SELECT ID+1 FROM temp WHERE ID < 100) SELECT ID INTO T100 FROM temp	insert 100 lines, loop ID 1 to 100

insert text on the line[mytext before after column blankline]

SELECT ('mytext') AS mynewcol,* FROM mytable	insert 'mytext ' / column before beginning of the line ie: sed 's/^/ /' #indent lines
SELECT IIF(0< CHARINDEX(' ',mycolumn,1), SUBSTRING( mycolumn,0 ,CHARINDEX( ' ',mycolumn+' '))+ 'mytext' + SUBSTRING( mycolumn,CHARINDEX( ' ',mycolumn+' '),999) + mycolumn ,mycolumn),* from mytable	insert 'mytext' after begin column
SELECT *,('mytext') AS mynewcol FROM mytable	insert 'mytext' or column after the end of the line
ALTER TABLE mytable ADD mynewcol VARCHAR(12) NULL DEFAULT(NULL);	insert 'mytext' or column after the end of the line
	change width of character field
ALTER TABLE mytable ADD mynewcol1 VARCHAR(25) NULL DEFAULT(NULL), mynewcol2 NUMERIC NULL DEFAULT(NULL), mynewcol3 BIGINT DEFAULT(NULL);	insert column & second & third column
SELECT REPLACE(mycolumn, 'mytext','mysecondtextmytext') FROM mytable	insert 'mysecondtext' before 'mytext'
SELECT REPLACE(mycolumn, 'mytext','mytextmysecondtext') FROM mytable	insert 'mysecondtext' after 'mytext'
SELECT mysecondcolumn + 'mytext' FROM mytable	insert 'mytext' after second column. TIP: to insert a new column use ' mytext'
SELECT 'mytext' + mysecondcolumn FROM mytable	insert 'mytext' before second column TIP: to insert a new column use 'mytext '
SELECT IIF(0mytext',mycolumn,1),'mysecondtext'+mycolumn,mycolumn),* from mytable	insert mysecondtext/column at beginning of line if line has 'mytext'
SELECT IIF(0mytext',mycolumn,1),mycolumn + 'mysecondtext',mycolumn),* from mytable	insert mysecondtext/column at end of line if line has 'mytext'
SELECT REPLACE(mysecondcolumn,'mytext','mysecondtextmytext') FROM mytable	if 'mytext' is in second column, insert 'mysecondtext' before the second column
SELECT REPLACE(mysecondcolumn,'mytext','mytextmysecondtext') FROM mytable	if 'mytext' is in second column, insert 'mysecondtext' after the second column
SELECT REPLACE(mycolumn,'mytext', 'mysecondtextmytext') FROM mytable	if 'mytext' at the beginning of a line, insert 'mysecondtext' before 'mytext'
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS myrow,* FROM mytable	insert linenumbers at the beginning of each line ie: find out linenumbers with 'mytext' : cat .txt| nl -ba |fgrep 'mytext'
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS myrow, * FROM mytable ) X WHERE mycolumn ='mytext'	select lines with 'mytext' include linenumbers (usefull for large files & can delete section of lines , from fixed linenumbers )
SELECT *,ROW_NUMBER() OVER (ORDER BY mycolumn) AS mylinenumber FROM mytable	insert linenumbers (formated to 9 numbers) at end

sort & rearrange order [sort second column delimiter split]

SELECT * FROM mytable ORDER BY mycolumn ASC	sort, but ignore case , uppercase or lowercase
SELECT * FROM mytable ORDER BY mycolumn	sort by numbers ie: look at beginning column as numeric values and sort TIP: if there are punctuation characters, sort may not work & delete them
SELECT * FROM mytable ORDER BY mysecondcolumn	sort on the second column TIP:beware of multiple spaces between columns
SELECT * FROM mytable ORDER BY mysecondcolumn ORDER DESC	sort on second column but in reverse order
SELECT mycolumn,mysecondcolumn FROM mytable GROUP BY mycolumn,mysecondcolumn ORDER BY mycolumn -- (only showing 2 columns here)	sort lines and then delete duplicate lines
SELECT * FROM mytable ORDER BY LEN(CAST(mycolumn AS VARCHAR)) ASC	sort the lines of a file by length, shortest on top
SELECT LEFT(mycolumn,2),* FROM mytable	select the beginning and second characters (fixed) of each line. delete after 2nd character. TIP: it s usually better to search for the text, rather than select a fixed nmber of characters : most often the data is not fully consistent
SELECT SUBSTRING (mycolumn, 2,1+(8-2)) , * FROM mytable	select absolute/fixed character positions between second to 8th
SELECT SUBSTRING(mycolumn,2,3)+ SUBSTRING(mycolumn,8,7) FROM mytable	select fixed characters 2 - 5, 8 - 15
SELECT SUBSTRING(mycolumn,2 ,9999) FROM mytable	select fixed text after the second character onwards, delete beginning 2 characters
SELECT IIF(LEN(mycolumn)>0, SUBSTRING(mycolumn, 1, LEN(mycolumn)-1), '') FROM mytable	select 2 (fixed) characters from the end of line, delete before the second from end character

convert /split / change structure of lines

SELECT t1.mycolumn, SPLIT.t1.value('.', 'VARCHAR(100)') AS mynew FROM (SELECT mycolumn ,CAST('' + REPLACE(mysecondcolumn, ' ', '') + '' AS XML) AS mynew FROM mytable) AS t1 CROSS APPLY mynew.nodes ('/M') AS SPLIT(t1)	replace spaces with newlines, convert/split text to a single list of words/products TIP:may need to replace punctuation with spaces first
SELECT t1.mycolumn,mycol = REPLACE ((SELECT t2.mysecondcolumn AS 'data()' FROM mytable AS t2 WHERE t1.mycolumn = t2.mycolumn FOR XML PATH('')), ' ', ', ') FROM mytable AS t1 GROUP BY mycolumn	replace /delete newlines with spaces, convert list into a long single line TIP: if windows, us \r (carriage return (13)) instead of \n (10)
SELECT mycolumn,STUFF((SELECT ',' + [mysecondcolumn] FROM mytable WHERE (mycolumn = t1.mycolumn) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS mycol FROM mytable t1	replace /delete newlines with spaces, convert list into a long single line,( group by second column)
SELECT x.XmlCol.value('.','varchar(36)') AS mylist FROM (SELECT CAST('' + REPLACE(mycolumn, ',', '') + '' AS xml) AS myXml FROM mytable) AS t1 CROSS APPLY t1.myXml.nodes('e') x(XmlCol);	replace commas (mydelimiter = ',') with a newline ie: split all text with commas into a table of words/columns (structure)
;WITH mytmp( mynewlist, mycolumn) AS (SELECT LEFT(mycolumn, CHARINDEX(',' ,mycolumn+',')-1) ,STUFF(mycolumn, 1, CHARINDEX(',' ,mycolumn+','), '') FROM mytable UNION all SELECT LEFT(mycolumn, CHARINDEX(',' ,mycolumn+',')-1) ,STUFF(mycolumn, 1, CHARINDEX(',' ,mycolumn+','), '') FROM mytmp WHERE mycolumn > '' ) SELECT mynewlist FROM mytmp -- mycolumn must be VARCHAR(MAX)	replace commas (mydelimiter = ',') with a newline ie: split all text with commas into a table of single words/columns (structure)
SELECT t1.*, t2.mytmp AS mysplit FROM (SELECT *, CAST(''+REPLACE(t.mycolumn,';','')+'' AS XML) AS myxml FROM mytable t) t1 CROSS APPLY ( SELECT mydat.D.value('.','VARCHAR(50)') AS mytmp FROM t1.myxml.nodes('X') AS mydat(D)) t2	replace semicolon (mydelimiter = ';') with a newline ie: split/convert text with semicolon into a table of words/columns (structure)
CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))RETURNS @TempTab TABLE (id VARCHAR(1000) not null) AS BEGIN; SET @InStr = REPLACE(@InStr + ',', ',,', ',') DECLARE @SP INT DECLARE @VALUE VARCHAR(1000) WHILE PATINDEX('%,%', @INSTR ) <> 0 BEGIN SELECT @SP = PATINDEX('%,%',@INSTR) SELECT @VALUE = LEFT(@INSTR , @SP - 1) SELECT @INSTR = STUFF(@INSTR, 1, @SP, '') INSERT INTO @TempTab(id) VALUES (@VALUE) END RETURN END GO DECLARE @LIST VARCHAR(200);SET @LIST = '1234567,mytext,12345';SELECT * FROM mytable WHERE mycolumn IN (SELECT * FROM dbo.CSVToTable( @LIST ))	replace commas (mydelimiter = ',') with a newline ie: split all text with commas into a table of words/columns (structure)
SELECT mycolumn, UPPER( mysecondcolumn) ,mythirdcolumn FROM mytable	convert second column to uppercase , '|' as mydelimiter
SELECT mycolumn, COUNT(*) AS times FROM mytable GROUP BY mycolumn	select how many occurrence , ie: pivot table
SELECT mycolumn, SUM(mysecondcolumn) AS mytotal FROM mytable GROUP BY mycolumn	sum/add up second column for each unique occurrence of the beginning column (like SQL GROUP BY)
SELECT mycolumn, COUNT(*) OVER (PARTITION BY mycolumn,mysecondcolumn) AS myCOUNTIF, COUNT(mysecondcolumn) OVER (PARTITION BY mycolumn,mysecondcolumn) AS mynewcol2, COUNT(*) OVER () AS mytotal FROM mytable	count how many different types (group by) of mycolumn and mysecondcolumn, how many total mycolumn
SELECT mycolumn,mysecondcolumn COUNT(mycolumn) FROM mytable GROUP BY mycolumn,mysecondcolumn	count how many different types (group by) of mycolumn and mysecondcolumn, how many total mycolumn
SELECT mycolumn, MAX( mysecondcolumn) AS mytotal FROM mytable GROUP BY mycolumn	maximum value of second column for each unique occurrence of the beginning column (like SQL GROUP BY)
SELECT mycolumn, MAX( mysecondcolumn) AS mytotal FROM mytable GROUP BY mycolumn	maximum value of second column for each unique occurrence of the beginning column, include all columns (like SQL GROUP BY)
SELECT mycolumn, MIN( mysecondcolumn) AS mytotal FROM mytable GROUP BY mycolumn	minimum value of second column for each unique occurrence of the beginning column (like SQL GROUP BY)
SELECT SUM(mysecondcolumn) OVER (ORDER BY mycolumn,mythirdcolumn) AS mytotal ,* FROM mytable	sum up second column - running total
SELECT mycolumn ,COUNT(*) FROM mytable GROUP BY mycolumn	occurrence based on begin column, include number of entriies
SELECT mycolumn,mysecondcolumn,COUNT(*) FROM mytable GROUP BY mycolumn,mysecondcolumn	occurrence based on begin column & secondcolumn, include number of entries
SELECT * FROM (SELECT *,LAG (mycolumn,1) OVER (PARTITION BY mycolumn ORDER BY mycolumn DESC) AS mytmp FROM mytable) X WHERE mytmp IS NULL	based on begin column find begin line of each unique group

loop , repeat muppix commands [mycommand mysecondcommand]

SELECT *,ROW_NUMBER() OVER (ORDER BY mycolumn DESC ) AS ID INTO #myloop FROM (SELECT DISTINCT mycolumn FROM mytable) X	create looping dates temp table (1)
DECLARE @Period INT;DECLARE @i INT = 1 ;DECLARE @End INT = 10; WHILE @i <= @End BEGIN;SET @Period = (SELECT mycolumn FROM #myloop WHERE ID=@i) ;SELECT @Period;SET @i = @i + 1;END;	loop through each dates (2)
DECLARE @i INT = 1 ;DECLARE @End INT = 10; WHILE @i <= @End BEGIN; SELECT * FROM(SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS myrow,* FROM mytable) X WHERE myrow = @i; SET @i = @i + 1;END;	loop some command 10 times
SELECT *, ISNULL(parent,'') + '-' + child AS combikey INTO mytables FROM (SELECT ROW_NUMBER() OVER (ORDER BY parent) AS linenumber,* FROM (SELECT NULL AS parent ,* FROM (SELECT DISTINCT t1.parent AS child FROM mytable t1 LEFT OUTER JOIN mytable t2 ON t2.child = t1.parent WHERE t2.child IS NULL) X UNION SELECT parent,child FROM mytable) Y)Z	parent child tree 1 - mytable = parent,child. insert parents lines as NULLS and linenumber
SELECT t1.linenumber,t1.parent,t1.child,t1.combikey INTO #mytabless FROM #mytables t1 LEFT JOIN #mytables t2 ON (t1.child+t1.parent)= (t2.parent + t2.child) WHERE t2.linenumber>t1.linenumber UNION SELECT t1.linenumber,t1.parent,t1.child,t1.combikey FROM #mytables t1 LEFT JOIN #mytables t2 ON (t1.child+t1.parent)= (t2.parent + t2.child) WHERE t2.parent IS NULL	parent child tree 2 - mytabless , strip duplicate parent = child & child = parent
SELECT t6.linenumber AS ID,t7.linenumber AS IDparent,t6.child AS jobno INTO mytableult FROM #mytabless t6 JOIN (SELECT linenumber AS IDparent, child AS jobno ,* FROM (SELECT t1.linenumber,t3.parent,t3.child ,t3.combikey FROM #mytabless t1 CROSS APPLY (SELECT * FROM #mytabless t2 WHERE t1.child=t2.parent) t3 UNION SELECT 0 AS linenumber,t5.parent,t5.child,t5.combikey FROM #mytabless t5 WHERE parent IS NULL )x )t7 ON t6.combikey = t7.combikey	parent child tree 3 - line up ultimate parent/child table , ready for recursive sql
WITH CTE AS ( SELECT id, idparent, jobno, CONVERT(VARCHAR(MAX),jobno) AS ListJob FROM #mytableult WHERE idParent = 0 UNION ALL SELECT t.id, t.idparent, t.jobno, c.ListJob + '/' + CONVERT(VARCHAR(MAX),t.jobno) AS ListJob FROM #mytableult t INNER JOIN CTE c ON t.idParent = c.id) SELECT * FROM CTE ORDER BY jobno;	parent child tree 4 final - each child with all its parents loop recursively
SELECT mycolumn, ( mysecondcolumn+mythirdcolumn+myfourthcolumn)/3 FROM mytable	sum/ add-up second, third & fourth column - divide - average
SELECT IIF(mycolumn IS NULL,1, mycolumn)/ IIF(0=ISNULL(mysecondcolumn ,0),.001,mysecondcolumn ),* FROM mytable	divide 2 numbers in sql without errors
DECLARE @sql varchar(1000);DECLARE @columns varchar(75);DECLARE @text varchar(75);SET @columns = 'mycolumn , mysecondcolumn, mythirdcolumn';SET @text = '''mytext''';SET @sql = 'SELECT ' + @columns + ' FROM mytable WHERE mycolumn = ' + @text;EXEC (@sql)	execute sql ,insert dynamic list of columns, mycolumn, mysecondcolumn

save / append files [directory extension database insert]

SELECT * FROM mytable; OUTPUT TO 'c:\\muppix\\myfile.txt' FORMAT TEXT QUOTE '"' WITH COLUMN NAMES	save results to .txt in this directory (TIP: pls note there is no "|" with this command ) ie: ls -al >myfile.txt
SELECT * INTO #mynewtable FROM mytable -- #mytemptable only last during this SQL session	save results to temporary mytable
INSERT INTO mytable SELECT * FROM mysecondtable	insert lines of the result below end of .txt and save (even if it doesnt exist yet) ie: grep mytext * >>myfile.txt
SELECT * INTO mysecondtable FROM mytable -- new SQL table	save as text file for viewing in notepad *.txt
SELECT * INTO mysecondtable [IN myotherdatabase] FROM mytable -- new SQL table from another database	copy/save as text file
SELECT * FROM mytable; OUTPUT TO 'c:\\muppix\\myspreadsheet.csv' FORMAT TEXT QUOTE '"' WITH COLUMN NAMES	save results to excell/spreadsheet or msaccess database in this directory. TIP: ensure the columns have a delimiter such as "|"
SELECT * FROM mytable UNION ALL SELECT * FROM mysecondtable -- but make sure all colunmds are the same	insert .txt lines at end/below mysecondfile.txt and mysecondfile.txt (even if mysecondfile doesnt exist yet)
SELECT t1.*,t2.* FROM mytable t1 JOIN mysecondtable t2 ON t1.mycolumn = t2.mycolumn	insert mysecondfile after(to right of) . side by side as 2 columns with '|' as mydelimiter between files
SELECT t1.* , t2.* FROM mytable t1 INNER JOIN mysecondtable t2 ON t1.mycolumn = t2.mycolumn	insert after columns from mysecondfile, based on the begin column of each file. only select matching lines. excel VLOOKUP(A2,mysecondfile!A:B,2) TIP: ensure are linux files, ie: dos2unix myfile
SELECT * FROM mytable t1 CROSS APPLY (SELECT * FROM mysecondtable t2 WHERE t1.mycolumn = t2.mycolumn )X	insert after columns from mysecondfile, based on the begin column of each file. only select matching lines. excel VLOOKUP(A2,mysecondfile!A:B,2) TIP: ensure are linux files, ie: dos2unix myfile
SELECT mytable.mycolumn, mytable.mysecondcolumn, mysecondtable.mythirdcolumn,mysecondtable.myfourthcolumn FROM mytable LEFT JOIN mysecondtable ON mytable.mycolumn = mysecondtable.mycolumn	insert after columns from mysecondfile, based on the begin column of each file. include non-matching lines. excel VLOOKUP(A2,mysecondfile!A:B,2) TIP: ensure are linux files, ie: dos2unix myfile
SELECT mytable.* , mysecondtable.* FROM mytable INNER JOIN mysecondtable ON mytable.mycolumn = mysecondtable.mycolumn	insert after columns from mysecondfile, based on the begin column of each file. include non-matching lines, mydelimiter = '|' TIP: ensure are linux files, ie: dos2unix myfile
SELECT M.mycolumn,M.mysecondcolumn,D.mydatecolumn,D.mylastcolumn FROM mytable M CROSS APPLY( SELECT TOP 2 D.mycolumn,mydatecolumn,mylastcolumn FROM mysecondtable D WHERE M.mycolumn = D.mycolumn ORDER BY D.mydatecolumn DESC )D	select mycolumn and mysecondcolumn from mytable and lines of last (end) two dates for each mycolumn from mysecondtable
SELECT * FROM mytable t1 CROSS APPLY (SELECT * FROM mysecondtable t2 WHERE t1.mycolumn = t2.mycolumn) X	joins 2 files by key in begin column of both files,like excel VLOOKUP .txt may need dos2unix & must be sorted & only use 1st column to index
SELECT t1.*,t3.* FROM mytable t1 OUTER APPLY(SELECT TOP 1 * FROM mysecondtable t2 WHERE t1.mycolumn = t2.mycolumn ORDER BY mysecondcolumn) t3	select whole of mytable & joins only single lines from secondtable / file where it can (but not add extra lines from second table ie outer join) exactly like VLOOKUP, show all columns
	select whole of mytable & joins only single lines from secondtable / file where it can (but not add extra lines from second table ie outer join) exactly like VLOOKUP, show all columns

www.muppix.co linux basic navigation commands in the terminal window

CREATE DATABASE mydatabase	create database
USE mydatabase	goto the dataset where the data is, ie goto C: drive (mydrive) or USB on windows. TIP: for linux/Apple ,use mount to show all harddrive names
EXPLAIN mytable	all filesnames (including socalled hidden files) and time stamps in mydir- this directory only filenames & details, this directory only
DECLARE @SQL NVARCHAR(MAX) = ''; SELECT @SQL = @SQL + 'SELECT ''' + t1.name + ''' AS DatabaseName, t.name AS TableName, t2.name AS ColumnName, t.modify_date FROM ' + t1.name + '.sys.tables t INNER JOIN ' + t1.name + '.sys.columns t2 ON t2.object_id = t.object_id UNION ALL ' FROM sys.databases t1 WHERE t1.name IN('mydatabase'); SET @SQL = left(@SQL, LEN(@SQL) - 10); EXEC sp_executesql @SQL	select all filenames (& all its size/date information) in mydir & all sub directory, also select each subdirectory name with each file
SELECT * FROM mytable	select all lines of .txt ie: cat muppix.txt
SELECT TOP 10 * FROM mytable ORDER BY mycolumn ASC	begin 10 lines of file TIP: try all your commands on just these 20 lines ie: cat muppix.txt | head -20
SELECT TOP 10 * FROM mytable ORDER BY mycolumn DESC	end 10 lines of file TIP: use this subset of the file to try all your commands ie: cat muppix.txt | tail
SELECT SUBSTRING(mycolumn,2,8-2) FROM mytable	delete character(s) before 2, select between character 2 (second) and 88. delete after 88th (fixed)
SELECT LEFT( mycolumn,88) FROM mytable	only begin / less than 88 (fixed) characters ie: cat .txt | cut -c-88
SELECT COUNT(*) FROM mytable	how many lines in the list
SELECT COUNT(*) FROM mytable	how many lines in the list / ie: how many mytext found in myfile: cat myfile.txt | fgrep mytext | wc -lc
SELECT name, database_id, create_date FROM sys.databases	names of all hard-drives (mydrive) on this version of linux & size. TIP: goto using these harddrive names
SELECT RTRIM( LTRIM( REPLACE( REPLACE( mycolumn,' ' ,' ' ),' ' ,' '))) FROM mytable	delete/replace multiple/duplicate/consecutive spaces with single space/blank, also deletes begin spaces. easy to view ie: cat .txt | awk -v OFS=" " '$1=$1'
SELECT * FROM mytable ORDER BY CONVERT(binary(25),mycolumn) ASC	sort lines
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY mycolumn ORDER BY mycolumn ) AS myrowno FROM mytable )X WHERE myrowno =1	sort lines and then delete duplicate lines
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql ORDER BY execquery.last_execution_time DESC	history of 100 mycommands i recently used in this terminal

tools to help view the output

SELECT TOP 2 * FROM mytable ORDER BY mycolumn ASC	select beginning (fixed) and second lines
SELECT TOP 2 * FROM mytable ORDER BY mycolumn ASC	select beginning (fixed) and second lines
SELECT LEFT( mycolumn,2) FROM mytable	only select beginning and second characters. ie: cut -c-77 to quickly view text, beginning 77 characters (not wrap long lines)
SELECT T1.text FROM sys.dm_exec_cached_plans AS T2 CROSS APPLY sys.dm_exec_sql_text(T2.plan_handle) AS T1 WHERE T1.text LIKE N'%mytext%'	out of the 100 recent mycommands, select those with 'mytext'
SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS linenumber,* FROM mytable	insert linenumbers at beginning of line ie: find out linenumbers with 'mytext' : cat .txt| cat -n |fgrep 'mytext'

SQL table commands

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%mytable%'	schema all column information
USE mydatabase;SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='VIEW'	schema select all views
SELECT * FROM fn_my_permissions (NULL, 'DATABASE')	all sql permissions
USE mydatabase; SELECT T2.TABLE_CATALOG,T1.TABLE_SCHEMA ,T1.TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.VIEWS T1 JOIN INFORMATION_SCHEMA.COLUMNS T2 ON T2.TABLE_SCHEMA = T1.TABLE_SCHEMA AND T2.TABLE_NAME = T1.TABLE_NAME	select all columns & all views
USE [mydatabase] IF OBJECT_ID('vwmytable','v') IS NOT NULL DROP VIEW vwmytable; GO CREATE VIEW [vwmytable] AS SELECT TOP 100 * FROM mytable	create view
SELECT Col.COLUMN_NAME as myprimkey, * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col WHERE Col.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name AND Constraint_Type = 'PRIMARY KEY' AND Col.Table_Name like 'mytable'	select mytable primary key
SELECT object_name(parent_object_id), object_name(referenced_object_id), name FROM sys.foreign_keys WHERE parent_object_id = object_id('mytable')	select all foreign keys
ALTER TABLE mytable ADD PRIMARY KEY (mycolumn, mysecondcolumn)	insert primary key in mytable
SELECT mycolumn, mysecondcolumn FROM (SELECT 'mytext' AS mycolumn ,12 as mysecondcolumn UNION ALL SELECT 'any thing' AS mycolumn , 44 UNION ALL SELECT 'mytext' AS mycolumn , 11 )X	SQL sandbox, create temporary test table
DECLARE @mytabl TABLE (mycol varchar(50), mystring varchar(MAX)) INSERT INTO @mytabl (mycol, mystring )SELECT mycolumn as mycol , mysecondcolumn as mystring FROM mytable t2 SELECT * FROM @mytabl	insert temporary table
SELECT * INTO mynewtable FROM mytable	copy mytable
SELECT * INTO mynewtable FROM mytable WHERE 1 = 0	copy table structure based on mytable
EXEC sp_RENAME 'mytable.mycolumn', 'mysecondcolumn', 'COLUMN'	replace mycolumn name with mysecondcolumn
SELECT * FROM mytable t1 WHERE EXISTS (SELECT * FROM mysecondtable t2 WHERE t1.mycolumn = t2.mycolumn )	lines with matching mycolumn in mysecondtable
SELECT CONVERT(varchar, REPLACE( LTRIM(REPLACE( mycolumn ,'0',' ')) ,' ', '0')) ,* FROM mytable	convert/clean text mycolumn to numbers to join/link another numeric column
DECLARE @mytabl TABLE (mycol varchar(50), mystring varchar(MAX)) INSERT INTO @mytabl (mycol, mystring ) SELECT t2.mycolumn as mycol ,IIF(SUBSTRING(' '+t2.mycolumn+'|' ,iter.pos,1) BETWEEN '0' AND '9' ,SUBSTRING(' '+t2.mycolumn+ '|' ,iter.pos,1),' ') mystring FROM mytable t2, (SELECT id AS pos FROM T100 ) iter WHERE iter.pos <= 1+LEN(t2.mycolumn) SELECT mycol,REPLACE(STUFF((SELECT ' ' + [mystring] FROM @mytabl WHERE (mycol = t1.mycol) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') ,' ' ,'') AS mystrings FROM @mytabl t1	delete punctuation / numbers (ensure no duplicate mycolumn)
SELECT * FROM ( SELECT t1.*,t2.mymin,t2.mymax,t2.mysum,t2.myavg,t2.mycount FROM mytable t1 INNER JOIN ( SELECT mycolumn, MIN(mysecondcolumn) mymin, MAX(mysecondcolumn) mymax, SUM(mysecondcolumn) AS mysum ,AVG(mysecondcolumn) AS myavg ,COUNT(mysecondcolumn) AS mycount FROM mytable GROUP BY mycolumn) t2 ON t1.mycolumn = t2.mycolumn ) X order by mycolumn	group by mycolumn, yet show all lines and all columns, insert min max avg
SELECT * FROM ( SELECT t1.*,t2.mymax,t2.mybiggest, ROW_NUMBER() OVER (PARTITION BY t1.mycolumn ORDER BY t1.mycolumn DESC) mylines FROM mytable t1 INNER JOIN ( SELECT mycolumn, MAX(mysecondcolumn) mymax, SUM(mysecondcolumn) AS mybiggest FROM mytable GROUP BY mycolumn ) t2 ON t1.mycolumn = t2.mycolumn ) X WHERE mylines=1	group by mycolumn yet show all columns
SELECT * FROM ( SELECT t1.*,t2.mymax,t2.mybig ,ROW_NUMBER() OVER (PARTITION BY t1.mycolumn,t1.mysecondcolumn ORDER BY t1.mycolumn,t1.mysecondcolumn DESC) myrow FROM mytable t1 INNER JOIN ( SELECT mycolumn,mysecondcolumn, MAX(mythirdcolumn) mymax,SUM(mythirdcolumn) mybig FROM mytable GROUP BY mycolumn,mysecondcolumn ) t2 ON t1.mycolumn = t2.mycolumn AND t1.mysecondcolumn = t2.mysecondcolumn ) X WHERE myrow=1	group by 2 columns, ( mycolumn & mysecondcolumn ) & yet show all columns
;WITH mytmp AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY mycolumn ,mysecondcolumn ORDER BY mycolumn ,mysecondcolumn ASC) AS myrow FROM mytable) SELECT * FROM mytmp WHERE myrow=1	group by 2 columns, ( mycolumn & mysecondcolumn ) & yet show all columns
SELECT *, MAX(mysecondcolumn) OVER (PARTITION BY mycolumn) AS mymax, MIN(mysecondcolumn) OVER (PARTITION BY mycolumn) AS mymin ,SUM(mysecondcolumn) OVER (PARTITION BY mycolumn) as mysum ,AVG(mysecondcolumn) OVER (PARTITION BY mycolumn) AS myavg FROM mytable	insert column with maximum min average sum of mysecondcolumn within the group by of mycolumn
;WITH mytmp AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY mycolumn ORDER BY mycolumn ASC) AS myrow FROM mytable) SELECT * FROM mytmp WHERE myrow=1	group by mycolumn yet show all columns
SELECT mycolumn,MAX(mysecondcolumn) FROM mytable GROUP BY mycolumn	unique lines (delete duplicate) and also find the value in mysecondcolumn
SELECT t1.* FROM mytable t1 LEFT JOIN mytable t2 ON t1.mycolumn = t2.mycolumn AND t1.mysecondcolumn < t2.mysecondcolumn WHERE t2.mycolumn IS NULL	for each (group by) mycolumn , select maximum / minimum of mysecondcolumn, yet show all columns
SELECT t1.* FROM mytable t1 JOIN ( SELECT mycolumn, MAX(mysecondcolumn) AS mysecondcolumn FROM mytable GROUP BY mycolumn) AS t2 ON t1.mycolumn = t2.mycolumn AND t1.mysecondcolumn = t2.mysecondcolumn	for each (group by) mycolumn , select maximum / minimum of mysecondcolumn, yet show all columns
SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS mylines ,ROW_NUMBER() OVER (PARTITION BY mycolumn ORDER BY mycolumn) AS mygrlines ,RANK() OVER (ORDER BY mycolumn) AS myuniqlines ,DENSE_RANK() OVER (ORDER BY mycolumn) AS mydisnctlines,* FROM mytable	insert linenumber, based on groups and sorts
SELECT mycolumn, ROW_NUMBER() OVER(ORDER BY mycolumn) AS myid, RANK() OVER(ORDER BY mycolumn) AS myrank, DENSE_RANK() OVER(ORDER BY mycolumn) mydenserank FROM mytable	insert linenumber, linenumber group by mycolumn
SELECT mycolumn, mythirdcolumn, mythirdcolumn * 100.0 / COALESCE( NULLIF( SUM(mythirdcolumn) OVER (PARTITION BY mycolumn),0),1) AS mypcent FROM mytable	percent of total, grouped by mycolumn
SELECT ,CAST( COALESCE( NULLIF( LTRIM( RTRIM( SUBSTRING( mycolumn ,PATINDEX( '%[0-9]%',mycolumn +'0' ),999))) ,'') ,'0.0') AS NUMERIC) FROM mytable	select numbers out of mixed column of characters and numbers
SELECT * FROM mytable WHERE mycolumn LIKE '[A-Z]%[0-9]%'	begins with a letter before a number
SELECT * FROM (SELECT ROW_NUMBER() OVER( PARTITION BY mycolumn ORDER BY mycolumn DESC,mysecondcolumn DESC) AS myrowno,* FROM mytable)X WHERE myrowno= 1	select begin (first) line of greatest group of mycolumn & mysecondcolumn,same behavior as excel VLOOKUP (1st in group of secondtable, otherwise empty)
SELECT * FROM (SELECT ROW_NUMBER() OVER( PARTITION BY mycolumn ORDER BY mycolumn DESC,mysecondcolumn DESC) AS myrowno ,* FROM mytable )X WHERE myrowno<= 2	select begin (second) lines of greatest group of mycolumn & mysecondcolumn
SELECT X.* FROM mytable AS t1 CROSS APPLY (SELECT TOP 2 mycolumn, mysecondcolumn, mythirdcolumn FROM mysecondtable AS t2 WHERE t2.mycolumn = t1.mycolumn ORDER BY mycolumn DESC, mysecondcolumn DESC) AS X	select begin 2 lines of a joined table
SELECT * FROM mytable t1 JOIN mysecondtable t2 ON t1.mycolumn = t2.mycolumn WHERE periods = (SELECT MAX(periods) FROM mysecondtable t2 WHERE t1.mycolumn = t2.mycolumn)	join the most begin / recent of mysecondtable
SELECT t1.* ,t2.* FROM mytable AS t1 JOIN (SELECT * FROM ( SELECT ROW_NUMBER() OVER( PARTITION BY mycolumn ORDER BY mycolumn,mysecondcolumn ) AS myrownum ,* FROM mysecondtable) X WHERE myrownum = 1) t2 ON t2.mycolumn = t1.mycolumn	selects mytable & joins only single lines from secondtable / file and deletes lines in mytable if no join is found , excel VLOOKUP
SELECT * FROM (SELECT *,LAG (mycolumn,1) OVER (PARTITION BY mycolumn ORDER BY mysecondcolumn DESC) AS mytmp FROM mytable) X WHERE mytmp IS NULL	select begin (first) line of greatest group of mycolumn & mysecondcolumn
SELECT mycolumn,mydate,myvalue,LAG(myvalue,1) OVER (ORDER BY mycolumn,mydate) AS myprevvalue FROM mytable	previous month (mydate) amount for mycolumn
SELECT * FROM (SELECT ROW_NUMBER() OVER( PARTITION BY mycolumn ORDER BY mycolumn,mysecondcolumn DESC) AS myrowno,* FROM (SELECT mycolumn,mysecondcolumn FROM mytable GROUP BY mycolumn,mysecondcolumn ) Y)X WHERE myrowno= 2	select previous date within mycolumn for group of mycolumn mysecondcolumn
SELECT mycolumn,MAX(mysecondcoumn)FROM mytable GROUP BY mycolumn	biggest of mysecondcolumn in group by mycolumn
WITH cte AS (SELECT * , RANK() OVER (PARTITION BY mycolumn ORDER BY mysecondcolumn desc ) AS r FROM mytable) SELECT * FROM cte WHERE r=1 ORDER BY mycolumn ASC;	biggest / max of mysecondcolumn in group by mycolumn, show all columns
WITH cte AS (SELECT * , RANK() OVER ( PARTITION BY mycolumn ORDER BY mysecondcolumn asc) AS r FROM mytable) SELECT * FROM cte WHERE r = 1 ORDER BY mycolumn ASC;	smallest / min of mysecondcolumn in group by mycolumn, show all columns
WITH mynewtable AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY mycolumn ORDER BY mycolumn DESC,mysecondcolumn DESC) AS myrowno FROM mytable) SELECT * FROM mynewtable WHERE myrowno = 1	select begin (first) line of greatest group of mycolumn & mysecondcolumn
SELECT t1.*,t2.* FROM mytable AS t1 CROSS APPLY (SELECT TOP 1 * FROM mysecondtable AS X WHERE X.mycolumn = t1.mycolumn ORDER BY mysecondcolumn DESC, mythirdcolumn) AS t2	select only beginning (first) of joined group in mysecondtable, (only for lines that can be joined - inner join)
SELECT mycolumn, mythirdcolumn ,* FROM (SELECT mycolumn, mythirdcolumn, DENSE_RANK() OVER (ORDER BY mycolumn DESC) myrank FROM mytable) X WHERE myrank <=2	select lines of 2 most greater values grouped by mycolumn / begin top 2
SELECT mycolumn, mythirdcolumn ,* FROM (SELECT mycolumn, mythirdcolumn, DENSE_RANK() OVER (ORDER BY mycolumn ASC) myrank FROM mytable) X WHERE myrank <=2	select lines of 2 most smaller values grouped by mycolumn / begin top 2
SELECT mycur, SUM(mynumbs) AS myweight FROM (SELECT DISTINCT DENSE_RANK () OVER(ORDER BY t2.mydatecolumn) mywind ,t2.mydatecolumn mycur ,t1.mydatecolumn mylast90 ,t1.mythirdcolumn mynumbs FROM mytable t2, mytable t1 WHERE t1.mydatecolumn BETWEEN (t2.mydatecolumn-22) AND t2.mydatecolumn ) X GROUP BY mycur	moving average over last 2 days
SELECT mycolumn ,mysecondcolumn ,SUM(mythirdcolumn) AS mythird FROM mytable GROUP BY mycolumn ,mysecondcolumn UNION ALL SELECT NULL,NULL ,SUM (mythirdcolumn) FROM mytable	sum grouped by mycolumn, and insert the total below
SELECT customerid FROM orders GROUP BY customerid HAVING COUNT(DISTINCT itemno)=(SELECT COUNT(*) FROM items) ----	lines where a customer has 1 of every item
SELECT mycolumn,mythirdcolumn ,SUM(mythirdcolumn) OVER() AS mytot ,SUM(mythirdcolumn) OVER (PARTITION BY mycolumn) AS mytotgroupby ,SUM(mythirdcolumn) OVER (ORDER BY mycolumn) AS myruntotgroupby ,SUM(mythirdcolumn) OVER (ORDER BY mycolumn RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS myruntotal FROM mytable	sum / total replicated, total per group, running total
SELECT COALESCE(mycolumn / NULLIF(mynumber,0), 0) FROM mytable	divide by zero, can handle nulls
SELECT mycolumn,mycalc1,mycalc2 FROM mytable CROSS APPLY (SELECT 2 * CAST(mycolumn AS BIGINT)) t1(mycalc1) CROSS APPLY (SELECT mycalc1 + 1) t2 (mycalc2)	re use my own created column names
SELECT Id,Foo,Bar FROM mytable CROSS APPLY (VALUES(Foo1, Bar1), (Foo2, Bar2), (Foo3, Bar3)) t1 (Foo, Bar)	un pivot/ convert multiple columns foo1,bar1,foo2,bar2, etc split into a pair of column
WITH t1 AS (SELECT 1 AS id),t2 AS (SELECT 2 AS id) SELECT * FROM t1 UNION ALL SELECT * FROM t2 UNION ALL SELECT * FROM t1	multple cte using with
SELECT SUM(CASE WHEN mycolumn IS NULL THEN 1 ELSE 0 END) AS CountNulls ,COUNT(mycolumn) AS CountnonNulls FROM mytable	count number of nulls in column
SELECT COUNT(1) RecordCount,COUNT(DISTINCT mycolumn) RecordUnique, COUNT(1) -COUNT(mycolumn) RecordMissing, MIN(mycolumn) MinVal, MAX(mycolumn) MaxVal , AVG(mycolumn) AvgVal, SUM(mycolumn) TotalVal, NULL MinDateVal, NULL MaxDateVal FROM mytable	select min max unique count
SELECT SUM(mysecondcolumn) AS K , COUNT(DISTINCT mytable.mycolumn) AS uniq_mycolumn FROM mytable	distinct count unique
SET STATISTICS IO ON;SET STATISTICS TIME ON;SELECT * FROM mytable WHERE mysecondcolumn LIKE '%mytext%';SET STATISTICS IO OFF;SET STATISTICS TIME OFF;	cpu execution time
SELECT *,LAG (mycolumn,1) OVER (ORDER BY mycolumn) AS mylag FROM mytable	select line of previous date/month on the same line
SELECT * FROM (SELECT ROW_NUMBER() OVER( PARTITION BY mycolumn ORDER BY mycolumn,mysecondcolumn DESC) AS myrowno,* FROM (SELECT mycolumn,mysecondcolumn FROM mytable GROUP BY mycolumn,mysecondcolumn ) Y) X WHERE myrowno= 2	select the previous found different mysecondcolumn in the group of (mycolumn,mysecondcolumn) - the last time mysecondcolumn changed
SELECT *,DATEADD(s,-1, DATEADD(mm, DATEDIFF(m,0, mydate),0)) FROM mytable	end of last month, days date
SELECT *,EOMONTH ( mydate, -1 ) AS 'Last Month' FROM mytable	end of last month, days date
SELECT *, DATEADD(mm, DATEDIFF(m,0, mydate )+1,-1) FROM mytable	end of current month days date
SELECT *, EOMONTH (mydate) AS 'This Month' FROM mytable	end of current actual month days date
SELECT EOMONTH(CONVERT(DATETIME, LEFT(CONVERT(VARCHAR(6),mycolumn)+'01', 8))) FROM mytable	end of current month days date , but for a yyyymm date, ie 202307
	convert date to yyyymm
SELECT CONVERT(INT,ROUND(.01*CONVERT(int, CONVERT(VARCHAR(10),DATEADD(s,-1, DATEADD(mm, DATEDIFF(m,0, CONVERT(DATETIME, LEFT(CONVERT(VARCHAR(6),mycolumn)+'01', 8))),0)), 112)),0)) FROM mytable	last/previous month (before) using integer date yyyymm ie 201801 return 201712
SELECT *, DATEADD(s,-1, DATEADD(mm, DATEDIFF(m,0, mydate)+2,0)) FROM mytable	end of next month , days date
SELECT *, EOMONTH ( mydate, 1) AS 'Next Month' FROM mytable	end of next month , days date
	begin of month , days date
	add 2 days after date
	add 2 days after date
	number of days from 19000101
	convert/ cast excel number of days from 19000101 to date
	2 days before to date
	select day month year from date
	select only working days between begin mydate and end mydate
SELECT CONVERT(varchar(8), mydatecolumn, 112) as mycol,* from mytable	convert date to yyyymmdd
WITH X (mydate,cnt) AS (SELECT DATEADD(d,-(DATEPART(dy,GETDATE())-1),GETDATE()), 1 FROM ( SELECT 1 AS mycol) Y UNION ALL SELECT DATEADD(m,3,mydate) ,cnt +1 FROM X WHERE cnt+1 <=4) SELECT mydate FROM X	quarterly dates from today
SELECT CONVERT(DATE, LEFT(CONVERT(VARCHAR(8),mycolumn), 8)) FROM mytable	convert integer yyyymmdd to sql date
SELECT STUFF(STUFF(mycolumn, 3, 0, '/'), 6, 0, '/') AS [DD/MM/YYYY] FROM mytable	format/convert date mmddyyyy as 07042017 to 07/04/2017
DECLARE @Schema NVARCHAR(100) = 'dbo'; DECLARE @Table NVARCHAR(100) = 'mytable'; DECLARE @sql NVARCHAR(MAX) =''; IF OBJECT_ID ('tempdb..#Nulls') IS NOT NULL DROP TABLE #Nulls; CREATE TABLE #Nulls (TableName sysname, ColumnName sysname , ColumnPosition int ,NullCount int , NonNullCount int); SELECT @sql += 'SELECT '''+TABLE_NAME+''' AS TableName , '''+COLUMN_NAME+''' AS ColumnName, '''+CONVERT(VARCHAR(5),ORDINAL_POSITION)+''' AS ColumnPosition, SUM(CASE WHEN '+COLUMN_NAME+' IS NULL THEN 1 ELSE 0 END) CountNulls , COUNT(' +COLUMN_NAME+') CountnonNulls FROM '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+';'+ CHAR(10) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @Schema AND TABLE_NAME = @Table; INSERT INTO #Nulls EXEC sp_executesql @sql; SELECT * FROM #Nulls;	how many nulls
SELECT DISTINCT o.name AS Object_Name,o.type_desc,m.definition FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like '%mytext%' AND o.type_desc = 'SQL_STORED_PROCEDURE' -- only shows 1st 4000 chars!	search stored procedures
CREATE TABLE #ProcSearch ([databaseNAme] Varchar(100),ProcName VARCHAR(128))	search stored procedures
SELECT SCHEMA_NAME(O.SCHEMA_ID) [SCHEMA_NAME], O.NAME, OBJECT_DEFINITION(OBJECT_ID) TEXT FROM SYSCOMMENTS AS C INNER JOIN SYS.OBJECTS AS O ON C.ID = O.[OBJECT_ID] INNER JOIN SYS.SCHEMAS AS S ON O.SCHEMA_ID = S.SCHEMA_ID WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%mytext%' ORDER BY SCHEMA_NAME(O.SCHEMA_ID), O.NAME -- only shows 1st 4000 chars!	search stored procedures
SELECT name, type FROM dbo.sysobjects WHERE type IN ('P','FN','IF','TF') ORDER BY type, name	select all stored procedure names

terminal keystrokes in linux window