Updating associated table with the values imported from a comma-separated file

I have three tables:

• Role
• Permission
• RolePermission (link table with just role id and permissionid)

I have a stored procedure that updates role

If I am passing permissionids associated with the role to the update stored procedure, I can use the开发者_开发百科 function [dbo.Split][1] to split them good.

But after that how can I update rolepermission table?

Do I need to loop? And if so: how?

---

One possible way you could do this (lacking detailed information about your data - so guessing at times) would be:

• BULK INSERT your CSV file into a temporary table, so you have both RoleID's and Permission ID's available (guessing that you have both in the CSV)

• then use a transaction and several T-SQL statements to put the data into the proper places.

Step 1: BULK INSERT

You would need to use BULK INSERT to get your data into a temporary table:

CREATE TABLE #BulkImportTemp (RoleID INT, PermissionID INT)

BULK INSERT #BulkImportTemp
FROM 'c:\yourcsvfile.csv'
WITH
  (FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n')
GO

SELECT * FROM #BulkImportTemp

Step 2: update the necessary tables

Now, once you have all that data in a temporary table, you can update the necessary tables:

START TRANSACTION

UPDATE dbo.Role
SET ....... 
FROM #BulkImportTemp tmp
WHERE ........

UPDATE dbo.Permission 
SET ....... 
FROM #BulkImportTemp tmp
WHERE ........

INSERT INTO dbo.RolePermission(....) 
  SELECT ........
  FROM #BulkImportTemp tmp
  WHERE ........

COMMIT

---

UPDATE [ROLE]   SET
 ProductId=@ProductId,
 [Name]=@Name
WHERE RoleId=@RoleId

DELETE FROM RolePermission  WHERE  RoleId=@RoleId
INSERT INTO RolePermission 
SELECT @RoleId, P.Items
FROM dbo.Split(@PermisionIds, ',') P