To DTS or NOT DTS when update sql from access?
-
HI. I am trying to update a sql table from an access table but the records that are being updated into the sql table already exist in the sql table so they first must be deleted. Is there any way this can be done in DTS using your own query in the wizard walk through? I've never manipulated sql tables so I'm not real savy on how to do these simple things. The incoming table could be sql as well and i would guess in that circumstance, sql code could be written in query analyzer. If the dts cannot be used where the incoming source is access, i'm not sure how to do this? would i need to turn the access table into a sql table first, and then work between 2 sql tables.
Sorry so wordy..
Juanita
-
Hey There Juanita,
The approach you mention about loading the Access data into an auxilary SQL table and then working off that table seems like a reasonable approach. I would stay away from DELETEing existing records since the delete operation may inadvertently delete records in other dependent tables.
One way to load the data into an auxilary table could be:
(1) Create an auxilary table (example: Access_Auxilary_Table) in your SQL Server database schema with all the columns which will be loaded from the Access table
(2) Create a linked server to the Access database (for info on how to do this, refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp)
(3) Create a stored procedure that looks something similar to this:
CREATE PROCEDURE dbo.usp_uploadAccessTable
AS
BEGIN
SET NOCOUNT ON
BEGIN TRAN
IF ( EXISTS (SELECT 1 FROM Access_Auxilary_Table) )
-- removing residual records from auxilary table
DELETE FROM Access_Auxilary_Table
IF ( @@ERROR != 0 ) GOTO Upload_Failed
-- uploading Access records into auxilary table
INSERT INTO Access_Auxilary_Table
SELECT * FROM OPENQUERY(<linked server name from step 2 goes here>, '<SQL query to retrieve data from actual Access table goes here>')
IF ( @@ERROR != 0 ) GOTO Upload_Failed
-- updating existing SQL records with corresponding Access records
UPDATE SQL_Table
SET <pairs of "a.<sql column goes here> = b.<auxilary access column goes here>" to update go here>
FROM
SQL_Table a, Access_Auxilary_Table b
WHERE
a.AccessTable_PrimaryKey = b.AccessTable_PrimaryKey
IF ( @@ERROR != 0 ) GOTO Upload_Failed
-- inserting Access records which do not exist in SQL table
INSERT INTO SQL_Table
SELECT * FROM Access_Auxilary_Table a
WHERE NOT EXISTS (SELECT 1 FROM SQL_Table s WHERE s.AccessTable_PrimaryKey = a.AccessTable_PrimaryKey)
IF ( @@ERROR != 0 ) GOTO Upload_Failed
GOTO Upload_Success
Upload_Failed:
ROLLBACK TRAN
RETURN (1)
Upload_Success:
COMMIT TRAN
RETURN (0)
END
(4) Configure the DTS package to call the stored procedure from step 3
JP
-
Thanks, I'll playaround with this and give it a try.
Juanita
-
Hey Juanita,
I corrected the UPDATE statement in the code in my previous post. I noticed that I originally posted the UPDATE like this:
UPDATE SQL_Table a
SET <pairs of "sql column = auxilary access column" to update go here>
FROM
Access_Auxilary_Table b
WHERE
a.AccessTable_PrimaryKey = b.AccessTable_PrimaryKey
This original update would not update anything in SQL_Table since SQL_Table was not included in the FROM clause.
The corrections are found in my previous post ( just refer to bold stuff )
JP
-
Thank you so much !! Got it !
Juanita
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply