Insert in Excel & Get in SQL table

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

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Insert in Excel & Get in SQL table

Author

Topic

notes4we
Yak Posting Veteran

90 Posts

Posted - 2008-09-18 : 13:48:04

Hello Friends,

I have got a requirement in which I have an Excel Workbook (latest version) as my source and SQL Server table as my destination. The scenario is that I have similar column names in both - source and destination.

So, now what I have to achieve is, if I insert some value in my excel sheet, then it should get updated in the SQL Server destination table. I have to avoid using SSIS for this.

I am not even sure if there is anyway to do this. I am researching on it and I would appreciate your help if you can also let me know your ideas.

Thank you,
notes4we

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 13:51:52

Have a look at OPENROWSET function in books online.I think what you need is something like

UPDATE d
SET d.Field1=s.Field1,
...
FROM Dest_Table d
INNER JOIN (SELECT * FROM OPENROWSET(..,'Select * FROm $Sheet1'))s
ON s.PKCol=d.PKCol

notes4we
Yak Posting Veteran

90 Posts

Posted - 2008-09-19 : 09:52:29

Hello,

Thank you for your reply.
I am sorry, but I am not able to get exactly where do you recommend me to use this query practically?
I mean, my aim is to set up something in an excel sheet that will help me to avoid opening the SQL Server table, yet insert all the data in it.

So, in this scenario, where do I use the query and how?
I pardon for my ignorance, but I am still learning, so.

Thank you once again.
- notes4we

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 09:55:42

quote:
Originally posted by notes4we

Hello,

Thank you for your reply.
I am sorry, but I am not able to get exactly where do you recommend me to use this query practically?
I mean, my aim is to set up something in an excel sheet that will help me to avoid opening the SQL Server table, yet insert all the data in it.

So, in this scenario, where do I use the query and how?
I pardon for my ignorance, but I am still learning, so.

Thank you once again.
- notes4we

i thought what you were asking for was to update sql table with updated data in your excel sheet (thats what your first post told)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-19 : 09:58:24

Use SSIS and it will append the data with SQL Server agent job.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 10:03:38

quote:
Originally posted by sodeep

Use SSIS and it will append the data with SQL Server agent job.

OP says he wants to avoid using ssis

Posted - 09/18/2008 : 13:48:04
Hello Friends,

I have got a requirement in which I have an Excel Workbook (latest version) as my source and SQL Server table as my destination. The scenario is that I have similar column names in both - source and destination.

So, now what I have to achieve is, if I insert some value in my excel sheet, then it should get updated in the SQL Server destination table. I have to avoid using SSIS for this.

I am not even sure if there is anyway to do this. I am researching on it and I would appreciate your help if you can also let me know your ideas.

Thank you,
notes4we

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-19 : 10:12:34

Question to OP

WHY ?

notes4we
Yak Posting Veteran

90 Posts

Posted - 2008-09-19 : 10:14:06

As my boss wants it that way. So, I cannot argue with him, but I can try my best to find ways if this could be achieved, orelse I will explain him the situation.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-19 : 10:21:46

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

notes4we
Yak Posting Veteran

90 Posts

Posted - 2008-09-19 : 10:27:56

Madhivanan,

I went over the link that you have provided, but my case is the other way round. My aim is: If I insert any values manually in the excel sheet, then those values should get inserted in the SQL Server table (having same columns as excel sheet) on its own.

So, I do not need to open or write any query to insert the data into the SQL Server table. Excel sheet should do it for me.

And your posted link explains the other way round.

Anyways, plz do let me know if you know someway to manage this.

Thank you,
notes4we

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 13:10:17

quote:
Originally posted by notes4we

Madhivanan,

I went over the link that you have provided, but my case is the other way round. My aim is: If I insert any values manually in the excel sheet, then those values should get inserted in the SQL Server table (having same columns as excel sheet) on its own.

So, I do not need to open or write any query to insert the data into the SQL Server table. Excel sheet should do it for me.

And your posted link explains the other way round.

Anyways, plz do let me know if you know someway to manage this.

Thank you,
notes4we

how frequently will your excel get updated? And how frequent you want updations to be reflected in your table?

notes4we
Yak Posting Veteran

90 Posts

Posted - 2008-09-19 : 16:03:52

I will be updating almost everyday. I wish to use Visual Basic to achieve this. I would appreciate if anyone of you can share some knowledge about VB code to insert the values in excel into SQL server table.

Thank you,
notes4we

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources