SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Excel Import
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

banksidepoet
Starting Member

2 Posts

Posted - 07/18/2012 :  04:23:26  Show Profile  Reply with Quote
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
3608 Posts

Posted - 07/18/2012 :  04:54:36  Show Profile  Reply with Quote
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Or use SSIS or similar.
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
94 Posts

Posted - 07/18/2012 :  05:50:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/18/2012 :  10:42:14  Show Profile  Reply with Quote
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

USA
203 Posts

Posted - 07/19/2012 :  05:53:39  Show Profile  Visit prett's Homepage  Reply with Quote
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 - 08/23/2014 :  08:27:38  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000