SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

This is very interesting question I have received from a new developer. How can I insert multiple values in table using only one insert? Now this is interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.

USE YourDB
GO
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('First',1);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Second',2);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Third',3);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Fourth',4);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Fifth',5);
GO

The clause INSERT INTO is repeated multiple times. Many times we use copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… clauses. Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps me focus on task, instead of copy paste. I have explained following script to new developer. 

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

The effective result is same.

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

Following three questions are many time asked about data between tables.

How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?

There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.

Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article 

SSIS and SSRS Interview questions with Answers

 1) What is the control flow

ANS: In SSIS a workflow is called a control-flow. A control-flow links together our modular data-flows as a series of operations in order to achieve a desired result.

A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, you use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow.

SQL Server 2005 Integration Services (SSIS) provides three different types of control flow elements: containers that provide structures in packages, tasks that provide functionality, and precedence constraints that connect the executables, containers, and tasks into an ordered control flow.

2) what is a data flow
ANS: A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.

SQL Server 2005 Integration Services (SSIS) provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.

3) how do you do error handling in SSIS
ANS: When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.

For example, a data conversion fails because a column contains a string instead of a number, an insertion into a database column fails because the data is a date and the column has a numeric data type, or an expression fails to evaluate because a column value is zero, resulting in a mathematical operation that is not valid.

Errors typically fall into one the following categories:

-Data conversion errors, which occur if a conversion results in loss of significant digits, the loss of insignificant digits, and the truncation of strings. Data conversion errors also occur if the requested conversion is not supported.
-Expression evaluation errors, which occur if expressions that are evaluated at run time perform invalid operations or become syntactically incorrect because of missing or incorrect data values.
-Lookup errors, which occur if a lookup operation fails to locate a match in the lookup table.

Many data flow components support error outputs, which let you control how the component handles row-level errors in both incoming and outgoing data. You specify how the component behaves when truncation or an error occurs by setting options on individual columns in the input or output.

For example, you can specify that the component should fail if customer name data is truncated, but ignore errors on another column that contains less important data.

4) How do you do logging in ssis.
ANS: SSIS includes logging features that write log entries when run-time events occur and can also write custom messages.

Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.

Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.

To customize the logging of an event or custom message, Integration Services provides a schema of commonly logged information to include in log entries. The Integration Services log schema defines the information that you can log. You can select elements from the log schema for each log entry.

To enable logging in a package
1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.
2. On the SSIS menu, click Logging.
3. Select a log provider in the Provider type list, and then click Add.

5)what are variables and what is variable scope ?
ANS: Variables store values that a SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.

Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.

Scope : A variable is created within the scope of a package or within the scope of a container, task, or event handler in the package. Because the package container is at the top of the container hierarchy, variables with package scope function like global variables and can be used by all containers in the package. Similarly, variables defined within the scope of a container such as a For Loop container can be used by all tasks or containers within the For Loop container.

6) True or False - Using a checkpoint file in SSIS is just like issuing the CHECKPOINT command against the relational engine. It commits all of the data to the database.
ANS: False. SSIS provides a Checkpoint capability which allows a package to restart at the point of failure.

7) True or False: SSIS has a default means to log all records updated, deleted or inserted on a per table basis.
ANS: False, but a custom solution can be built to meet these needs.

8) What is a breakpoint in SSIS? How is it setup? How do you disable it?
ANS: A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an opportunity to review the status of the data, variables and the overall status of the SSIS package.
10 unique conditions exist for each breakpoint.
Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the object where you want to set the breakpoint and select the 'Edit Breakpoints...' option.

9) How do you eliminate quotes from being uploaded from a flat file to SQL Server?
ANS: In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.
Additional information: How to strip out double quotes from an import file in SQL Server Integration Services

10) Can you explain how to setup a checkpoint file in SSIS?
ANS: The following items need to be configured on the properties tab for SSIS package:
CheckpointFileName - Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it's a good idea to use an expression that concatenates a path defined in a package variable and the package name.
CheckpointUsage - Determines if/how checkpoints are used. Choose from these options: Never (default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist.
SaveCheckpoints - Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior.

11) How do you upgrade an SSIS Package?
ANS: Depending on the complexity of the package, one or two techniques are typically used:
Recode the package based on the functionality in SQL Server DTS
Use the Migrate DTS 2000 Package wizard in BIDS then recode any portion of the package that is not accurate.

Using RS Scripter to create deployment script for reporting services

If you are using SSRS then chances are high to come across a scenario where you want to deploy reports developed on development machine to the production server, there are various ways to do this and one of them is to use RS Scripter tool. Here are the steps to do this -

1. Creating Report Deployment Script

1. Download the Reporting Services Scripter - http://www.sqldbatips.com/showarticle.asp?ID=62
2. Update the servers.xml; change the reportservice value if your ReportServer name is different.

   <server label="SQL2000" reportservice="http://localhost/ReportServer/ReportService.asmx" />

   <server label="SQL2005" reportservice="http://localhost/ReportServer/ReportService2005.asmx" />

3. Run the RSScripter.exe.

Using the Pivot transformation in SQL Server Integration Services-VDO

Pivoting is a useful way to transform data in SQL Server Integration Services (SSIS) -- but it's hardly intuitive.


http://searchsqlserver.techtarget.com/video/Using-the-Pivot-transformation-in-SQL-Server-Integration-Services?videoId=ed16802b3e7f6210VgnVCM1000000d01c80aRCRD

Using package configurations in SQL Server Integration Services-VDO

http://searchsqlserver.techtarget.com/video/Using-package-configurations-in-SQL-Server-Integration-Services-SSIS?videoId=f375802b3e7f6210VgnVCM1000000d01c80aRCRD

Saving time with text data sources(Flat File) in SSIS-VDO

http://searchsqlserver.techtarget.com/video/Saving-time-with-text-data-sources-in-SSIS?videoId=8eb5802b3e7f6210VgnVCM1000000d01c80aRCRD

An introduction to SQL Server data warehousing concepts-part2 VDO

http://searchsqlserver.techtarget.com/video/Building-a-data-warehousing-and-BI-solution

An introduction to SQL Server data warehousing concepts-Part1 VDO

http://searchsqlserver.techtarget.com/video/An-introduction-to-SQL-Server-data-warehousing-concepts

SSRS::::Creating new project Using Report Wizard

There are two ways to create SSRS Reports. You can Create the report manually, or you can use the Report Wizard to give yourself a head start. I already explain how to Create a report Manually in previous post. For this first report, we are going to take advantage of the wizard.

MS BI::::: 39 Videos to Watch for Free on BI

Click this link to view the videos on Business Intelligence Tools::::::  39 VIDEOS

SSRS::::Reporting Services Configuration Tool

  Installing SQL Server Reporting Services is just the first step in building the foundation for a Reporting Services instance.  The second step in the process is configuring Reporting Services to confirm to the particulars of your environment.

Business Intelligence Portfolio

Check out this SlideShare Presentation:

Business Intelligence Portfolio

Sample Resume on BI

How to Install SQL Server 2005 - A Quick Guide

1. Insert the DVD into the DVD drive and double-click on the icon:

SSRS::::: Report Parameters in SQL Server Reporting Services

Introduction

When you are using reports, you need to have parameters that narrow down your report for better analysis. You will often see that you need to have more than one parameter, where the second parameter may depend on the first parameter, which many require you to refresh the second parameter depending on the first parameter.

In addition,