July 23, 2003 at 11:49 am
Is it possible in a DTS package to delete rows from a table when there is a match in a specific column in an EXCEL file? For example, if acct_id is 1000 in the table, and row 14 in Column A in the EXCEL file is 1000, then delete the row from the table.
July 23, 2003 at 3:26 pm
Create linked server to Excel File.
sp_addlinkedserver N'Excel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0',
N'c:\data\MySheet.xls', NULL, N'Excel 5.0'
GO
sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO
And run query as following.
delete account
where acct_id IN (SELECT ColumnA FROM [FROM EXCEL...Sheet1] where ColumnA = 1000)
July 23, 2003 at 3:28 pm
I would try to import the Excel sheet in a (temporary) table and then do the join that way. Probably offers the best performance.
If that's not possible, you could use ActiveX scripting task to get the necessary data from the Excel sheet and put it in global variables or again in a temporary table.
July 24, 2003 at 7:49 am
quote:
Create linked server to Excel File.sp_addlinkedserver N'Excel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0',
N'c:\data\MySheet.xls', NULL, N'Excel 5.0'
GO
sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO
And run query as following.
delete account
where acct_id IN (SELECT ColumnA FROM [FROM EXCEL...Sheet1] where ColumnA = 1000)
I'm going to have to try this one.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply