Archive

Archive for the ‘Insert’ Category

What are SQL Query Hints?

July 5, 2014 Leave a comment

SQL server usually selects the best query plan required to execute a T-SQL query. However, there might be cases where the plans selected are not the best. In such situations, you can use the query hints to suggest the best possible plan/method of executing a query. The Query hints can be applied on all operators in a query. These hints apply to SELECT, DELETE, INSERT, UPDATE and MERGE. You can use the Query hints in the OPTION clause after the query.

Few commonly used Query hints with examples are as below.

Read more…

 

Advertisements

Compound Operators in SQL Server

July 5, 2011 1 comment

There was a time when we all wished SQL Server supports compound operators just like any other programming languages. Wouldn’t it be nice to have a shorter syntax for assigning the result of an arithmetic operator?  Well, SQL server 2008 introduced Compound operators that allow you to perform several arithmetic operations using an operand along with equal to (=) operand.

 For example:

Declare @var1  int

Set @var1 = 150

Set @var1 += 100

Read more…

 

 

Understanding MERGE Transact SQL Statement

July 5, 2011 1 comment

By using “Merge”, you can perform insert, delete and update on a table in a single SQL statement. This helps in synchronizing the tables as well.

However while using merge there are several points that you need to keep in mind. Few

  • In a “Merge” statement, a “When Matched” clause with a search condition cannot appear after a ‘When Matched’ clause with no search condition.
  • A “Merge” statement must be terminated by a semi-colon (;).
  • An action of type ‘DELETE’ is not allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.
  • At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.
  • Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints.

Few examples of merge are as given below.

Read more…

 

 

Script to List Linked Servers

October 7, 2010 Leave a comment

In a large server , it is very common to create high number of linked server connections. it might be required ( not very often) to use this list in the SPs or other objetcs created. Below is a simple query / script that can provide the list of linked servers used.

Continue Reading ..

Use single “INSERT” statement for multiple value inserts in SQL server 2008

January 27, 2010 1 comment

In Sql Server 2005 or earlier , to insert each record into a table , you had to isuue one insert statement. One of the new programmability enhancements in the Database Engine introduced in SQL Server 2008 for Transact-SQL is the row constructor which consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma.  With the row constructor, you can now insert multiple values using a single INSERT statement.  

Example:

INSERT INTO [dbo].[TestTable] VALUES
('1', 'Simon'),  
('2', 'Mark'),  
('3', 'Peter')

One of the issue with this approach is that the number of records in a single insert is limited to 1000 records. You might get an error if you try to insert more than 1000 records.

What is @@IDENTITY ?

January 25, 2010 Leave a comment

Very often, it is important to check the last identity value inserted into a table. This can be easily done using the system function “@@IDENTITY” . This System function returns the last inserted identity value. 

The function returns an integer value or a NULL. After an INSERT statement the @@IDENTITY function will contain the latest value inserted into the identity field. It will contain NULL, If the insert/ select statement does not affect any records. one Key feature that needs to be noted is that the @@IDENTITY value does not revert to a previous value even if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back. However the Triggers fired doesn’t have an impact on the function. Failed statements and transactions can change the  identity for a table and create gaps in the identity column values.

to check these , Execute the function with out performing an insert in a new session.

SELECT  @@IDENTITY
Result will be "NULL"

 Now insert some value into a table containing an identity field. If you do not have one, the below script might be of use. 


CREATE TABLE [dbo].[TestTable](
[Ident] [int] IDENTITY(100,1) NOT NULL,
[ColUmn_1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

Insert the data by executing the statements below


INSERT INTO TESTTABLE ( COLUMN_1) VALUES ('HELLO');
SELECT  @@IDENTITY AS CURRENT_IDENTITY_VALUE
     , IDENT AS INSERTED_IDENTITY_VALUE
FROM
 TESTTABLE ;

Result:

Current_identity_value    Inserted_Identity_Value
100                       100

The Scope of the function is restricted to the current session. To get the identity value on a different server , we need to use functions / procedures.

Get the latest identity value inserted into a table / Identity value inserted in the current scope .

September 15, 2009 1 comment

It is often a requirement to fetch the latest/ current identity value used in a specific table . There are two types of identity values that you can fetch.

  • Depending on the scope of execution and irrespective of the table.
  • The value used in a table and irrespective of scope.

Let me show this to you, with an example. Create a table using the query

CREATE TABLE TestTable (
 TestID INT NOT NULL IDENTITY(1,1) ,
 TestValue NVARCHAR(50) NOT NULL )

Depending on the scope of execution and irrespective of the table

Insert a record into the table

INSERT TestTable( TestValue)
VALUES ( 'record 22')

SELECT SCOPE_IDENTITY() as RecentInserted

The function “SCOPE_IDENTITY()” prints the identity value used in the recent insert. This is irrespective of the table used. If you continue inserting record in any other table with Identity columns, you will notice the value returned by the function changing. This function does not accept any parameters.

The identity value used in a table

Use the function “IDENT_CURRENT() ” to get the current identity value used for a table . A sample query is as given below.

SELECT IDENT_CURRENT('TestTable') IdentityInTable

Notice that the table name has to be passed as a parameter for this function.

Also Check how to  Insert values into IDENTITY column of SQL server table