October 23, 2013 at 2:53 pm
I am trying to write my first bulk insert without much luck. I have a table with three fields in it. I have set the variable for two of the fields. I am having problems with the third variable clid. I have a table that I can select the information from but when I place it in the bulk insert it fails. How can I get the values from the variable clid into the declared variale @clid?
DECLARE @clid int,@roleid int,@usrid int
SET @roleid = 2
SET @usrid = 316
-- Get a list of the files to bulk insert with fields for names
SELECT clid
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 clid
-- Loop to Next
FETCH next FROM bulk_insert INTO @clid,@roleid,@usrid -- Next Record
END
-- Close Bulk Insert
CLOSE bulk_insert
DEALLOCATE bulk_insert
October 23, 2013 at 3:06 pm
After thinking about the issue I decided to go with a regular insert instead of a bulk insert and everything worked out great.
INSERT INTO dbo.dic_Client_RoleAssign(clid,roleid,usrid)
SELECT clid,2,316
FROM rptdata_ahs.dbo.dic_Client
WHERE compid = 1 or compid = 7 and arch = 0
ORDER BY compid;
October 23, 2013 at 4:32 pm
I suppose that you went through the right path. I'm not sure why were you using a cursor, but a simple insert is a lot better.
A bulk insert is used to insert rows to a table from a file and you're inserting rows from a table so a bulk insert won't work for this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply