SQL Scrapbook

Short and simple SQL Server posts

Row Constructor

leave a comment »

I just discovered a very cool feature in sql server 2008 which i always wanted. With this feature called the row value constructor or the table value constructor you no longer have to write multipe insert statements or use the
INSERT INTO
<TableName> SELECT <ColumnList> UNION ALL block to insert values into a table.

Row constructors/Table value constructors let you insert multiple rows into a table with single insert statement.

IF OBJECT_ID('#Employee') IS NOT NULL
DROP TABLE #Employee --Drop the table if it exists
GO

CREATE TABLE #Employee (EmpID INT, EmpName VARCHAR(50)) -- Create the table 

INSERT	#Employee     -- This inserts 5 rows
VALUES	(1,'JBauer'),
	(2,'DPalmer'),
	(3,'NCaffrey'),
	(4,'PCollingwood'),
	(5,'PFernandeso')
The above statement inserted 5 rows into the #Employee table. The row values have to specified in the VALUES clause of the insert statement and they should be in braces separated by a comma.
You can see the result by executing the below select statement
SELECT	EmpID,
	EmpName
FROM    #Employee

The row constructors can also be used in a derived table:

SELECT  EmpID,EmpName
FROM	(VALUES(1,'JBauer'),
        (2,'DPalmer'),
        (3,'NCaffrey'),
        (4,'PCollingwood'),
        (5,'PFernandeso')) AS DerTable(EmpID,EmpName)

Any constraints on the tables like the IDENTITY,NOT NULL or the DEFAULT can’t be overwritten when inserting using the row constructors. An explict value
can’t be insterted into an identity column and a NULL value can’t be inserted into a column with a NOT NUL constraint defined on it.

Row Constructor with NOT NULL and DEFAULT”

IF OBJECT_ID('#Employee1') IS NOT NULL

DROP TABLE #Employee1
CREATE TABLE #Employee1 (EmpID INT, EmpName VARCHAR(50) NOT NULL DEFAULT 'N/A')

INSERT #Employee1
VALUES	(1,'JBauer'),
	(2,'DPalmer'),
	(3,'NCaffrey'),
	(4,'PFernandeso'),
	(5,DEFAULT)

SELECT	EmpID,EmpName
FROM	#Employee1

While i don’t understand the limit of 1000 rows that can be inserted using this approach, there are some more meaningful limitaions like
1) The number of columns for each row in a table value constructor must be the same
2) The data types of the corresponding columns should be of the same domain.

Advertisements

Written by sqlscrapbook

July 20, 2010 at 8:13 pm

Load data into a temp table using SSIS

leave a comment »

Very recently i had to load a flat file into a temp table. I know this is not a good approach but i had no other option (atleast at that time). It was then i found out that this is not very straight forward. Below listed are the steps i followed.
Again in short, this is what i want to accomplish. Using a data flow task (DFT) in the SSIS package, i want to load data from a file to a temp table. As you can see the source of the DFT is a flat file and destination is a temporary table.
1) Create a OLEDB connect manager pointing to the SQL Server database, Create a flat file connection manager pointing to the source flat file.

2) For the OLEDB connect manager, in the properties pane set the RetainSameConnection property to true. This is an important part when working with temp tables in SSIS.
3) Configure an execute sql task to create a global temp table. This is my sample create statement

CREATE TABLE ##LoadTempTable(
[ProductKey] INT NULL,

[ProductName] VARCHAR(50) NULL,

[ProductType] VARCHAR(50) NULL
)

4) Execute the create statement you used in the execute sql task (step 2) in the management studio to create the same table there. This way we will have the table available for design and column mappings.
5) Drag a DFT and set the DelayValidation property to true. Configure the source connection. Drag and edit the OLEDB destination connection. Specify the connection manager name.
6) In the data access mode select table or view. In the Name of the table or view field click new. You will see a create statement like CREATE TABLE [OLE DB Destination]…
7) Change the name of the table to your global temp table name.
8) Edit the mappings and you will be able to run the package.

Written by sqlscrapbook

July 14, 2010 at 8:04 pm

Posted in SSIS

Tagged with

Custom message when there are no rows in the data source

leave a comment »

Some times when there is no data in the data source the SSRS report created on top of this will show only the header information. There is an inbuilt option to show your own message when this is the case. In this quick post i will demonstrate how to do this.First, i will create a dataset and make sure that this dataset doesn’t return any rows.

SELECT    TOP 0 FirstName, LastName, [State]
FROM dbo.ResultTable

If you take a close look at the above query, i used TOP 0 to make sure that the dataset returns 0 rows. Now that we have the dataset lets go ahead with the report design. I created a table report and the fields are First Name, Last Name and State.

Since there are no rows returned when you run the report we will see only the report headers.

Now to display a custom message when there are no rows there is a property called “NOROWS”. Select the table and in the properties pane find NOROWS.

Click on the expression button and enter any custom text.

Now that you have the NOROWS property configured you will see this message when you run the report and there are no rows returned by the dataset.

Conclusion: NOROWS property in SSRS can be set to display a user friendly message when the data set doesn’t return any data.

Written by sqlscrapbook

July 13, 2010 at 10:06 pm

Posted in SSRS

Tagged with ,

First Post

leave a comment »

Bienvenida… so here it is, my first post on my first blog…Well, my area of interest is SQL Server and that’s what i will be sharing through this blog. I will post my experiences, new things i learned and lot of cool stuff. Never the less the best way to learn is to teach and this is my objective. I am here to learn… Thanks and take care.

Written by sqlscrapbook

July 9, 2010 at 2:17 am

Posted in General