{"id":221,"date":"2007-02-06T00:00:00","date_gmt":"2007-02-05T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-excel-workbench\/"},"modified":"2026-09-22T10:24:24","modified_gmt":"2026-09-22T10:24:24","slug":"sql-server-excel-workbench","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/sql-server-excel-workbench\/","title":{"rendered":"How to move data between SQL Server and Excel with T-SQL"},"content":{"rendered":"\n
\n

T-SQL can create, read, update, and delete Excel spreadsheet data directly from SQL Server, without exporting through a separate tool. <\/strong><\/p>\n\n\n\n

This guide (workbench) covers four approaches: linking to an Excel file as a data source, querying it inline with OPENDATASOURCE<\/code>\/OPENROWSET<\/code>, generating a quick HTML-based export with sp_makewebtask<\/code>, and using OLE Automation for full control over formatting, ranges, and pivot tables. <\/strong><\/p>\n\n\n\n

Each example uses a small test table of Cambridge pubs, and full sample code is available as a download.<\/strong><\/p>\n<\/blockquote>\n\n\n\n

Phil and I have teamed up on this workbench, which demonstrates how to create, read, update and delete (C.R.U.D) information in Microsoft Excel using T-SQL, from SQL Server<\/strong>. <\/p>\n\n\n\n

The workbench is structured so that it can be pasted into Query Analyzer and SSMS, and the individual examples executed – you can download the .sql from the “Code Download” link at the bottom of the article, load it up and start experimenting!<\/p>\n\n\n\n

The structure of this guide (what you’ll learn)<\/h2>\n\n\n\n

We start by showing you how to create an Excel Spreadsheet from SQL Server in T-SQL (Transact SQL), create a worksheet, attach to it as a linked server, write to it, read from it, update it as if it was an ordinary SQL Server Database table, and then synchronize the data in the worksheet with SQL Server. <\/p>\n\n\n\n

We also illustrate the use of OPENQUERY<\/code>, OPENDATASOURCE<\/code> and OPENROWSET<\/code>.<\/p>\n\n\n\n

To create the Excel spreadsheet, we show how to attach to an ADODB source from SQL Server and execute SQL against that source. We then show you an alternative ‘quick cheat’ way (using sp_makewebtask<\/code>) to create and populate an Excel spreadsheet from Transact SQL.<\/p>\n\n\n\n

If you need more control over the Excel Spreadsheet that you are creating, we then show you how to do it via OLE automation. This will enable you to do anything you can do via keystrokes, and allow you to generate full Excel reports with pivot tables and Graphs.<\/p>\n\n\n\n

Using this technique, you should be able to populate the data, or place data in particular calls or ranges. You can even do ‘macro substitutions’.<\/p>\n\n\n\n

Good to know before you start<\/h2>\n\n\n\n

A word of caution before you start. If you have your security wide open, it is not just you who would be able to write out data as a spreadsheet. An intruder would be able to do it with that list of passwords or credit-card numbers. In a production system, this sort of operation needs to be properly ring-fenced. <\/p>\n\n\n\n

We tend to create a job queue and have a special user, with the appropriate permissions, on the Task Scheduler, to do anything that involves OLE automation or xp_CMDShell<\/code>. Security precautions can get quite complex, but they are outside the scope of the article.<\/p>\n\n\n\n

\n
\n\n

You may also be interested in:<\/em><\/p>\n\n\n\n

SQL Server Security Features: Complete Guide<\/a><\/strong><\/p>\n\n\n\n

All Security, Privacy & Compliance articles and guides on Simple Talk<\/a><\/strong><\/p>\n\n<\/div>\n<\/div> \n\n\n

Some of what we illustrate can be done using DTS or SSIS. Unfortunately, these are outside the scope of this article. In fact, transferring data between Excel and SQL Server can be done in a surprising variety of ways and it would be fun one day to try to list them all.<\/p>\n\n\n\n

How to set up the test data<\/h2>\n\n\n\n

First, we need some simple test data:<\/p>\n\n\n\n

    CREATE TABLE ##CambridgePubs \n            (Pubname VARCHAR(40), \n            Address VARCHAR(80), \n            Postcode VARCHAR(8)) \n    \n\n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Bees In The Wall','36 North Road, \n    Whittlesford, Cambridge','CB2 4NZ' \n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Blackamoors Head','205 Victoria Road, \n    Cambridge','CB4 3LF' \n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Blue Lion','2 Horningsea Road, \n    Fen Ditton, Cambridge','CB5 8SZ' \n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Cambridge Blue','85-87 Gwydir Street, \n    Cambridge','CB1 2LG' \n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Champion Of The Thames','68 King Street, \n    Cambridge','CB1 1LN' \n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Cross Keys','77 Ermine Street, \n    Caxton, Cambridge','CB3 8PQ' \n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Crown Inn','11 High Street, \n    Linton, Cambridge','CB1 6HS' \n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Devonshire Arms','1 Devonshire Road, \n    Cambridge','CB1 2BH' \n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Duke Of Argyle','90 Argyle Street, \n    Cambridge','CB1 3LS' \n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Duke Of Wellington','49 Alms Hill, \n    Bourn, Cambridge','CB3 7SH' \n    INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \n        SELECT 'Eagle Public House','Benet Street, \n    Cambridge','CB2 3QN' \n    <\/pre>\n\n\n\n
And so on. (The full import file is in the ZIP, as is the Excel file!).<\/p>\n\n\n\n
Create the table and then execute the contents of CambridgePubs.SQL<\/code>. We’re now ready to get started properly.<\/p>\n\n\n\n
Contents<\/h2>\n\n\n
\n