SSIS SQL Server Integration Services - limit number of rows

SSIS SQL Server Integration Services - limit number of rows

SSIS Microsoft Sql server

1. Use Row Sampling component from toolbox

2. Double click at Row Sampling component to set number of rows

 3. Set Sampling selected output to redirect selected number of rows

4. Set Sampling unselected output to redirect all rows available

TSQL: PIVOT table with hours

TSQL: PIVOT table with hours

Transact-SQL

1. Table containg names and time

Columns: Name, CreatedOn

Table: RegisterTime

2. Query

SELECT * FROM

(SELECT Name, DATEPART(HOUR, CreatedOn) AS RcpHour 
          FROM RegisterTime) sourceTable
 PIVOT (COUNT(RcpHour) FOR RcpHour IN 
         ( [0],  [1],  [2],  [3],  [4],  [5], 
           [6],  [7],  [8],  [9], [10], [11], 
          [12], [13], [14], [15], [16], [17], 
          [18], [19], [20], [21], [22], [23])) as pvtTable

Transact-SQL: insert into two tables, use newly created values with output clause

Transact-SQL: insert into two tables, use newly created values with output clause

Microsoft SQL

Tables:

Table1 Columns: Id, Name

Table2 Columns: Id2, Surname, Table1Id

Data:

Name: Adam 

Surname: Sandler

Query:

INSERT INTO Table1(Name) 

OUTPUT inserted.Id, 'Sandler' INTO Table2 (Table1Id, Surname)  

VALUES('Adam');

Query will not work with foreign keys defined. In order to use it with foreign keys you need to switch off constrain check.

f.e.

ALTER TABLE Table2 NOCHECK CONSTRAINT ALL

GO

Query

ALTER TABLE Table2 CHECK CONSTRAINT ALL

GO