Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kitfox69
Starting Member
13 Posts |
 Posted - 2008-05-15 : 10:22:24
|
| A little background first.I have been taking Oracle classes to learn SQL structure and statements. I have also used that knowledge to start working with MySQL to create a pricing structure outside of our MS SQL database.I am obviously still learning how to work with MS SQL Server 2005 and I am starting to see that there a A LOT of differences amongst the three databases.My question begins here:1. I want to import an excel spreadsheet to a temporary or actual table newprice (does not matter which, temp or actual). The spreadsheet contains two columns of data, A) item_id which holds all of our SKUs and B) item_prc_new which are my newly calculated and rounded prices. I am dealing with over 13000 SKUs that need to be updated and taking three days and 2 people to enter them one by one is not my cup of tea.2. Once I have that table in the same database as our production item table (I may be forced to import it into another database named test on the same server instead of the production database as downtime is limited for that database) I want to use this statement to update those 13000 SKUs in the newprice table to the production item table using:update item set item_prc_2 = test.newprice.item_prc_new where item.item_id in (SELECT item_id from test.newprice.item_id)What I need to know is:1. How to see what formatting is set on the item.item_id column so I can match it in test.newprice.item_id2. The easiest or most efficient way to import that spreadsheet c:\newprice.xls into the test databaseBrooks C. DavisIT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture HomestoresDELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-15 : 10:50:41
|
| To do the import from excel really depends on the environment and how often you need to do it.If it's a one off I would just create an insert statement as an expression, copy it down all rows, copy the result into a query window and run it.The expression would be something like="insert mytbl (SKU, Price) select '" & A1 & "','" & B1 & "'"If you need to do it more frequently and want to automate it then SSIS or openrowset would be the way to go - this would mean getting the file to somewhere accessible from the server unless you want to automate it on a client machine.To copy the data into another database just execute an insert statement using the database nameinsert mytbl (sku, price) select sku, price from otherdatabase..mytblThe format you end up with wilkl depend on the excel spreadsheet, destination column and method used. Try it and see. Check with the source to see that you are not losing preceision. Often it's best to use a character format as an intermediary.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-15 : 10:54:13
|
| The exporting can be done using SSIS Export/Omport or BULKINSERT or bcp methods.The formatting can be set by specifying a format file during export in bcp or BULK INSERT methods or by applying appropriate transformations in SSIS. Refer this:-http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.htmlhttp://www.developerfusion.co.uk/show/5357/http://www.builderau.com.au/program/sqlserver/soa/How-to-import-an-Excel-file-into-SQL-Server-2005-using-Integration-Services/0,339028455,339285948,00.htm |
|
|
|
|
|
|
|
|
Data Import Help