Handy Code for SQL Server

Example: How to copy One table to another Table ( Both Data with its Structure)

Code:

<span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> INTO </span><span class="typ">DestinationTable</span><span class="pln"> FROM </span><span class="typ">SourceTable</span><span class="pln">

</span>

Example: How to create a data like a table

Code:

<span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> FROM</span><span class="pun">(</span><span class="pln">
SELECT </span><span class="lit">1</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> ID</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rambo'</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Name</span><span class="pln">
 UNION
SELECT </span><span class="lit">2</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> ID</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Azmi'</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Name</span><span class="pln">
</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">TableName</span><span class="pln">
</span>

Example: How to create a data like above and insert into in a table

Code:

<span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> INTO </span><span class="typ">DestinationTable</span><span class="pln"> FROM </span><span class="pun">(</span><span class="pln">
SELECT </span><span class="lit">1</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> ID</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Sanjay'</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Name</span><span class="pln">
 UNION
SELECT </span><span class="lit">2</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> ID</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rumman'</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Name</span><span class="pln">
</span><span class="pun">)</span><span class="pln"> </span><span class="typ">SourceTable</span><span class="pln">

</span>

Example:

Code:

<span class="pln">

 declare </span><span class="lit">@var</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'1,3'</span><span class="pln">  
 declare </span><span class="lit">@query</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">500</span><span class="pun">)</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'
 select * from
(
SELECT  Dept, SUM(Salary) as salarySum, ROW_NUMBER() OVER(ORDER BY Dept) AS Row_Number  FROM EmpSalary GROUP BY  Dept
)ta where ta.Row_Number in ('</span><span class="pun">+</span><span class="pln"> </span><span class="lit">@var</span><span class="pln"> </span><span class="pun">+</span><span class="str">')'</span><span class="pln">
</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">@query</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> qry

</span>

Example:

Code:

<span class="pln">

 declare </span><span class="lit">@var</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'1,3'</span><span class="pln">  
 declare </span><span class="lit">@query</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">500</span><span class="pun">)</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'
 select * from
(
SELECT  Dept, SUM(Salary) as salarySum, ROW_NUMBER() OVER(ORDER BY Dept) AS Row_Number  FROM EmpSalary GROUP BY  Dept
)ta where ta.Row_Number in ('</span><span class="pun">+</span><span class="pln"> </span><span class="lit">@var</span><span class="pln"> </span><span class="pun">+</span><span class="str">')'</span><span class="pln">
</span><span class="kwd">exec</span><span class="pun">(</span><span class="lit">@query</span><span class="pun">)</span><span class="pln">

</span>

Example: Core Code

<span class="pln">
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">All</span><span class="pln"> the databases that are present
</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> sys</span><span class="pun">.</span><span class="pln">databases

</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> sys</span><span class="pun">.</span><span class="pln">database_files
</span><span class="kwd">select</span><span class="pln"> physical_name </span><span class="kwd">from</span><span class="pln"> sys</span><span class="pun">.</span><span class="pln">database_files
</span>