How to dynamically excel files to database 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)

How to dynamically excel files to database table

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-10-31 : 17:15:39

Hello,

I have hundred excel file to load to database table: Name. Here is template 1. Get id number from the excel 1 for example: id = 12 then
run query to get name: Jean: select name from tableOne where id = 12, name = Jean, then fill the excel template 1 to template 2

template 1

id name time term test
1 1 0
2 2 0
3 3 0
4 4 0
5 5 0
6 6 0
7 7 0
8 8 0
9 8 0
10 11 0
11 11 0
12 12 0

excel 1

id

12
34
13
45
67
....
....
100

template 2

id name time term test
12 Jean 1 1 0
12 Jean 2 2 0
12 Jean 3 3 0
12 Jean 4 4 0
12 Jean 5 5 0
12 Jean 6 6 0
12 Jean 7 7 0
12 Jean 8 8 0
12 Jean 9 8 0
12 Jean 10 11 0
12 Jean 11 11 0

then delete table name where id = 12, then load template 2 to name table.

Is there any way to complish it?

Thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 04:39:01

have an excel source and dump results onto a internediate table using oledb destination.
then add a execute sql task to do cross join between table and insert to destination

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

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-11-01 : 14:42:16

Thanks. I have several hundreds id number, query each id to get name and then make hundreds excel based on each id and name

id name time term test
1 1 0
2 2 0
3 3 0
4 4 0
5 5 0
6 6 0
7 7 0
8 8 0
9 8 0
10 11 0
11 11 0
12 12 0

Do you have detail example code to do it?

Thank you very much!

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-11-01 : 14:49:11

time term test id name
1 1 0
2 2 0
3 3 0
4 4 0
5 5 0
6 6 0
7 7 0
8 8 0
9 8 0
10 11 0
11 11 0
12 12 0

The id and name is empty

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources