First Bulk Insert is not working Incorrect syntax near '@roleid' error

[vba317]

I am trying to develop my first bulk insert in SQL 2005 without much luck. I have a table called dbo.dic_Client_RoleAssign the table has three fields in it : clid,roleid and usrid. I have another table called dbo.dic_Client this table has three fields that I am interested in clid, compid and arch. What I am trying to do is if compid and arch are certain values I want all the clid values from the dbo.dic_Client table to be inserted into the dbo.dic_Client_RoleAssign table with some variables assigned by me. I am currently getting an error Incorrect syntax near '@roleid' on line 29 which is the WHILE @@fetch_status = 0 line. I thought I would have to do a bulk insert because the Client table has hundreds of values in it. If there is an easier way by all means I am open to it.

Any help is appreciated
Tom

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sproc_RoleAssign]

AS


/***********************************************************************
********************** LOOP FOR ROLE ASSIGN ****************************
************************************************************************/

-- Declare Bulk Insert
--DECLARE bulk_insert CURSOR FOR
DECLARE @clid int,@roleid int,@usrid int

SET @roleid = 2
SET @usrid = 306

-- Get a list of the files to bulk insert with fields for names
SELECT clid,compid,arch
FROM rptdata_ahs.dbo.dic_Client
WHERE compid = 1 or compid = 7 and arch = 0 
ORDER BY compid;

-- Start Loop
OPEN bulk_insert
FETCH next FROM bulk_insert INTO @clid,@roleid,@usrid
WHILE @@fetch_status = 0
BEGIN


INSERT INTO dbo.dic_Client_RoleAssign(clid,@roleid,@usrid
SELECT clid
FROM rptdata_ahs.dbo.dic_Client


---- Loop to Next
FETCH next FROM bulk_insert INTO @clid,@roleid,@usrid -- Next Record
END
--
---- Close Bulk Insert
CLOSE bulk_insert
DEALLOCATE bulk_insert

 

[gmmastros]

Bulk insert is meant to load a lot of rows in to a table all at once. It is a very powerful, and very fast method for doing so.

Most people I know will create a temp table, bulk insert in to the temp table, and then process the data (which often includes loading the data in to a real table).


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom