August 25, 2004 at 6:15 pm
I need to copy the data in two spreadsheets into two tables in "SQL Server 2000". Below are the details:
One spreadsheet contains the data that needs to be added to an existing table in SQL server. All field names in the spreadsheet match the DB table column names.
Another spreadsheet contains the data that needs to be copied to a new DB table. The table currently does NOT exist in the DB.
Would you please point to me how to accomplish this through "SQL Server Enterprise Manager"
August 26, 2004 at 9:44 am
DTS Import Data wizard will work, or right-click tables..all tasks..import data.
1. Select Excell for your datasource..next
2. destination should be sql server and database you want
3. copy tables
4. select the worksheet(s) that you wish to import
You said one worksheet goes to existing table and the other goes to a new table. Select existing sql table for the destination of the existing table. The new table will be created for you; the name can be modified if you like.
4a transform the data/datatypes and edit the sql, if necessary
Next
5. Finish (run immediately)
August 26, 2004 at 9:49 am
Thank you!!!!
August 27, 2004 at 6:52 am
The only thing I would add is that if this a regular requirement and the spreadsheets and tables retain the same names before executing the package you can save it. it can then be sceduled to run at predetermined times as well as being available to run manually.
In the wizard there are options for dropping and creating tables so you have the option to replace data in the target tables or append data to existing tables.
August 30, 2004 at 6:23 am
They want to append the data, but this is the problem I'm having now...
The first row of the spreadsheet matches the column heading in the table - but when I go to import the spreadsheet, it is showing me THREE seperate tables or views on the Select Source tables/views screen. I don't know why this is if I'm importing from ONE spreadhsheet... unless the spreadsheet is formatted incorrectly. I went ahead and selected the first one and set my destination table and I when I run it, i get an error: Violation of Primary Key constraint 'PK_Faultresolutions' cannot insert duplicate Key in FaultResolutions.
maybe this is somethig simple.. but can anyone help??
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply