Direct Data Load from external file to SQL Server Table and Vice Versa

## Using OpenRowSet we can load data from an xl file to a table
insert into dbo.Customers_xl
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;IMEX=1;HDR=NO;DATABASE=E:\sample_entry.xls', 'Select * from [Sheet1$]');


## Using BCP (bulk copy) we can write the output of a XPATH query to an xml file.
declare @sql varchar(8000)

SELECT @sql = 'bcp "SELECT TOP 5 * FROM AdventureWorks.HumanResources.Department for xml auto,elements" queryout c:\myXML.xml -c -t -T -S "MININT-CM4Q9R0\SQLSERVERDEV2008"'

exec master..xp_cmdshell @sql
before doing this The first thing you have to do is make sure xp_cmdshell is enabled

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE 



ref:
http://www.mssqltips.com/sqlservertip/1633/simple-way-to-export-sql-server-data-to-text-files/
http://thiagsundar.wordpress.com/export-data-to-text-file/
http://www.simple-talk.com/sql/t-sql-programming/beginning-sql-server-2005-xml-programming/

Comments

Post a Comment

Popular posts from this blog

SQL Server: Finding Query using SPID

A SPID in SQL Server is a Server Process ID. Every time an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session. Status of a Session: RUNNING: This status means session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. SUSPENDED : It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAIT can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue. RUNNABLE : The SPID is in the runnable queue of a scheduler and waiting for a quantum to run on the sche...
Read more

Restoring Master, Model and MSDB from Netbackup

Restoring System Databases got two parts: Taking the instance in single user mode and Restoring the Master Database If you need to rebuild the system databases on top of this you can use the below command: "C:\Program Files\Microsoft SQL Server\ 100 \Setup Bootstrap\ SQLServer2008R2 \setup.exe" /ACTION=REBUILDDATABASE /INSTANCENAME=SQL008 /SQLSYSADMINACCOUNTS=domain\ServiceUserName /SAPWD="yourpasswordOfSAuser" /SQLCOLLATION="Latin1_General_CI_AS" here 100 is for SQL Server 2008R2. It can vary case by case. How to take instance to single user mode: Two situation can happen: 1. Standalone Instance  2. Failover Cluster Instance 1. Standalone Instance: Go to SQL Config Manager SQL Service Properties -> Advanced Properties -> Startup Parameters put -m; in the Startup Parameters Apply and Okay - it will ask to restart the SQL Service to take effect. Stop and Start the service from Config Manager or you can stop and serv...
Read more