Question. Can an INSERT query return a value?

Answer: Yes

SQL Query

SQL
insert into DS_UserTypes (UserTypeName,[Description])  
output inserted.UserTypeid,inserted.UserTypeName values  
('TeamLead','Nothing')

user typeid

C# Code

SQL
string Query = "insert into DS_UserTypes (UserTypeName,[Description]) " + "output inserted.UserTypeid,inserted.UserTypeName values " + "('TeamLead','Nothing')";
con.Open();
SqlCommand cmd = NEW SqlCommand(Query, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()) { Console.WriteLine("User type Id :" + rdr [ 0 ].ToString());
Console.WriteLine("User type name :" + rdr [ 1 ].ToString());
}

Question. How to determine the Last Row identity?

Answer

SQL
select distinct @@IDENTITY 'Last Row Id' from DS_UserTypes  
select * from DS_UserTypes

Output

last row id

Question. How to get the latest rows against an attribute in a table?

Answer

SQL
with budget as (select RANK()over(partition by uniquematterid,assignmentid  
order by BudgetApprovedDate desc) as [Rank],  
* from AS_Budget )  
  
select [RANK],Budget_ID 'Id',MatterId 'TaskId',LawfirmID 'VendorId'  
,LeadLawyerId 'PersonInCharge',BudgetApprovedDate 'ApprovedDate'  
from budget where MatterId=2

Query output

rank

From this, we can see that the latest approved work id has the rank 1.

Now we can re-write our query as.

SQL
select [RANK],Budget_ID 'WorkId',MatterId 'TaskId',LawfirmID 'VendorId'  
,LeadLawyerId 'PersonInCharge',BudgetApprovedDate 'ApprovedDate'  
from budget where [Rank]=1

Then it will return only the latest row of data against each attribute in the partition by clause.

Question. Update the table with a Join query.

Answer

Before updating

user id

SQL
select * from DS_User

After updating

SQL
update a set a.active='False' from DS_User a inner join DS_UserTypes b on a.UserType=b.UserTypeid  
where b.UserTypeid=5  
  
select * from DS_User

user id after update

Question. How to determine whether a table exists in the database?

Answer

SQL
Sp_tables ‘%table_name%’

For example

SQL
Sp_tables '%ds_%'

table

Question. How to determine a procedure using a Query?

Answer

SQL
sp_helptext ‘Procedure_name’

For example

SQL
Sp_ helptext 'GetImageID'

create procedure

Question. How do we determine whether a specific table is used in any stored procedures?

Answer

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN

sysobjects so on sc.id=so.id WHERE sc.text LIKE '%table_name%'

For example

SQL
SELECT DISTINCT so.name FROM syscomments sc INNER JOIN  
sysobjects so on sc.id=so.id WHERE sc.text LIKE '%users%'

ASUsetActivityReport

Summary

In this article, we learned about some valuable commands in SQL.