Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Question Unanswered: Looking for easy way to update w/o SQL statements

    My manager is interested in knowing if there is a way to update our website's SQL database using a method with excel, similar to importing.

    The person who was previously in my position had imported a few hundred new products into the database with an excel spreadsheet.

    Now, we would like to make updates such as a price changes or similar adjustments to a number of the products in the database. We could use a web interface, but ours requires us to find each product individually and it takes too much time. I told him that it would probably be necessary to write an SQL statement to update the tables, but we're also interested in maintaining the integrity of the database and are worried about loosing data due to a typo. Is it possible to export the db contents to an excel file, make changes, and then merge those changes into the existing database? I have tried and failed, so I am wondering if any experienced users could help me out.

    Also, is there some kind of phpmyadmin for MS SQL? A free, open source alternative would be best.
    // Andrew Rosborough

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Create a view/function/stored procedure that will retrieve the data with desired values, bcp the data out using this object, truncate the original table, and bulk insert the data back in.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Better check relational integrity issues first.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    create view/function/stored procedure
    bcp...queryout...
    alter table <table_name> nocheck constraint all
    truncate table <table_name>
    bulk insert...
    alter table <table_name> check constraint all

  5. #5
    Join Date
    Mar 2004
    Posts
    14
    Is this in SQL Server Manger or some similar software? I guess what I'm saying is that I'm inexperienced with Microsoft SQL Server and it seems like your directions are too vague. Thanks for helping me though.
    // Andrew Rosborough

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is the Data in Excel?

    I would DTS the data to a staging table, write a sproc that performed audits on the file, and if it passed all the test do an insert

    MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2004
    Posts
    14
    Originally posted by Brett Kaiser
    Is the Data in Excel?

    I would DTS the data to a staging table, write a sproc that performed audits on the file, and if it passed all the test do an insert

    MOO
    The data is stored on the webserver, but we have tried exporting to excel and when I imported the changes a new table was made or something. It was kind of confusing to work with the Manager.
    // Andrew Rosborough

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by arosboro
    It was kind of confusing to work with the Manager.
    That's funny...it's always confusing to work with a manager....ooooh you probably meant Enterprise Manager...


    EM for short...

    And DTS....

    Is the data fixed width or delimited?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One of the things you need to worry about if you export your data, modify it, and then load it back into the database is whether any relational integrity is established between your table and other database tables, either logically or physically. You could potentially screw up your application if you are not carefull.

    The best way to modify data is, of course, through the interface, but failing that you really need to do your modifications within SQL Server, using SQL statements. If you are going to be using a SQL Server database then you or somebody else in your office needs to learn some of the basics, and what you are trying to do is probably not very complex. I am afraid that you are going to create and very complicated and fragile solution to this problem involving mutiple steps and technologies, when the issue could be resolved easily within SQL Server.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •