Tag: 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 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