May 23, 2005 at 5:43 pm
Hi, I need to create a stored procedure to do the following task.
The stored procedure has to read an excel file, then insert data in a temporal table, then compare the temporal table with an existing table and insert rows that not exist and write the rows that already exist in other temporal table.
Does anyone has some sript to do this?
Thanks for your help.
Thanks for your help.
P.D.
I need to learn how to create sp's any advice or book suggestions?
May 24, 2005 at 2:50 am
Have you not looked at using DTS for this task? Seems like it's exactly what DTS was made for.......
Have Fun
Steve
We need men who can dream of things that never were.
May 24, 2005 at 5:50 am
I agree, DTS sounds like the way to go.
With regards to learning about SP's, Ken Hendersons Guru's Guide to T-SQL is excellent and covers so much about T-SQL. There is a book review section on this site under resources and a reveiw of this book can be found here.
http://qa.sqlservercentral.com/columnists/bknight/tsqlgurusguide.asp
Hope this helps,
Ed Phillips
Ed Phillips
May 24, 2005 at 3:21 pm
Actually I'm working with DTS but I need to call this functionality from an asp page, in other words, I have an application that needs to load data from an excel file and then write it into a table in sql server, this task needs to run when user desires.
Any idea?
Thanks everyone for your help.
May 24, 2005 at 11:22 pm
Hi,
You can call a DTS from VB application,I think the same way you can do with ASP also.
May 25, 2005 at 7:12 am
To answer part of your question: When you have the first Temporal table imported, the code below will add new records to the existing table and add the records that already exist to another temporal table (#Temporal2).
To test this, run the code to create the tables EXISTING and TEMPORAL1, then execute the two scripts that do the work.
This process depends on there being a field in the existing and temporal (imported) table that uniquely identifies each record (i.e. a primary key or other unique index field).
Hope it helps.
--Create #Temporal2 table and fill with roecords
-- that exist in the Existing table
SELECT T1.*
INTO #Temporal2
FROM Temporal1 T1
LEFT OUTER JOIN Existing E
ON E.PKField = T1.PKField
WHERE E.PKField is not null
--Add records to Existing table if they are in Temporal1
-- but not in Existing
INSERT INTO Existing
SELECT T1.PKField, T1.Name
FROM Temporal1 T1
LEFT OUTER JOIN Existing E
ON E.PKField = T1.PKField
WHERE E.PKField is null
--Create and populate the Exisiting and Temporal1 tables
CREATE TABLE Existing (PKField int, Name char(30))
GO
insert into Existing
Values('100','Albert')
GO
insert into Existing
Values('200','Bruce')
GO
insert into Existing
Values('400','Dave')
GO
CREATE TABLE Temporal1 (PKField int, Name char(30))
GO
insert into Temporal1
Values('300','Christina')
GO
insert into Temporal1
Values('400','Dave')
GO
May 27, 2005 at 4:33 pm
Finally, It's working.
I'm using DTS, I've created my source(xls), my destiny (SQL), Execute Slq Task and I'ts working.
Thanks everyone for your advices.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply