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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Excel Import

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=49926

Or use SSIS or similar.
Go to Top of Page

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.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
Go to Top of Page

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.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/

Go to Top of Page

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
Go to Top of Page

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]
Go to Top of Page
   

- Advertisement -