CREATE TABLE #tmpTable ( COL1 int, COL2 int, COL3 nvarchar(max), COL4 nvarchar(max), COL5 bit ) INSERT INTO #tmpTable exec SpGetRecords ‘Params’ NOTE: The columns of #tmpTable must be the same as SpGetRecords. Otherwise, there will be a problem. If there are no parameters in the stored procedure, you don’t need to use ‘Params’.
The OUTPUT clause was introduced in SQL Server 2005. The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE, or DELETE statement. It even supports a MERGE statement, which was introduced in SQL Server 2008 version. The OUTPUT clause has access to two temporary or in-memory SQL tables, INSERTED […]
Query syntax: var count = (from t in context.MyTable where t.Id == @Id select t).Count(); Method syntax: var count = context.MyTable .Where(t => t.Id == @Id) .Count() Both generate the same SQL query.
If you have two tables A and B, both with column C, here are the records, which are present in the table A but not in B: SELECT A.* FROM A LEFT JOIN B ON (A.C = B.C) WHERE B.C IS NULL To get all the differences with a single query, a full join must be used, like this: […]
To create a single string from multiple rows in MySQL, you can use the GROUP_CONCAT function. This function allows you to concatenate values from multiple rows into a single string, separated by a specified delimiter. Here is an example of how you can use this function: SELECT GROUP_CONCAT(column_name ORDER BY column_name ASC SEPARATOR ‘,’) FROM […]
In SQL Server 2012+, you can use OFFSET…FETCH. The below query will help you to get 2nd row. But with little changes, you can get the ‘n’th row as well. SELECT <column(s)> FROM <table(s)> ORDER BY <sort column(s)> OFFSET 1 ROWS — Skip this number of rows FETCH NEXT 1 ROWS ONLY; — Return this […]
SELECT (SCHEMA_NAME(A.schema_id) + ‘.’ + A.Name) AS TableName , SUM(B.rows) AS RecordCount FROM sys.objects A INNER JOIN sys.partitions B ON A.object_id = B.object_id WHERE A.type = ‘U’ GROUP BY A.schema_id, A.Name ORDER BY RecordCount DESC
SELECT m1.Column1, m1.Column2, m1.Column3, r.MaxTime FROM ( SELECT Column1, MAX(TimeColumn) as MaxTime FROM MyTable GROUP BY Column1 ) r INNER JOIN MyTable m1 ON m1.Column1= r.Column1AND m1.TimeColumn = r.MaxTime
STUFF((SELECT ‘, ‘+ Column1 FROM Table1 FOR XML PATH(”)),1,1,”) AS ConcatenatedColumn StudentName ————- Mary John Sam Alaina Edward Result: Mary, John, Sam, Alaina, Edward
WITH Records AS (SELECT ROW_NUMBER() OVER(ORDER BY ID_of_your_SQLTable) AS ‘ROW’, * FROM yourSQLTable) SELECT * FROM Records WHERE ROW = X –You can change X with the number you wanna get (For Example ROW = 5 will bring the 5th record)
<Columns> <asp:TemplateField HeaderText=”RowNumber”> <ItemTemplate> <%# Container.DataItemIndex + 1 %> </ItemTemplate> </asp:TemplateField> … </Columns>