Archive | September 2008

Stored Procedure – User Profile Import

I was asked to build a utility to import the user profiles from a CSV file to the FBA Database in Sharepoint. I then came up with a solution of moving the contents from the CSV to the Temporary table and then move the columns to the respective tables using INSERT…SELECT commands. I ‘m enclosing the SP that does exactly what i say.

1.Create a Temp Table : To hold the contents of the CSV file temporarily

USE [MyDatabase]

GO

CREATE TABLE [dbo].[Temp]

(
[EmployeeName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Salutation] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PhNo] [numeric](18, 0) NULL,

[EmailID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)


2.Use BULK INSERT to copy the contents from the CSV to the Temp Table

DECLARE @SQL VARCHAR(2000)
SET @SQL =’BULK INSERT Temp FROM ‘+@FilePath+’ WITH(FIRSTROW=2,FIELDTERMINATOR = ”,” , ROWTERMINATOR = ”\n”)’
EXEC(@SQL)
Parameters used –
FilePath is the input parameter for the SP
FIRSTROW indicates that copying should start from 2nd Row
FIELDTERMINATOR indicates comma separated values
ROWTERMINATOR indicates ROW terminator for the CSV

3.So,we have the CSV contents in the Temp table. Our job is now to move the columns to the respective tables using INSERT…. SELECT command. But,we should also consider the impediments in inserting contents directly to the respective tables because of the existence of the primary keys in the table .
However,we can look at this issue in 2 ways .One is inserting columns by setting primary key as read only and the other is , inserting columns by setting IDENTITY_INSERT “ON” where we explicitly mention values for the keys.

SET IDENTITY_INSERT [EmpTbl] ON
INSERT
EmpTbl(EmployeeName,EmployeeID,Salutation)
SELECT
EmployeeName,EmployeeID,Salutation FROM Temp
SET IDENTITY_INSERT [CLDirectory] OFF

As,mentioned before it can also be made read-only without setting IDENTITY_INSERT key like

INSERT EmpTbl(EmployeeName,EmployeeID,Salutation)
SELECT
EmployeeName,EmployeeID,Salutation FROM Temp

4.Once,we have inserted columns to the table we need to TRUNCATE and DROP the table.

TRUNCATE TABLE Temp
DROP TABLE Temp

Once,we have the SP we can then write a console/windows application for passing SQL parameters to execute the Stored procedure.

Advertisements