Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Thursday, January 9, 2020
What is SSIS package in SQL Server?
6:04 PM
SSIS
SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations that are extracted, transform and load data. ETL operations are very common in data warehousing (DW) applications, SSIS is by no means limited to just data warehousing;
SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations that are extracted, transform and load data. ETL operations are very common in data warehousing (DW) applications, SSIS is by no means limited to just data warehousing;
SQL Server Integration Services basics usages,
- To retrieve data from just about any source (file, database, etc.)
- To perform various transformations on the data; e.g. convert from one type to another, convert to u, ppercase or lowercase, perform calculations, etc.
- To load data into just about any source
- To define a workflow
The first version of SSIS was released with SQL Server 2005. SSIS is a replacement for Data Transformation Services (DTS) which was available with SQL Server 7.0 and SQL Server 2000. SSIS builds on the capabilities introduced with DTS.
In this tutorial, we will step through a number of topics that you need to understand in order to successfully build an SSIS package. Our high-level outline is as follows:
- Creating SSIS packages with SQL Server Management Studio (SSMS)
- Business Intelligence Development Studio (BIDS)
- Creating a simple SSIS package in BIDS
- Deploying SSIS packages
- Executing SSIS packages
You can either use the outline on the left or click the arrows to the right or below to scroll through each of these topics.
Thursday, August 24, 2017
Get the last generated Identity column value in SQL Server
5:07 PM
In SQL Server Identity column values are auto created.
There are multiple ways in sql server, to retrieve the last identity value that is generated. The most common way is to use SCOPE_IDENTITY() built in function.
Apart, from using SCOPE_IDENTITY(), you also have @@IDENTITY and IDENT_CURRENT('TableName') function. But there are certain very important variation that needs to be taken care. which is explained as below,
Example,
Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('GalaxyEmployees')
Let's now understand the difference between three of the different scenarios,
SCOPE_IDENTITY() returns the last identity value that is created in the same session (Connection) and in the same scope (in the same session and same scope - Stored procedure, function, trigger).
Let's say, I have 2 tables GalaxyEmployee1 and GalaxyEmployee2, and I have a trigger on GalaxyEmployee1 table, which will insert a record into GalaxyEmployee2 table.
So, when we insert a record into GalaxyEmployee1 table, SCOPE_IDENTITY() returns the identity value that is generated in GalaxyEmployee1 table, where as @@IDENTITY returns, the value that is generated in GalaxyEmployee2 table. So, @@IDENTITY returns the last identity value that is created in the same session without any consideration to the scope.
IDENT_CURRENT('GalaxyEmployees') returns the last identity value created for a specific table across any session and any scope.
In brief:
SCOPE_IDENTITY() - Returns the last identity value that is created in the same session and in the same scope.
@@IDENTITY - Returns the last identity value that is created in the same session and across any scope.
IDENT_CURRENT('Table') - Returns the last identity value that is created for a specific table across any session and any scope.
There are multiple ways in sql server, to retrieve the last identity value that is generated. The most common way is to use SCOPE_IDENTITY() built in function.
Apart, from using SCOPE_IDENTITY(), you also have @@IDENTITY and IDENT_CURRENT('TableName') function. But there are certain very important variation that needs to be taken care. which is explained as below,
Example,
Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('GalaxyEmployees')
Let's now understand the difference between three of the different scenarios,
SCOPE_IDENTITY() returns the last identity value that is created in the same session (Connection) and in the same scope (in the same session and same scope - Stored procedure, function, trigger).
Let's say, I have 2 tables GalaxyEmployee1 and GalaxyEmployee2, and I have a trigger on GalaxyEmployee1 table, which will insert a record into GalaxyEmployee2 table.
So, when we insert a record into GalaxyEmployee1 table, SCOPE_IDENTITY() returns the identity value that is generated in GalaxyEmployee1 table, where as @@IDENTITY returns, the value that is generated in GalaxyEmployee2 table. So, @@IDENTITY returns the last identity value that is created in the same session without any consideration to the scope.
IDENT_CURRENT('GalaxyEmployees') returns the last identity value created for a specific table across any session and any scope.
In brief:
SCOPE_IDENTITY() - Returns the last identity value that is created in the same session and in the same scope.
@@IDENTITY - Returns the last identity value that is created in the same session and across any scope.
IDENT_CURRENT('Table') - Returns the last identity value that is created for a specific table across any session and any scope.
Subscribe to:
Posts (Atom)
