3

I'm converting a database from Excel onto MS SQL server and am basically clueless.

The Excel file has column headings of GroupID, Name, Members, Remarks

The SQL table has the same fields.

When I update the SQL some records are totally new, so need to be appended, others need a column or two updated, while most records need nothing at all. So far I've taken the lazy way out & truncated the file & appended everything back in, but what's the proper way?

Deina Underhill
  • 557
  • 1
  • 9
  • 23
  • 1
    Import the file as a separate table and you can do all your updates from there. Depending on your version of SQL server you may be able to use the MERGE statement. It shouldn't take too long to knock up an insert, and an update statement. – twoleggedhorse Jan 09 '13 at 17:58
  • @twoleggedhorse That should probably be an answer, you know, since it is an answer (and a good one at that) and all. – WhoaItsAFactorial Jan 09 '13 at 17:59
  • Fair enough, I've made this an answer :) – twoleggedhorse Jan 09 '13 at 18:01

1 Answers1

6

Import the file as a separate table and you can do all your updates from there. Depending on your version of SQL server you may be able to use the MERGE statement. It shouldn't take too long to knock up an insert, and an update statement.

Something like this for the update:

UPDATE o
SET    name = i.name
FROM   originaltablename o
       INNER JOIN importedexceltablename i
           ON o.GroupID = i.GroupID
WHERE  o.name <> i.name

And something like this for the insert:

INSERT INTO originaltablename
SELECT i.*
FROM   importedexceltablename i
       LEFT JOIN originaltablename o
           ON o.GroupID = i.GroupID
WHERE  o.GroupID IS NULL

Be careful though, this is just an example to get you going as you haven't given enough information for a proper solution.

twoleggedhorse
  • 4,938
  • 4
  • 23
  • 38
  • Thank you much! That worked fine the other day, but seems to be moofing today for some reason I keep getting an **"Msg 208, Level 16, State 1, Line 3, Invalid object name 'o'." error The complete code I'm using is: `USE [Boats] --select * from [dbo].[Groups] as o --SELECT * -- FROM [dbo].[Groups$] as i ------ WHERE ID is null; GO INSERT INTO o SELECT i.* FROM [dbo].[ImportGrp] i LEFT JOIN [dbo].[Groups] o ON o.ID = i.ID WHERE o.ID IS NULL PRINT 'Number of rows added is ' + CAST(@@ROWCOUNT as char(6)); ` – Deina Underhill Jan 14 '13 at 05:41
  • Try changing the line "INSERT INTO o" so that it reads "INSERT INTO [dbo].[Groups]" – twoleggedhorse Jan 14 '13 at 09:32