Category: SQL Server

Import Export wizard in SQL Server step by step

sql2008

After long time I have decided to write an article again. Hopefully they will be useful for you. Today I want to write about one of a very useful feature in SQL Server which is Import/Export. This functionality is very useful when you have any kind of structured data and you need to push it to your SQL server databases or on any situation that you need to share your data from SQL Server databases with specific format to others. The SQL Server Import Export wizard is based on SQL Server Integration Service (SSIS) but it is very simple and you do not need to have any experience with SSIS to use it. In below I am going to share how you can use SQL Server Import Export via SQL Server Management Studio.

Import Wizard: For this case we are going to push ImportSample.csv file to our CodingTips_DB database. This database has “tbl_ImportExport” table for this purpose.

If you need any help to create database or table you can find useful information in previous articles. Create Database  Create Table

Step 1: First of all you must connect to database server and select database.

ImportExport1

Step 2: Right click on the database and select Import option under Tasks.

ImportExport2

Step 3: Click Next button on the first page.

ImportExport3

Step 4: Choose a Data Source page is provides variety of different data sources which can be selected to import data from SQL Server, Microsoft Excel, Flat File and etc. Based on data or file that you want to import to your database you can choose one of the available data sources from the list. For our scenario we must select Flat File Source from drop-down list box as shows in below picture because we have a csv file to import to our database. You can use Brows button to find your file. After that all columns are available in Columns tab to check. In Advanced tab you can change length of columns or data type and etc. Preview tab contains sample of data to check.

Column name in the first data row option must checked if your file has column name in first row.

Text qualifier text box must be populated by relevant character if the data in file a rounded by specific character like double or single quotes.

Step 5: Choose a Destination page provides variety of destination same as source. In our scenario we choose “Microsoft OLE DB Provider for SQL Server” and enter necessary information to connect to our database server. After that select the database from drop-down list.

ImportExport5

Step 6: In this step destination table must be selected otherwise the new table will be created by wizard based on the information provided by you and source file data attributes. Edit Mappings window provides some more information and options to make sure mappings, data types and length of columns are correct. Furthermore you are able to select more options in this page to control your destination tables behavior.

Step 7: In Review Data Type Mapping page you can control the wizard behavior in case of any error. You are able to fail execution in case of error or truncation. All below warnings are related to type mismatch in our scenario, If wizard will not convert them automatically or you could not fix them by changing data types in previous step you must save the wizard as a SSIS package in next step and manipulate the package via data tool.

ImportExport7

Step 8: In this page you can run your wizard immediately or save it as SSIS package and manipulate it in data tool.

ImportExport8

Step 9: Finally, you can get the execution report in this page.

ImportExport9

Advertisement

How to use OFFSET and FETCH NEXT ROWS in SQL Server 2012?

sql2008

--SQL Server 2012
WITH CTE_SoldProduct
AS
(
SELECT SUM(Quantity) QNT,ProductID FROM dbo.TBL_Sales
GROUP BY ProductID
)

SELECT 
      P.NAME 
      ,P.Price
      ,SP.QNT
    FROM CTE_SoldProduct SP
    INNER JOIN dbo.TBL_Product p ON SP.ProductID = p.ID
ORDER BY SP.QNT DESC
offset 1 rows FETCH NEXT 2 ROWS ONLY;

--SQL Server 2008
 WITH CTE_SoldProduct
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY SUM(Quantity) DESC) AS num, SUM(Quantity) QNT,ProductID FROM dbo.TBL_Sales
GROUP BY ProductID
)

SELECT 
      P.NAME 
      ,P.Price
      ,SP.QNT
    FROM CTE_SoldProduct SP
    INNER JOIN dbo.TBL_Product p ON SP.ProductID = p.ID
WHERE num >=2

Cursor in SQL Server

sql2008

SQL Server Cursor is a row base operator but it does not have good performance

because it create different execution plan per each rows, personality

I do not like use this operator so always I look for standards operators same as JOIN,SELECT,GROUP,…

and Loop operators instead of cursor.

By the way few times the cursor is useful;

please pay attention to below example:

Simple Sample:

USE Arm_DB 
GO

DECLARE @ProductID INT
DECLARE @ProductName NVARCHAR(50)
DECLARE @ProductPrice DECIMAL(10,2)

DECLARE Product_Cursor CURSOR FOR 
                                SELECT ID,NAME,Price 
                                    FROM dbo.TBL_Product

OPEN Product_Cursor FETCH FROM Product_Cursor INTO  @ProductID
                                                   ,@ProductName
                                                   ,@ProductPrice
    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @ProductName ProductName
             ,SUM(Quantity)*@ProductPrice AS TotalSales 
            FROM dbo.TBL_Sales
            WHERE ProductID = @ProductID

        FETCH FROM Product_Cursor INTO @ProductID
                                      ,@ProductName
                                      ,@ProductPrice  
    END      
CLOSE Product_Cursor
DEALLOCATE Product_Cursor

Optimal Option: Use JOIN and GROUP BY instead of CURSOR 

SELECT 
      P.NAME AS ProductName 
     ,SUM(S.Quantity) * P.Price AS TotalSales
 FROM dbo.TBL_Sales S
  INNER JOIN dbo.TBL_Product P ON S.ProductID = P.ID
GROUP BY P.NAME,P.Price

Useful sample: Get Backup from all user’s databases in SQL Server instance

USE Master 
Go

DECLARE @Path NVARCHAR(600) = 'C:\Arman\Mine\MyBlog\CodingTips\DBbackup\'
DECLARE @FileDate NVARCHAR(50) = CONVERT(NVARCHAR,GETDATE(),112)
DECLARE @DBname NVARCHAR(50) = ''
DECLARE @FileName NVARCHAR(1000) = ''

DECLARE DB_Cursor CURSOR For 
                            SELECT name 
                            FROM sys.databases  
                            WHERE database_id > 4 --Start From User Database

OPEN  DB_Cursor FETCH NEXT FROM DB_Cursor INTO @DBname
     WHILE @@FETCH_STATUS = 0
      BEGIN

        SET @FileName = @Path + @DBname + '_' + @FileDate + '.Bak'    
        BACKUP DATABASE @DBName TO DISK = @FileName    
        FETCH NEXT FROM DB_Cursor INTO @DBname         
      END 
CLOSE DB_Cursor
DEALLOCATE DB_Cursor

How to create table in SQL Server

sql2008

USE Arm_DB
Go

 IF (OBJECT_ID('TBL_Product','U') IS NOT NULL)
   DROP TABLE TBL_Product

Go

CREATE TABLE TBL_Product
(
ID INT IDENTITY(1,1)
,NAME NVARCHAR(50)
,Price DECIMAL(10,2)
,[Desc] NVARCHAR(MAX)
,InsertedDate DATETIME DEFAULT SYSDATETIME()
,MOdifiedDate DATETIME DEFAULT SYSDATETIME()
CONSTRAINT PK_TBL_Product PRIMARY KEY (ID)
)

GO 

 IF (OBJECT_ID('TBL_Sales','U')IS NOT NULL )
    DROP TABLE TBL_Sales

CREATE TABLE TBL_Sales
(
ID INT IDENTITY(1,1)
,ProductID INT 
,Quantity INT
,SalesDate DATETIME
,InsertedDate DATETIME DEFAULT SYSDATETIME()
,ModifiedDate DATETIME DEFAULT SYSDATETIME()
CONSTRAINT PK_TB_Sales PRIMARY KEY (ID)
CONSTRAINT FK_TBL_Sales_TBL_Product 
     FOREIGN KEY (ProductID) REFERENCES dbo.TBL_Product(ID)
)

GO

INSERT dbo.TBL_Product   ( NAME ,Price )
SELECT 'Book',32.4
UNION ALL
SELECT 'Pen',5.2
UNION ALL
SELECT 'Notebook',8.7

Go

INSERT INTO dbo.TBL_Sales( ProductID , Quantity ,SalesDate)
SELECT 1,4,'2013-Apr-10'
UNION ALL
SELECT 1,3,'2013-Apr-11'
UNION ALL
SELECT 1,8,'2013-Apr-13'
UNION ALL
SELECT 1,1,'2013-Apr-18'
UNION ALL
SELECT 2,4,'2013-Apr-10'
UNION ALL
SELECT 2,3,'2013-Apr-11'
UNION ALL
SELECT 2,8,'2013-Apr-13'
UNION ALL
SELECT 2,1,'2013-Apr-18'
UNION ALL
SELECT 3,4,'2013-Apr-10'
UNION ALL
SELECT 3,5,'2013-Apr-11'
UNION ALL
SELECT 3,3,'2013-Apr-13'
UNION ALL
SELECT 3,9,'2013-Apr-18'

SELECT * FROM dbo.TBL_Product

SELECT * FROM dbo.TBL_Sales

How to get list of the most expensive queries in SQL Server

microsoft_sql_server_icon (1)

SELECT TOP 20
    (total_logical_reads + total_logical_writes) / execution_count AS AverageIO
    ,(total_logical_reads + total_logical_writes) AS TotalIO
    ,qt.text Query
    ,o.name ObjectName
    ,DB_NAME(qt.dbid) AS DATABASEName
FROM 
    sys.dm_exec_query_stats qs
        CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
        LEFT OUTER JOIN
    sys.objects o ON o.object_id = qt.objectid          
ORDER BY AverageIO DESC

How to get list of the longest queries in SQL Server

microsoft_sql_server_icon (1)

If you have problem with your database server performance and you do not know where to start your investigation you can use below query to have some clue.

SELECT TOP 20
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS AverageSecond
    ,qs.total_elapsed_time / 1000000.0 AS TotalSecond
    ,qt.text AS Query
    ,o.name AS OBJECTName
    ,DB_NAME(qt.dbid) DatabaseName
FROM 
    sys.dm_exec_query_stats qs
        CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
        LEFT OUTER JOIN 
    sys.objects o ON qt.objectid = o.object_id
ORDER BY AverageSecond DESC

Update from in SQL Server (lookup)

sql2008

USE Arm_DB
Go

CREATE TABLE TBL_ARM1
(
ID INT IDENTITY(1,1)
,Title Nvarchar(50)
)

Go

CREATE TABLE TBL_Arm2
(
ID INT Identity(1,1)
,Name Nvarchar(50)
,Arm1_ID Int
,Arm_Title Nvarchar(50)
)


GO 
INSERT INTO TBL_ARM1 VALUES ('Arm1_1')
INSERT INTO TBL_ARM1 VALUES ('Arm1_2')
INSERT INTO TBL_ARM1 VALUES ('Arm1_3')

Go

INSERT INTO TBL_Arm2 (Name ,Arm_Title) VALUES('Mehran','Arm1_1')
INSERT INTO TBL_Arm2 (Name ,Arm_Title) VALUES('Mehran2','Arm1_2')
INSERT INTO TBL_Arm2 (Name ,Arm_Title) VALUES('Mehran3','Arm1_3')



SELECT * FROM TBL_Arm2


UPDATE TBL_Arm2
SET Arm1_ID = A.ID
FROM TBL_ARM1 A
WHERE A.Title = Arm_Title

List of all transactions that are running in SQL Server’s instance

microsoft_sql_server_icon (1)

     SELECT sqltext.TEXT
     ,req.session_id
     ,req.status
     ,req.command
     ,req.cpu_time
     ,req.total_elapsed_time
     ,req.blocking_session_id
     ,req.percent_complete
     ,req.estimated_completion_time/1000/60  estimated_completion_time_Min
     ,start_time
    FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
    ORDER BY start_time