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.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.|||Better check relational integrity issues first.|||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|||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.|||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|||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.|||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?|||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.
