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
 Development Tools
 Other Development Tools
 inserting excel rows into SQL SERVER 2005

Author  Topic 

emyk
Yak Posting Veteran

57 Posts

Posted - 2012-04-04 : 23:07:06
I am working on excel spreadsheet upload process where users export their data via macro VBA into SQL SERVER 2005.

So far I am able to import data from the SQL server into an execl but I am being challenged with writing excel data into SQL SERVER database:

Here is what I am working on
Sub Connect2SQLXpress()

Dim oCon As ADODB.Connection
Dim oRS As ADODB.Recordset

Set oCon = New ADODB.Connection
oCon.ConnectionString = "Driver={SQL Native Client};Server=myserver;Database=DB; Trusted_Connection=yes;"
oCon.Open

Set oRS = New ADODB.Recordset
oRS.ActiveConnection = oCon
oRS.Source = "insert into table (ID,Name,State,Code) VALUES (Columns("A":"B":"C":"D"))"


oRS.Open


oRS.Close

oCon.Close

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing
MsgBox "All done!"

End Sub

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-04 : 23:21:29
why not use OPENROWSET or export import wizard for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-17 : 03:51:08
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Webtalk
Starting Member

2 Posts

Posted - 2012-10-11 : 00:53:15
I've learn some excellent stuff here.
unspammed
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-11 : 01:52:43
1) Powershell is very useful in these circumstances
2) Another more manual way is , generate the INSERT staments and run as a script : http://www.sqlserver-dba.com/2012/01/sql-server-generate-sql-insert-from-excel.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -