quote:

---

Originally posted by Lozt

Hi Brett,

No.

An example - I have 20 employees who move desk so, instead of altering & entering code 1 by 1, I use formulas in excel eg

Column A - add extensions
Colum B - add all 20 desk locations
Column C - Add: ".="UPDATE dbo.person SET Desk = '" & B1 & "' WHERE Extension = '" & A1 & "'""

Then copy (drag) from C1 to C20

I then copy the resulting code to Query Analyzer.

Hope this is clear.

L

---

-- Example of your table
CREATE TABLE #myPerson99 (LastName varchar(50), Phone varchar(50))
GO

-- Put Rows in to update, leave 1 out for the need to Insert
INSERT INTO #myPerson99(LastName, Phone)
SELECT 'Jonesy', '02874 012450' UNION ALL
SELECT 'Marky Mark', '02874 023450'

-- Show The data in the Table as it stand BEFORE DML

SELECT * FROM #myPerson99

-- Example of how I would not use Excel and just create a table variable

DECLARE @excel table (LastName varchar(50), Phone varchar(50))
INSERT INTO @excel(LastName, Phone)
SELECT 'Jones', '02874 012450' UNION ALL
SELECT 'Hamill', '02874 023450' UNION ALL
SELECT 'Sayeed', '02874 212450'

-- Now just perform a Set update..inner join means existance

	UPDATE t
	   SET LastName = e.LastName
      FROM #myPerson99 t
INNER JOIN @excel e
		ON e.Phone = t.Phone

-- INSERT uses non existence

INSERT INTO #myPerson99 (LastName, Phone)
	 SELECT e.LastName, e.Phone
	   FROM @excel e
WHERE NOT EXISTS(SELECT * FROM #myPerson99 t
				  WHERE e.Phone = t.Phone)

-- Let's Take a look

SELECT * FROM #myPerson99
GO

DROP TABLE #myPerson99
GO