Importing Multiple Worksheets On Excel To Single Table

Posted on 2011-05-11
Last Modified: 2012-05-11
Began supporting a new production system quite recently, with SQL Server 2005 as backend. Had call from user today from user who was trying to import Excel spreadsheet via the SQL 2005 Importr/Export Wizard - user was trying to import all four worksheets from spreadsheet into single table in SQL Server 2005.

Import was failing with error message along lines of :

The same destination table name TARGETTABLE is used more than once. All destination table names must be unique.

User mentioned that they had been able to do this previously without any problems under their old SQL Server 2000 box - logged onto that box, and I was able to import all four worksheets from spreadsheet into single table in SQL Server 2005.

Any ideas on how this can be done in SQL 2005, given this apparent restriction in SQL 2005's Importr/Export Wizard.
Question by:raymurphy
    LVL 15

    Accepted Solution

    LVL 39

    Assisted Solution

    You could import all sheets in different intermediate/staging tables then run in SQL server a INSERT into your single destination table a series of SELECT statements with UNION ALL in between from each of the intermediate/staging tables.

    Author Comment

    ashilo, this looks like something that will be worth trying.

    lcohan, this approach is fine from a developer/dba perspective but would not be suitable for the end-users themselves.

    Thanks for both comments.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now