Importing from Excel into Datbase tables

Hi
I have data in excel file which I wanted to import into sql tables, and I
wanted to do this programatically since I have to move the data first to
master table and then use the Master record key to insert into child table s
o
that i can have relationship data.
How can I do this with DTS?
Sample code will be much appreciated...
MakarandYou can use OpenRowset() to query data from excel like a *normal* table.
This should allow you to do insert/update/delete to your sql table.
select * from
OPENROWSET('Microsoft.Jet.OLED_B.4.0','Excel
8.0;Database=C:\Data\test.xls'_,Sheet1$)
--
-oj
"Makarand Keer" <MakarandKeer@.discussions.microsoft.com> wrote in message
news:8B4688A0-C5BE-48BC-A023-63284616DC7B@.microsoft.com...
> Hi
> I have data in excel file which I wanted to import into sql tables, and I
> wanted to do this programatically since I have to move the data first to
> master table and then use the Master record key to insert into child table
> so
> that i can have relationship data.
> How can I do this with DTS?
> Sample code will be much appreciated...
> Makarand|||Hi
This sounds like you want to load the data into a holding table and then use
queries to distribute it from there.
Loading into the holding table is just the same as any import from Excel,
you can use the import wizard to initially create the package or do it
manually.
To moving data from the holding table is probably best undertaken if you
wrote a stored procedure. In your backage you could as and "Execute SQL
Task" to execute the procedure. You can test the procedure out from query
analyser without having to call/step the package all the time. Using
statements such as INSERT...SELECT will allow you to choose which columns
from the holding table map onto the destination columns of the live tables.
Books online has example of the INSERT statement and information about DTS.
This site also has many examples http://www.sqldts.com/default.aspx?101
John
"Makarand Keer" <MakarandKeer@.discussions.microsoft.com> wrote in message
news:8B4688A0-C5BE-48BC-A023-63284616DC7B@.microsoft.com...
> Hi
> I have data in excel file which I wanted to import into sql tables, and I
> wanted to do this programatically since I have to move the data first to
> master table and then use the Master record key to insert into child table
> so
> that i can have relationship data.
> How can I do this with DTS?
> Sample code will be much appreciated...
> Makarand