Get the last inserted row ID

Method 1 

INSERT INTO dbo.YourTableName (Columns.......) VALUES (...........)

SELECT SCOPE_IDENTITY() 

Method 2 

DECLARE @id int 

INSERT INTO dbo.YourTableName (Columns.......) VALUES (...........)

SET @id=@@identity

SELECT @id 

UPDATE from SELECT using SQL

UPDATE
    TableName1
SET
    TableName1.col1 = TableName2.col1,
    TableName1.col2 = TableName2.col2
FROM
    TableName1
INNER JOIN
    TableName2
ON
    TableName2 = TableName2.id

Find Column From All Tables of Database

USE <Database>
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%<Field Name>%'
ORDER BY schema_name, table_name;

Ex:
----
USE DBTest
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name; 

--------------------------------------------------------------------------
 table_name  |  schema_name  |  column_name
-------------------------------------------------------------------------
Employee   |   dbo              |     EmployeeID
-------------------------------------------------------------------------

Split FirstName, Middle Name, Last Name use comma

DECLARE @FirstName as nvarchar(max),@LastName as nvarchar(max),@MiddleName as nvarchar(max),@FullName as nvarchar(max)

Set @FullName= 'Ram,Kumar,Siva'

Set @FirstName =(select SUBSTRING(@FullName,1,(CHARINDEX(',',@FullName,1)-1)))

Set @LastName=(select reverse(SUBSTRING(reverse(@FullName),1,(CHARINDEX(',',reverse(@FullName),1)-1))) )


Set @MiddleName= (SELECT SUBSTRING(@FullName,LEN(SUBSTRING(@FullName,1,(CHARINDEX(',',@FullName,1)-1)))+2,(CHARINDEX(',',@FullName,LEN(SUBSTRING(@FullName,1,(CHARINDEX(',',@FullName,1)-1)))+2)) - (LEN(SUBSTRING(@FullName,1,(CHARINDEX(',',@FullName,1)-1)))+2)))



Print @FirstName
Print @LastName
Print @MiddleNam

Get values from a DOT,comma separated value in SQL

Get first or second values from a DOT, comma separated value in SQL

DOT
-----
select cast(left(FiledName,CHARINDEX('.',FiledName)-1) AS INT)FirstValue
FROM    TableName1 where [Id]=193012

comma 

 
select cast(left(FiledName,CHARINDEX(',',FiledName)-1) AS INT)FirstValue
FROM    TableName1 where [Id]=193012

Copy data from one table onto another table

Copy data from one table onto another table 

UPDATE Table2 
SET Table2 .col1 = Table1.col1, 
Table2 .col2 = Table1.col2,
    Table2 .col3 = Table1.col3,
...
FROM Table1, Table2
WHERE Table1.id = Table12.id

Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE


Method 1 : INSERT INTO SELECT

----Create
Table2 CREATE TABLE Table2(FirstName VARCHAR(100), LastName VARCHAR(100))
 
----INSERT INTO Table2 using SELECT
INSERT INTO Table2(FirstName, LastName)
SELECT FirstName, LastName
FROM Table1
WHERE [id]= 2

----Verify that Data in Table2
SELECT FirstName, LastName
FROM Table2


Method 2 : SELECT INTO 


----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO Table2
FROM Table1
WHERE [id] = 2

----Verify that Data in TestTable
SELECT FirstName, LastName
FROM Table2


Month / Year base search use Sql query

Month / Year base search use Sql query

SELECT * FROM TableName
   WHERE MONTH(<dataFiled>) = 2 AND YEAR(<dataFiled>) = 2013

Ex
SELECT * FROM  tblProcess
   WHERE MONTH(UserProcessDate) = 2 AND YEAR(UserProcessDate) = 2013

Select count(*) from multiple tables

Select count(*) from multiple tables

Select
case when a.result=0
then 1
else
0
end as Count_of_Table
from
(select COUNT(*) as result
from Table1
full outer join Table2
on Table1.ID=Table2.ID
where Table1.id=3 or Table2.ID=3)AS a

How to Get Instance Name from T-SQL (get server Name, and more details)

How to Get Instance Name from T-SQL (get server Name, and more details)

1. Select Case 
        When SERVERPROPERTY ('InstanceName') Is Null Then @@SERVERNAME
        Else SERVERPROPERTY ('InstanceName')
        End   (or)  select convert(nvarchar(128), serverproperty('servername'));
    2. sp_helpserver
    3. select @@servername
    4. select * from SYS.SYSSERVERS
    5. select * FROM SYS.SERVERS

***(server_id,name,product,provider,data_source,location,provider_string,catalog,connect_timeout,query_timeout,is_linked,is_remote_login_enabled,is_rpc_out_enabled,is_data_access_enabled,is_collation_compatible,uses_remote_collation,collation_name,lazy_schema_validation,is_system,is_publisher,is_subscriber,is_distributor,is_nonsql_subscriber,is_remote_proc_transaction_promotion_enabled,modify_date)

Remove / Delete Linked Server

sp_dropserver '<linkedserverName>'
Ex : sp_dropserver 'MSAccess'

Add Linked Server / Creating Linked Server & using OPENQUERY

Ref: http://msdn.microsoft.com/en-us/library/aa259589%28v=sql.80%29.aspx
Solutions :   

Syntax
 sp_addlinkedserver [ @server = ] 'server'      // Remote Computer Name
      [ , [ @srvproduct = ] 'product_name' ]        // Not Needed
    [ , [ @provider = ] 'provider_name' ]            // SQL Server Driver
    [ , [ @datasrc = ] 'data_source' ]                  // Server Name And Instance
    [ , [ @location = ] 'location' ]
    [ , [ @provstr = ] 'provider_string' ]
    [ , [ @catalog = ] 'catalog' ]

Example 1 : (Creating Linked server use excel 2003)
 EXEC sp_addLinkedServer 
    @server= 'Xls_2003',
    @srvproduct = 'Jet 4.0',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'E:\SQL Server\LinkedServer2003.xls',
    @provstr = 'Excel 5.0; HDR=Yes';
GO

Query
1.SELECT * FROM OPENQUERY (XLS_2003, 'Select * from [Sheet1$]')
2. SELECT * FROM XLS_2003...[Sheet1$]
 
Example 2 : (Creating Linked server use excel 2007)
 EXEC sp_addLinkedServer 
    @server= 'Xlsx_2007',
    @srvproduct = 'ACE 12.0',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'E:\SQL Server\LinkedServer2007.xlsx',
    @provstr = 'Excel 12.0; HDR=Yes';
GO

Query
1.SELECT * FROM OPENQUERY (XLS_2007, 'Select * from [Sheet1$]')
2. SELECT * FROM XLS_2007...[Sheet1$]


Example 3 : (Creating Linked server use excel 2010)

***If use 2010 excel add "MS Access Database Engine"

 EXEC sp_addLinkedServer 
    @server= 'XLSX_2010',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'E:\SQL Server - Blogs\LinkedServer2010.xlsx',
    @provstr = 'Excel 12.0; HDR=Yes';
GO

Query
1.SELECT * FROM OPENQUERY (XLSX_2010, 'Select * from [Sheet1$]')
2. SELECT * FROM XLSX_2010...[Sheet1$]
 
*** use : Enable advanced options 


Example 2 : (Creating Linked server use MSAccess) 
 EXEC sp_addLinkedServer 
    @server= 'MSAccess',
    @srvproduct = 'OLE DB Provider for Jet',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = N'E:\SQL Server - Blogs\LinkedServer.mdb',
GO

Set the maximum amount of memory to 64 MB:

use master   // master - Database Name
EXEC sp_CONFIGURE 'max server memory (MB)', 64
RECONFIGURE WITH OVERRIDE 

SQL SERVER – Enable CLR using T-SQL script Error: linked server "(null)" / Enable advanced options:

Solutions : 

Before doing any .Net coding in SQL Server you must enable the CLR. In SQL Server 2005, the CLR is OFF by default.
This is done in an effort to limit security vulnerabilities. Following is the script which will enable CLR.

 EXECUTE
Solutions :
(You may need to execute the following SQL Statements to configure the Linked Server initially)
EXECUTE sp_CONFIGURE 'show advanced options', 1 RECONFIGURE with Override
EXECUTE sp_CONFIGURE 'Ad Hoc Distributed Queries', 1 RECONFIGURE with Override

or

EXEC sp_CONFIGURE 'show advanced options' , '1';
GORECONFIGURE;
GO
EXEC sp_CONFIGURE 'clr enabled' , '1'
GO
RECONFIGURE;
GO

How to get all field names & Table schema in a table using SQL query

Solutions :
select [column_name],* from information_schema.columns
 where table_name = 'Table name' order by [column_name]

List All Tables of Database

Solutions :  1

                       Use Database name
                 GO    
                       SELECT [name] FROM sys.Tables Order By name asc

                      (OR)
                SELECT [name] FROM sys.tables WHERE is_ms_shipped=0 ORDER BY name ASC

Solutions :  2
MS access 

SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Name Not Like 'MsyS*' AND MSysObjects.Type=1 ORDER BY MSysObjects.Name

How to get table structure using query?

Solutions : 
sp_help tableName;

Ex:  Sp_help hemp; // hemp   is name of Table