Export data in SQL Server using BCP command

 

The BCP command in SQL Server is a command-line utility that bulk copies data between a SQL Server instance and a data file in a user-specified format. It is used to import large amounts of data from a text file into a SQL Server table or to export data out of a table into a text file. It is also used to create format files that store metadata about the data, such as field types and lengths.

 

Procedure is to export data from SQL Server data table into csv with pipe "|" delimiter.

 

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: EXECUTE Procedure to build BCP Command against any table in database

EXECUTE sqlcache_GenerateTableBCPCommand 'DBName','TableName','DestinationFileName'

 

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Create BCP Command against any table in database

USE DBName
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sqlcache_GenerateTableBCPCommand]
@DBName varchar(255),
@TABLE VARCHAR(255),
@FILE VARCHAR(255)
AS

CREATE TABLE #QUERY (  QUERY VARCHAR(MAX)  )

DECLARE @SERVERNAME VARCHAR (255);
SET @SERVERNAME='SqlCache'

------------------------------Generate comma seprated columns of target table
declare @cmd varchar(4000), @sql varchar(8000)
SELECT @sql =''
INSERT INTO #QUERY
SELECT DISTINCT
' SELECT '+(STUFF((
                    SELECT ', ' +  ''''''+name+ ''''''
                    FROM SYS.COLUMNS Z
                    WHERE Z.object_id=Y.object_id
                    AND Y.object_id=OBJECT_ID(C.TABLENAME)
                    FOR XML PATH('')
                    ), 1, 2, ''))  
+' UNION ALL SELECT ' + (STUFF((
                                SELECT ', ' + 'cast(['+name+'] as varchar(500))'
                                FROM SYS.COLUMNS Z
                                WHERE Z.object_id=Y.object_id
                                AND Y.object_id=OBJECT_ID(C.TABLENAME)
                                FOR XML PATH('')
                                ), 1, 2, ''))+' FROM '+@DBName+'.dbo.'+TABLENAME   AS S
FROM (
    SELECT [Tables].name AS [TableName],
    SUM([Partitions].[rows]) AS [TotalRowCount]
    FROM sys.tables AS [Tables]
    JOIN sys.partitions AS [Partitions]
    ON [Tables].[object_id] = [Partitions].[object_id]
    AND [Partitions].index_id IN ( 0, 1 )
    WHERE [Tables].name like '%'+@TABLE+'%'
    GROUP BY SCHEMA_NAME(schema_id), [Tables].name) AS C
INNER JOIN SYS.COLUMNS Y ON Y.object_id=OBJECT_ID(C.TABLENAME)
--WHERE TotalRowCount >0

--------------------------------Generate BCP command and exports data from user table into pipe delimited csv file
 SELECT @sql= ( SELECT TOP 1  QUERY FROM #QUERY )

SET @Cmd = 'master..xp_cmdshell ''bcp "' + @sql + '" queryout D:\DestinationFolder\'+@SERVERNAME+'_'+@FILE+'.csv -c -t^| -S'+@@SERVERNAME+' -T -Usqlcacheuser -Psqlcachepass'''

EXEC (@cmd)

GO

 

 

New features of Microsoft SQL Server 2012

 1. AlwaysOn Availability Groups  

2. Contained Databases  

3. Columnstore Indexes  

4. FileTable  

5. Graph Data Support  

6. Improved Security and Compliance  

7. Enhanced data visualizations  

8. PowerPivot  

9. Data Quality Services  

10.Enhanced SQL Server Integration Services  

11. Windows Azure Integration 

 12. Windows PowerShell Enhancements  

13. SQL Server Management Studio Enhancements  

14. Distributed Replay  

15. SQL Server Data Tools  

16. Adaptive Query Processing  

17. Native JSON Support  

18. Dynamic Data Masking  

19. Performance Tuning Enhancements  

20. Support for Linux Operating System 

1. AlwaysOn Availability Groups 

AlwaysOn Availability Groups is a feature of Microsoft SQL Server 2012 that provides increased availability and disaster recovery capabilities. It allows up to four secondary replicas to be configured with varying levels of read-only access, automatic failover, and availability at different locations. The primary replica handles all transaction requests and redirects read-only requests to the secondary replicas. This allows for higher availability and scalability of the database. 

 

2. Contained Databases 

A Contained Database in Microsoft SQL Server 2012 is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. The database has all of its settings, metadata and objects and is self-contained within its own set of system objects. All objects of the database are fully contained within the database itself and are not stored in the master database. Contained databases can be moved to other instances of SQL Server and retain all of their settings, making them more portable than traditional databases. 

 

3. Columnstore Indexes 

Columnstore Indexes are a new type of index that is optimized for data warehouse workloads. It is designed to improve query performance by reducing the amount of data that is scanned, thus reducing the number of I/O operations that need to be performed. This type of index is stored in a columnar format, allowing for much faster access to data. Columnstore Indexes are available in SQL Server 2012 and later. 

 

4. FileTable 

A FileTable is a new feature introduced in SQL Server 2012 that allows a database to store files and documents. It is a special type of table that is used to store file data in the form of files and documents, allowing easy access and management of documents. FileTables provide an integrated way to store and manage files, which makes it easier to access them from applications. 

 

5. Graph Data Support 

Graph Data Support in SQL Server 2012 is a new feature that allows users to store and query data stored in the form of graph structures. This feature allows developers to store data as nodes and edges and then query the data using an SQL-like syntax. This feature provides a powerful way to analyze and visualize complex relationships between data. Additionally, it can also be used to model scenarios where data is related in a hierarchical or networked manner. 

 

6. Improved Security and Compliance 

SQL Server 2012 includes several features that help organizations meet their security and compliance requirements:  

1. Transparent Data Encryption (TDE): TDE encrypts the data at rest, providing a layer of protection for sensitive data stored in the database.   

2. Extensible Key Management: Extensible Key Management provides a way to manage encryption keys outside of the database, allowing organizations to meet their key management needs.  

3. Row-Level Security: Row-Level Security allows organizations to control access to data on a per-user basis, helping to secure sensitive data.  

4. Auditing and Compliance: SQL Server 2012 offers a robust auditing system, allowing organizations to track changes to data and gain insight into who is accessing it.   

5. Dynamic Data Masking: Dynamic Data Masking allows organizations to hide sensitive data from users who do not need access to it, reducing the risk of data leakage. 

 

7. Enhanced data visualizations 

SQL Server 2012 enhanced data visualizations by making it easier to create, visualize, and interact with data. The new features in SQL Server 2012 include Power View, a self-service business intelligence tool, and improved support for data visualizations in Excel and SharePoint. SQL Server 2012 also provides enhanced visualization capabilities with integration of SQL Server Reporting Services, which allows for more flexible and powerful data visualization capabilities. 

 

8. PowerPivot 

PowerPivot is an add-on to Microsoft Excel that enables users to mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily. It provides a simple and intuitive interface to create powerful data models and interactive reports. PowerPivot is included in Microsoft SQL Server 2012. 

 

9. Data Quality Services 

Data Quality Services (DQS) is a component of Microsoft SQL Server that enables customers to leverage a Knowledge-Based approach to data cleansing and data matching. It is a scalable data quality platform that enables customers to build and deploy data quality solutions. DQS enables customers to create, maintain, and use knowledge bases to improve the quality of their data. It also provides data matching capabilities that can be used to identify duplicate records and reduce the time it takes to complete data integration projects. 

 

10. Enhanced SQL Server Integration Services 

SQL Server 2012 Integration Services (SSIS) offers a number of enhancements over previous versions, including better performance and scalability, improved error handling, and the ability to deploy packages to the SSIS server. It also has the ability to create custom connections and tasks, and improved logging and auditing capabilities. Additionally, SSIS includes a new deployment model, allowing packages to be deployed to the SSIS server and managed centrally. Finally, a new Package Parts feature allows developers to break a package into separate parts to make it easier to manage. 

 

11. Windows Azure Integration 

Windows Azure Integration in SQL Server 2012 is a feature that enables users to deploy and manage their databases in Windows Azure, resulting in a scalable and cost effective cloud solution. It also allows users to easily move data between on-premises and cloud databases, as well as providing the ability to connect to Windows Azure services such as storage, messaging, and identity. 

 

12. Windows PowerShell Enhancements 

Windows PowerShell Enhancements in SQL Server 2012 provide a new set of cmdlets, providers, modules, and snap-ins that enable administrators and developers to automate administrative tasks. Enhancements include a new set of cmdlets for managing database objects, new built-in providers, and a new module specifically for managing SQL Server 2012. Additionally, Windows PowerShell provides a new set of snap-ins and a new scripting language to help simplify and streamline database administration and development tasks. 

 

13. SQL Server Management Studio Enhancements 

The SQL Server Management Studio of SQL Server 2012 has been enhanced in several ways. These include new features like improved query writing capabilities, improved integration with Windows PowerShell, improved IntelliSense and code snippets, improved Database Diagrams, improved Database Project support, and improved performance tuning. 

 

14. Distributed Replay 

Distributed Replay of SQL Server 2012 is a feature that allows users to simulate real-world workloads on SQL Server systems. It lets users replay trace files captured from production systems on a set of computers. This helps to scale up the testing environment to assess the performance and scalability of the system under test. With Distributed Replay, users can simulate the production workloads across multiple computers, analyze the results, and identify the root cause of any performance issues. 

 

15. SQL Server Data Tools 

SQL Server Data Tools (SSDT) is a set of development tools that allow developers to build and deploy databases and business intelligence solutions in the SQL Server 2012 environment. SSDT includes a rich set of features for authoring, debugging, and deploying data-tier applications for SQL Server 2012, SQL Azure, and SQL Server on Windows Azure Virtual Machines. Developers can use the integrated development environment (IDE) of SSDT to design and deploy databases, create data-tier applications, and develop and deploy projects to the cloud. 

 

16. Adaptive Query Processing 

Adaptive Query Processing is a set of query processing enhancements in SQL Server 2012 that helps improve query performance. This is done by automatically adapting the query plan based on feedback from the initial plan execution. Adaptive Query Processing can choose between different query plans during the execution process, and make adjustments to the query plan as needed. This helps improve query performance by reducing the amount of time needed to generate the query plan, and by choosing the optimal query plan for the given query. 

 

17. Native JSON Support 

Native JSON support in SQL Server 2012 is a feature that allows developers to store and query JSON data in SQL Server databases. This feature allows for the storage of JSON documents in a relational database, as well as the ability to query and update those documents. This feature enables developers to more easily integrate their applications with web services and other applications that use JSON as their data exchange format. 

 

18. Dynamic Data Masking 

Dynamic Data Masking (DDM) is a feature in SQL Server 2012 that allows you to mask sensitive data in the database, so that it is not viewable by users who do not have the proper permissions. DDM allows you to define rules to determine which data should be masked, and how it should be masked. This can help to protect sensitive data from unauthorized users or malicious applications. 

 

19. Performance Tuning Enhancements 

1. Columnstore Indexes: Columnstore indexes provide 10-100x query performance gains for data warehouse workloads.  

2. Resource Governor: Resource Governor helps control performance by allowing administrators to limit the resources consumed by individual queries or groups of queries.  

3. Query Store: Query Store helps identify and troubleshoot query performance issues by collecting and storing query performance data over time.  

4. Backup Compression: Backup compression can save up to 90% of disk space used for backups. 

5. Buffer Pool Extension: Buffer Pool Extension allows the buffer pool to be extended to SSDs, providing faster query response times.  

6. In-Memory OLTP: In-Memory OLTP provides up to 30x performance gains for transactional workloads.  

7. Plan Freezing: Plan freezing helps prevent plan regressions and can improve query performance by up to 30%. 

 

20. Support for Linux Operating System 

SQL Server 2012 supports Linux as a platform for running SQL Server. Microsoft provides tools that make it easy to install, configure and manage SQL Server on Linux. Additionally, customers can use the feature-rich SQL Server client tools and APIs, such as Transact-SQL, on Linux to manage their SQL Server databases.