SQL Command Line Consolidation

From: G. Benson (anonymous_at_discussions.microsoft.com)
Date: 02/21/04 

Date: Fri, 20 Feb 2004 19:41:05 -0800

My primary objective is to link an Excel work*** with an SQL table, and then update the LOG_SOURCE column of the SQL table with the values from the Excel work*** (using ITEMNO as the link). I'd like to consolidate the following code or simplify the entire process, if possible. Here is a brief description of my actual data:

Excel File name = inventory.xls
File location = C:\Documents and Settings\POS\Desktop\inventory.xls
Excel current work*** sample (both columns are 16 character fields):

     ITEMNO LOG_SOURCE
A1 13110 B1 White 3 Ring
A2 13111 B2 White 3 Ring
A3 13112 B3 09/09/03
A4 13113 B4 09/09/03
A5 13114 B5 09/09/03

SQL Table Name = ITEMS
SQL current table sample (both columns are 16 character fields):

ITEMNO LOG_SOURCE
13110
13111
13112
13113
13114

I apologize in advance for my limited SQL experience, but here is the code that I've collected thus far to perform the link:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\POS\Desktop\inventory.xls',
NULL,
'Excel 5.0'

(not sure what I should change Excel version name to if I'm using Microsoft Office Excel 2003)

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd *** as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

(I wonder if I could avoid defining table ranges to save a step or two and simply use column ranges -in other words, what commands could we eliminate by using Sheet1 A1:A5 & Sheet1 B1:B5 ?)

(what part of your code triggers the actual update of the SQL server's LOG_SOURCE column with the Excel values?)

Here is the code I've collected thus far to perform the update:

update Items set log_source = t1.log_source

update items
set
items.LOG_SOURCE = t1.LOG_SOURCE
from t1
inner join Items
on items.ItemNo = t1.ItemNo

Also, does DTS come as part of MSDE & if so, how do I access it? Would I save any time by using DTS rather than the procedures above?

Thank you in advance for your time and expertise.