SQL Server Performance

Database Entry Problem (MSSQL-2005)

Discussion in 'SQL Server 2005 General DBA Questions' started by anshubansal2000, Sep 9, 2005.

  1. anshubansal2000 New Member

    Hi,
    I am preparing a database in MSSQL for all cities list of the world. I have a list of all cities in MS-Excel file.
    is there any way to enter these values automatically into database from Excel file or I have to enter the values manually? I am afraid these all values are 8000+ values.

    Please let me know.
    Thanking You
    Anshu
  2. dineshasanka Moderator

  3. Madhivanan Moderator

  4. Adriaan New Member

    If you have Access installed too, then import the Excel sheet into Access. Next, add a linked ODBC table.

    File > Get External Data > Link Tables

    This brings up the Link dialog box. For "Files of Type" select the last option "ODBC databases", and a wizard will pop up that guides you through the steps.

    After the link to table on SQL Server is established, you run an INSERT action query.

    There may be ways of doing this directly from Excel, but I'm never comfortable with true data in Excel.
  5. FrankKalis Moderator

    If this is a one time import just to fill the SQL Server table, go with the DTS Import/Export wizard. You can imvoke it in Enterprise Manager with a right mouse click.
    Sounds like a straight forward import.

    Sorry, just read that it's SQL Server 2005. No idea where and how to invoke the wizard here. [:I]
    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  6. satya Moderator

    DTS is still valid in SQL 2005, before that if you're handling this data for a production environment then I suggest to wait until end of November as SQL 2K5 RTM will be released and may have few advantages more as the current one is in beta.

    SQL 2005 introduces SSIS -http://www.databasejournal.com/features/mssql/article.php/3525886 for more information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. anshubansal2000 New Member

    Hi,
    I am sorry to say, I couldnot find DTS wizard in SQL Server Express edition. Also I couldn't find DTS Wizard in Visual Studio 2005 evaluation version.
    Please let me know.
    Thanking You
    Anshu
  8. Madhivanan Moderator

    Have you tried the Link I suggested?

    Madhivanan

    Failing to plan is Planning to fail
  9. anshubansal2000 New Member

    Yes, I am trying, I will just let you know in couple of moments.
    Thanking You
    Anshu
  10. Adriaan New Member

    As an aside ... if there are just 8,000 cities in your world then what planet are you on?[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  11. Madhivanan Moderator

    may be top 8000 cities [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  12. anshubansal2000 New Member

    Hi Guys,<br />Only the selected cities I am cosidering...<img src='/community/emoticons/emotion-1.gif' alt=':)' />)<br /><br />Anshu
  13. satya Moderator

    SQL Express 2005 is like MSDE you will not get any GUI tools with it and you need to install SQL Management studio for GUI tools.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. Adriaan New Member

    ... or add ODBC table links to your Office application and upload the data that way.
  15. anshubansal2000 New Member

    Hi Madhivanan,

    I just tried by the way you suggested. But I am sorry to say that it is showing an error that AD-Hoc Qyery should be on.
    I tried to switch it on by SQL Server Express Edition. But I am sorry to say that in SQL Server Express Evaluation Version it is not showing the option for AD-Hoc Queries.
    Please let me know how to switch it on so that I can run the query.

    Thanking You.
    Anshu
  16. Madhivanan Moderator

  17. anshubansal2000 New Member

    Hi Madhivanan,

    Thanking you for providing your suggestions. But I am sorry to say that I am unable to find to run any method for Ad-Hoc queries. I think there is no way to switch it on in SQL server express 2005.

    If you have any idea then please let me know.
    Thanking You,
    Anshu
  18. satya Moderator

    As a first doubt can you confirm whether you're providing/managing this data for production purpose or for your training.

    One of the MSDN article refers ...some features are removed from SQL Server Express compared to MSDE. These features include DTS, replication publishing, and SQL Agent.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  19. anshubansal2000 New Member

    Hi MAdhivanan,
    Thanking you, SQL Express is running AD-Hoc Queries. I also have one more novice question. In the query you provided, where to put the name of the column inthis query.
    Also should I provide the SheetNumber of Excel Sheet.

    Please help me.
    Anshu
  20. Madhivanan Moderator

    You should use Sheet Name and not Sheet No

    Use Insert into tableName Select * from OpenRowset(..... Select City from [sheetName$])

    Madhivanan

    Failing to plan is Planning to fail
  21. anshubansal2000 New Member

    Hi Madhivanan,

    I am just running this query

    INSERT INTO city (cityName) // Where cityName is the name of the column in the city table//
    SELECT *
    FROM
    OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=c:city.xls; HDR=YES',
    'SELECT A FROM [city]'
    ) AS derivedtbl_1

    but it is showing error that can not get the column information from the OLE database provider.
    Please help me if you see any mistake in this query.

    Thanking You
    Anshu
  22. Madhivanan Moderator

    If the sheet City has the column(heading) named A it will work correctly

    Madhivanan

    Failing to plan is Planning to fail
  23. anshubansal2000 New Member

    Hi Madhivanan,

    Thanking you for your reply. I am executing query like this
    INSERT INTO city (cityName)
    SELECT *
    FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=c:city.xls; HDR=YES',
    'SELECT * FROM [city]') AS derivedtbl_1

    so I Think now it should execute because we are selecting *. But I am sorry to say it is again showing error that can not have column information of OLE data provider.

    Please let me know where to look for that.

    thanking you very very much
    Anshu
  24. Adriaan New Member

    Can you run the SELECT part only, and see how many columns are returned?

Share This Page