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 |
|
banksidepoet
Starting Member
2 Posts |
 Posted - 2012-07-18 : 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
3608 Posts |
Posted - 2012-07-18 : 04:54:36
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926Or use SSIS or similar. |
 |
|
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
Posted - 2012-07-18 : 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.htmlIn 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-18 : 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.htmlIn 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
prett
Posting Yak Master
212 Posts |
Posted - 2012-07-19 : 05:53:39
|
| I always prefer SQL Server Import and Export Wizard to import a spread sheet into an existing sql table. Here is all information about SQL Server Import and Export Wizard : http://msdn.microsoft.com/en-us/library/ms140052.aspx |
|
|
|
pladrounf
Starting Member
3 Posts |
Posted - 2014-08-23 : 08:27:38
|
| Restore and recovery of SQL database is an important factor that play an sophisticated role in corruption or disastrous situations. In such critical scenario of disaster the use of Recovery Toolbox for SQL Server is relevant and secure option. Get it from here http://www.sql.recoverytoolbox.com/[url][/url] |
|
|
|
|
|
|
|
|
Excel Import