Can I avoid a cursor in this multiple row insert? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Can I avoid a cursor in this multiple row insert?

Author

Topic

sixside
Starting Member

12 Posts

Posted - 2007-11-20 : 15:41:39

I've got a pretty simple setup in which I'm trying to insert multiple rows into a table using a split function that returns a table. The problem is that rows must be unique so two of the columns have a primary key.

Table Def:


CREATE TABLE [dbo].[UserChores](
	[UserId] [int] NOT NULL,
	[ChoreId] [int] NOT NULL,
 CONSTRAINT [PK_UsersChores] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC,
	[ChoreId] ASC
)

Here's my Split UDF:


CREATE FUNCTION [dbo].[Split]
(	
	@List nvarchar(2000),
	@SplitOn nvarchar(5)
)
RETURNS @RtnValue TABLE 
(
	[Id] int identity(1,1),
	[Value] nvarchar(100)
)
AS
BEGIN

	WHILE (Charindex(@SplitOn,@List)>0)
	BEGIN

		INSERT INTO @RtnValue (Value)
		SELECT Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

		SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
	END


	INSERT INTO @RtnValue (Value)
    SELECT Value = ltrim(rtrim(@List))

    RETURN
END

Now I'd like to be able to just pass a CSV into to a SPROC that does the insert into UserChores, so something like this:


CREATE PROCEDURE [dbo].[UserChores_INS]

@UserId int,
@ChoreIdList varchar(50)

AS

INSERT INTO [dbo].[UserChores]
	([UserId] ,[ChoreId])
	SELECT @UserId, [Value] 
	FROM
	dbo.Split(@ChoreIdList , ',')

But IF the UserChores table already has a row for one of the records trying to be inserted by the sproc, the entire insert statement will fail. I'd like it to insert all rows that it can, and discard any rows that would be duplicates and cause the entire insert to fail.

I can think of how to accomplish this using a cursor and an IF NOT EXISTS statement, but was just wondering if there is a better way?

Thanks!

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-11-20 : 17:34:33

You want to insert only the values that are missing from the destination table, correct?


INSERT INTO [dbo].[UserChores]
	([UserId] ,[ChoreId])
	SELECT @UserId, [Value] 
	FROM
	dbo.Split(@ChoreIdList , ',') s
	left
	join	dbo.UserChores uc on
			s.Value = uc.ChoreID
	where	uc.ChoreID is null

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources