| Author |
 Topic  |
|
|
banksidepoet
Starting Member
2 Posts |
 Posted - 07/18/2012 : 04:23:26
|
Hi.
Hope the answer to this is simple.
I use Excel 2010 (although I could save my spread sheet in an earlier format if necessary).
I use Sql Server 2008 Web Edition.
I need a method to import a spread sheet (possibly thousands of rows and certainly 10 - 20 columns) into an existing sql table overwriting what is already there.
I can create the spread sheet so that the columns match the database columns exactly (I imagine this is a prerequisite).
Can anyone help me with the method to do this. I use Sql Server Management Studio if that helps.
Please don't assume a high level of Macro or SQL Server experience on my part as, despite the fact that I use Excel and Sql Server every day, I have never done anything remotely like this, so detailed explanations please.
Thanks.
|
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3560 Posts |
|
|
mmkrishna1919
Yak Posting Veteran
India
54 Posts |
Posted - 07/18/2012 : 05:50:16
|
Hi banksidepoet,
plz refer below link for simple resolution no need much sql knowledge.
http://mysqlpages.blogspot.ca/2012/07/import-data-from-excel-to-sql-server.html
In this link explained only based on two columns,you need to populate simmilar formula for your 10-20 columns and don't forget to add single quote for char/varchar or date data type columns.
it will insert new rows into table, it won't overwrite existing records,we have to delete existing records and then procedd with import.
M.MURALI kRISHNA |
Edited by - mmkrishna1919 on 07/18/2012 05:58:46 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 07/18/2012 : 10:42:14
|
quote:
Originally posted by mmkrishna1919
Hi banksidepoet,
plz refer below link for simple resolution no need much sql knowledge.
http://mysqlpages.blogspot.ca/2012/07/import-data-from-excel-to-sql-server.html
In this link explained only based on two columns,you need to populate simmilar formula for your 10-20 columns and don't forget to add single quote for char/varchar or date data type columns.
it will insert new rows into table, it won't overwrite existing records,we have to delete existing records and then procedd with import.
M.MURALI kRISHNA
whilst ita a good method for manually loading data from execl, this cant be automated in any manner as it involves copying and running query generated by excel onto sql window.
in practical situation you normally schedule activities like excel loading as a sql agent job. In such cases best approach is to use ssis export import,OPENROWSET etc which we can always automate in a job.
For adhoc loading this is a cool method though
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
prett
Posting Yak Master
USA
161 Posts |
|
| |
Topic |
|
Excel Import
