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 |
|
dringi
Starting Member
7 Posts |
 Posted - 2005-11-27 : 13:11:29
|
| hi,i want to import from excel, data into an existing table in access (overwriting..) using sql. can anyone help me with the right code??thanks |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-11-27 : 17:28:07
|
| Hi dringi1. When you say overwrite - do you mean delete and then insert, or do you mean that you only want to update certain columns?2. Do you want to use VBA code for the whole procedure? You can't do it all in SQL unless you have a table linked to the Excel sheet...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
dringi
Starting Member
7 Posts |
Posted - 2005-11-28 : 14:37:29
|
yes I mean delete and insert the new data into the table.i need to update this table on a baily basis......how can i do?? quote]Originally posted by rrb
Hi dringi1. When you say overwrite - do you mean delete and then insert, or do you mean that you only want to update certain columns?2. Do you want to use VBA code for the whole procedure? You can't do it all in SQL unless you have a table linked to the Excel sheet...--I hope that when I die someone will say of me "That guy sure owed me a lot of money"[/quote] |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-28 : 16:56:52
|
| I would simply create a DTS package that would do a TRUNCATE of the table, and then do the importing, and then simply schedule that DTS package to run each night. (This would be to do a complete wipe of all of the existing data in the table and essentially start over with just the data from your excel sheet.) The DTS wizard should be able to walk you through creating the Import of the data from Excel perfectly. Then choose to Schedule it. You can either edit the DTS package if you know what to do, or just edit the Job that was created per the schedule and insert a new task for the job to run before the import that would just contain the single query task "Truncate myTable" |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-11-28 : 17:25:57
|
| unless you're using access -in which case there is no DTS.dringi - are you using MS Access, or SQL Server to update an MS Access database?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-29 : 01:26:38
|
| If you use Access then Open the table and delete all records;copy the EXCEL data and paste over thereMadhivananFailing to plan is Planning to fail |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-12-01 : 17:11:52
|
| Sorry Dringi - is this in Access? Do you want to do it in VBA?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
import table