How to insert a coulmn in Excel that is resulted from calculated columns in sql database

Posted on 2014-08-08
Last Modified: 2014-09-02
I am importing table data from SQl database in Excel by query.. however, I would like to insert additional columns in excel which are resulted from a formula done to already existing columns in the database. For instance, in SQL database I have Campaign table that consist of (AccountID, Campaign, Cost, Clicks and impressions) these columns are imported successfully to Excel. I would like to add to Excel table another column (CTR) which is equals (Clicks/impressions)
is there any way that allow me to do that?

Thank you
Question by:Rawan Jambi
    LVL 10

    Accepted Solution

    You can do it one of two ways.  The easiest, add an item to the select query that like
          ,clicks/impressions as 'CTR'
    That will put the results in with the query results.  

    Option B, if you're using a query table within the excel, is to simply add a formula to the column next to the last column of your query results.  This link gives a pretty good detail on doing that so I won't 'reinvent the wheel here'.
    LVL 14

    Expert Comment

    by:Vikas Garg

    You can add extra column to sql

    insert into OPENROWSET('Microsoft.Ace.OLEDB.12.0','Excel 12.0;Database=D:\testing.xlsx;', 
        'SELECT * FROM [Sheet2$]') select *,yourcolumn  as CTR from Comb

    Open in new window

    LVL 14

    Expert Comment

    by:Don Thomson
    What version of Excel are you working with?
    I personally would import the SQL data into Access - then run a query to add the formula and create a new table (or modify the existing table)  and then if you need to, export it to an Excel format.

    You could of course just do it directly in Excel using a Macro (activated with either a keystroke or via a button)

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now