Import Data from EXCEL

How to Import Data from EXCEL to Destination

Import data from excel through wizard 

Step 1: Choose your database where to import data -->Tasks-->Import Data

Step 2: Click Next

Step 3: Choose the Data source as a Microsoft Excel and browse the file path as well as choose the Excel Version click next

Here we have the options to get the columns Dynamically (like First row has column names) or Manually we have to provide

Step 4:Now choose our destination and Authentication type also database -->click Next

Step 5: Choose either option click next

Step 6: Select the sheet where you have a required data then click next

Step 7:Here we have the options to choose either Package or run immediately 

Run immediately is a process which retrieves the data instantly 

If it is a package we can run package along with configuration file at any environment 

Step 8: Close 

Backup, Restore, and Move the SSIS Catalog

Backup, Restore, and Move the SSIS Catalog

For full details go through the web page

http://technet.microsoft.com/en-us/library/hh213291.aspx

Cannot drop database [SSISDB] because it is currently in use.

USE [master]
GO

/****** Object:  Database [SSISDB]    Script Date: --/--/20-- 12:53:55 PM ******/

alter database [SSISDB] set single_user with rollback immediate


 drop database [SSISDB]

Restore database [SSISDB] 

USE [master]
GO

/****** Object:  Database [SSISDB]    Script Date: 3/10/2014 12:53:46 PM ******/
CREATE DATABASE [SSISDB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf' , SIZE = 40960KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf' , SIZE = 3456KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [SSISDB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SSISDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [SSISDB] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_NULLS OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_PADDING OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [SSISDB] SET ARITHABORT OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [SSISDB] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [SSISDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [SSISDB] SET CURSOR_DEFAULT  GLOBAL
GO

ALTER DATABASE [SSISDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [SSISDB] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [SSISDB] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [SSISDB] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [SSISDB] SET  DISABLE_BROKER
GO

ALTER DATABASE [SSISDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [SSISDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [SSISDB] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [SSISDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [SSISDB] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [SSISDB] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [SSISDB] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [SSISDB] SET RECOVERY FULL
GO

ALTER DATABASE [SSISDB] SET  MULTI_USER
GO

ALTER DATABASE [SSISDB] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [SSISDB] SET DB_CHAINING OFF
GO

ALTER DATABASE [SSISDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO

ALTER DATABASE [SSISDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO

ALTER DATABASE [SSISDB] SET  READ_WRITE
GO

MERGE JOIN

MERGE JOIN IN SQL SERVER

MERGE INTO EMPP as table1 USING (select * from EMP) as table2  ON table1.EMPNO=table2.EMPNO
WHEN MATCHED THEN UPDATE SET table1.ENAME=table2.ENAME,table1.JOB=table2.JOB,table1.MGR=table2.MGR,table1.HIREDATE=table2.HIREDATE,table1.SAL=table2.SAL,table1.COMM=table2.COMM,table1.DEPTNO=table2.DEPTNO
WHEN NOT MATCHED THEN
INSERT VALUES(table2.EMPNO,table2.ENAME,table2.JOB,table2.MGR,table2.HIREDATE,table2.SAL,table2.COMM,table2.DEPTNO);

/***PRINT THE DEPT WISE SALRIES WHOSE SALARY IS GRATER THAN DEPT WISE AVERAGE SALARY***/
WITH X AS
(
SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO
)SELECT * FROM X
INNER JOIN EMP ON X.DEPTNO=EMP.DEPTNO WHERE SAL>AVGSAL