Archive

Archive for the ‘T SQL’ Category

BCP { IN | OUT | QUERYOUT } – the BCP util in SQL Server

September 9, 2011 11 comments

As per MS BOL the BCP utility bulk copies data between an instance of SQL Server and a data file in a user-specified format. The BCP utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.

BCP or Bulk Copy Program is a command line utility that runs on DOS console. It is not a SQL statement, to run it from SQL SSMS you must use the xp_cmdshell Extended Stored Procedure as shown below.
 

Now let’s check with few examples how we can use the BCP Utility:
 

–> BCP OUT


-- Bulk Copy All Table records to a File
SELECT *
FROM Person.Address

exec master..xp_cmdshell 'BCP AdventureWorks.Person.Address OUT d:\PersonAddressAll.txt -T -c'

-- Processing status on Result Pane
NULL
Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
...
1000 rows successfully bulk-copied to host-file. Total received: 18000
1000 rows successfully bulk-copied to host-file. Total received: 19000
NULL
19614 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 250 Average : (78456.00 rows per sec.)
NULL

 

–> BCP QUERYOUT


-- Bulk Copy Selected (Top 20) records by using Query in BCP command from Table to a file
SELECT TOP 20 AddressID, AddressLine1, City, StateProvinceID, PostalCode, ModifiedDate
FROM Person.Address

exec master..xp_cmdshell 'BCP "SELECT TOP 20 AddressID, AddressLine1, City, StateProvinceID, PostalCode, ModifiedDate FROM AdventureWorks.Person.Address" QUERYOUT d:\PersonAddressByQuery.txt -T -c'

-- Processing status on Result Pane
NULL
Starting copy...
NULL
20 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (20000.00 rows per sec.)
NULL

 

–> BCP IN


-- Bulk Copy All File records to an existing Table
-- Create a similar table first
IF OBJECT_ID('AdventureWorks.Person.Address2') IS NOT NULL
DROP TABLE AdventureWorks.Person.Address2

SELECT *
INTO AdventureWorks.Person.Address2
FROM AdventureWorks.Person.Address
WHERE 1=2

exec master..xp_cmdshell 'BCP AdventureWorks.Person.Address2 IN d:\PersonAddressAll.txt -T -c'

-- Check newly inserted records
SELECT *
FROM AdventureWorks.Person.Address2 -- (19614 row(s) affected)

 

Options used in above queries:
-T : Trusted connection, here username & password are not required.
-c : Performs the operation using a character data type.
 

Note: In case of no Trusted connection, you must have to use the -U & -P options, like: “-U username -P password” instead of “-T”. If “-P password” is not applied the password is asked when the command is submitted, thus it is also a secure way of entering a masked password.
If you are not on default instance than there will be need to provide the Server instance name also by adding -S option, like: “-S ServerName”.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 

Categories: SQL Tips Tags: , , , , ,

SQL Trivia – Reverse a string without using T-SQL REVERSE() function

August 18, 2011 9 comments

Everyone knows that the REVERSE() function (as its name suggests) reverses a string’s value, MSDN link.

This post’s title talks about going the other way, but why? If we already have this function then why to reinvent the wheel.

But at times an Interviewer may trick you and asks this question randomly: “How will you reverse a string without using REVERSE() function? No loop, should be a single query.” People who have actually worked on CTEs and know about recursive CTEs can guess the answer and create the query on-spot. Actually he wants to know if you have really worked on CTEs & recursive CTEs, or just learned the concept, or do not know about this at all.

Let’s check this by a simple example:

DECLARE @StringToReverse VARCHAR(55)
SET @StringToReverse = 'Reverse a string with out using REVERSE() function'

;WITH cte AS (
      SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln
      UNION ALL
      SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln
      FROM cte
      WHERE ln >= 1)
SELECT @StringToReverse AS String, revStr
FROM cte
WHERE ln = 0

Output:-

Note: In the code above check line numbers 5 & 7 containing CAST function, “CAST(” AS VARCHAR(55)) AS revStr” & “CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55))”.

Applying CAST or CONVERT function is necessary here as datatype & size of both the columns in the anchor & recursive part should be same. Otherwise SQL server will throw the following error:

Msg 240, Level 16, State 1, Line 4
Types don’t match between the anchor and the recursive part in column “revStr” of recursive query “cte”.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 

Categories: SQL Tips, SQL Trivia Tags: , , ,

DIRTY reads and PHANTOM reads – SQL Server

July 20, 2011 15 comments

–> DIRTY READS: Reading uncommitted modifications are call Dirty Reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction, thus getting you incorrect or wrong data.

This happens at READ UNCOMMITTED transaction isolation level, the lowest level. Here transactions running do not issue SHARED locks to prevent other transactions from modifying data read by the current transaction. This also do not prevent from reading rows that have been modified but not yet committed by other transactions.

To prevent Dirty Reads, READ COMMITTED or SNAPSHOT isolation level should be used.
 

–> PHANTOM READS: Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get different number of rows by firing same query in current transaction.

In REPEATABLE READ isolation levels Shared locks are acquired. This prevents data modification when other transaction is reading the rows and also prevents data read when other transaction are modifying the rows. But this does not stop INSERT operation which can add records to a table getting modified or read on another transaction. This leads to PHANTOM reads.

PHANTOM reads can be prevented by using SERIALIZABLE isolation level, the highest level. This level acquires RANGE locks thus preventing READ, Modification and INSERT operation on other transaction until the first transaction gets completed.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 

Categories: DB Concepts, Differences Tags: , , , , , , ,

Check and ReSeed IDENTITY column value in a table

July 8, 2011 3 comments

In my [previous post] I discussed about IDENTITY property with a [demo video].

Here in this post we will see how you can Re-Seed a particular IDENTITY Column value.
 

There are times when you want to Re-Seed or reset the value of an identity column in a table.

When you delete a lot of records from a table and then try to insert more records. You would expect the identity values to start after the max value present after delete. But it preserves the max value ever present in the table and continues from there.

Or when you delete all records form a table and want to reseed the identity column value to start from afresh 1.
 

All you need is following statement, syntax:
DBCC CHECKIDENT (TableNameWithSingleQuotes, reSeed, NewseedValue);

-- Example:
DBCC CHECKIDENT ('Person.Contact', reseed, 100);

 

This will start assigning new values starting from 101. But make sure that there are no records that have value greater than 100, otherwise you might duplicates.

If all records are deleted from the table and you want to reseed it to 0 then you can also TRUNCATE the table again. This will reset the IDENTITY column as per the DDL of that table.
 

–> You can check the same demo here:


 

–> Sample SQL Code:

-- Check current IDENTITY value, and re-seeds the value with largest value of the column:
DBCC CHECKIDENT ('dbo.Employee'); -- do not use it.
GO

-- Re-seed IDENTITY column value:
DBCC CHECKIDENT ('dbo.Employee', reseed, 102);
GO

-- Check current IDENTITY value (without Re-seeding):
DBCC CHECKIDENT ('dbo.Employee', noreseed);
GO

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Deepak B')

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Manish A')
GO

SELECT * FROM [dbo].[Employee]
GO

 

Categories: SQL Tips Tags: , , ,

SQL Query for calculating Running Totals

July 4, 2011 4 comments

A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, simply by adding the value of the new number to the running total.

Let’s see how to get these Running totals by creating a simple SQL queries below:

USE [AdventureWorks]
GO

-- Method 1: (Query Cost 9%)
;with RunTot as (
select row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
from Sales.SalesOrderHeader s
join Person.Contact p
on s.SalesPersonID = p.ContactID)
select  a.row, a.SalesPersonID, a.FirstName, a.LastName, a.OrderDate, a.TotalDue, sum(b.TotalDue) as RunTotal
from RunTot a
join RunTot b on a.SalesPersonID = b.SalesPersonID and a.row >= b.row
group by a.row, a.SalesPersonID, a.FirstName, a.LastName, a.TotalDue, a.OrderDate
order by a.SalesPersonID, a.row

-- Method 2: (Query Cost 91%)
;with RunTot as (
select row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
from Sales.SalesOrderHeader s
join Person.Contact p
on s.SalesPersonID = p.ContactID)
select  row, SalesPersonID, FirstName, LastName, OrderDate, TotalDue,
(select SUM(TotalDue) from RunTot b where b.SalesPersonID=a.SalesPersonID and b.row<=a.row) as RunTotal
from RunTot a

-- Output Listed below:

Categories: SQL Tips Tags: