Archive

Archive for the ‘Insert’ Category

Creating .csv or .txt file using batch (.bat) commands

June 27, 2018 Leave a comment

Copy and paste the below code into a batch file (.bat file) to create a .csv or text file from the resultset of query. You can either use the query directly in the batch file or use another file with SQL query as the source. In this case, I have used file C:\SQLCMDTEST\TestCode.sql  as the query source. you can point this to any file that have a SQL query

create the file

sqlcmd -S “SQLServerName” -d “YourDBName” -i “C:\SQLCMDTEST\TestCode.sql” -W -s “,” -o “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT.csv”

delete the second row in the file with —————,—————  value

findstr /V “\——-” “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT.csv” > “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT_06222018.csv”

 

@@echo off

del errors /f /s /q

rd Errors

md Errors

sqlcmd -S “SQLServerName” -d “YourDBName” -i “C:\SQLCMDTEST\TestCode.sql” -W -s “,” -o “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT.csv”

findstr /V “\——-” “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT.csv” > “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT_06222018.csv”

del “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT.csv”

 

Advertisements

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…

 

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.