SQL Server Performance Forum – Threads Archive

DataLoading tools / Load data to multiple tables

I have around 400 rows in an excel sheet which we need to update/ insert into multiple tables of sql server.
Is there any DataLoading tools or any script we can write to load these data. To be more clear for example data like this empid orderid empname dateinput dateoutput trans
===== ======= ======= ========= ========== ======
1 201 anc 12/12/06 12/24/06 Y
so these kind of rows are in excel sheet and coluumns like
empid, empname belong to emp table
orderid belong to order table
dateinput, dateoutput, trans belong to date table So we need to update / insert these records into tables of sql server 2005. how to import them to sql server 2005? or can we write any script to do this job?
Are there any dataloading tools to achieve this.
Any ideas? thanks.[8D]

---

What about using SSIS(previously DTS)? Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

As said, either you can use SSIS (ETL Tool in SQL Server 2005) or write OpenQuery to get the excel data in SQL Platform. Madhu

---

YOu can use OpenRowset…or BULK INSERT too… Replace the file name and sheet name… select * from OpenRowSet(
‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:Test.xls;HDR=YES;IMEX=1’
,’select * from [sheet1$]’
)
MohammedU.
Moderator
SQL-Server-Performance.com

---

Can u guys be more specific. I tried using the SSIS but i can import to a single table in the db. Thanks.

---

This can be done using SSIS also,but i feel this is better…. empid, empname belong to emp table Insert into Emp (EmpID,EMPName)
select empid, empname from OpenRowSet(
‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:Test.xls;HDR=YES;IMEX=1’
,’select * from [sheet1$]’
) orderid belong to order table Insert into Order (OrderID)
select OrderID from OpenRowSet(
‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:Test.xls;HDR=YES;IMEX=1’
,’select * from [sheet1$]’
) dateinput, dateoutput, trans belong to date table Insert Into Date (ut, dateoutput, trans)
select dateinput, dateoutput, trans from OpenRowSet(
‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:Test.xls;HDR=YES;IMEX=1’
,’select * from [sheet1$]’
)

---

]]>