bcp is giving me a strange error, which I cannot solve by myself. I tried to Google it without any success.
Interestingly, it works on my local machine but it does not work on others :)
Now the details. I need to bulk insert some data into the database. The challenge is that two tables have to be updated at the same time, as they are linked with one-to-one relationship. The first table's primary key is also an identity field.
The second table's primary key should get the value, assigned by SQL to the first table's identity key.
So we have:
Table1( ID1(Primary, Identity), Value1) Table2( ID2(Primary), Value2)
Input data set: (Value1, Value2)
After insert the tables values should be...
1010, 10 1010, 100
1011, 20 1011,200
1012, 30 1012,300
1010-1012 are the identity values assigned by SQL
We cannot target two tables in bcp, but we can target a view,
So I created a view (MyView), linking ID1 and ID2, and having two fields Value1 and Value2
now, after issuing... bcp pubs.dbo.MyView in my.txt -T -c -t ,
I am getting the following error
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]View or function 'pubs.dbo.MyView' is not
updatable because the modification affects multiple base tables.
After bit of research, I have created a INSTEAD OF INSERT TRIGGER to catch the insert statement and redirect it to two separate statements, updating each table separately. My two insert statements look like the following...
insert into Table1 (Value1) select i.Value1 from inserted i
insert into Table2 (ID2, Value2) select scope_identity(), i.Value2 from inserted i
The scope_identity() function eventually returns me ID1 assigned to the first table.
The problem with those statements is that the trigger is called once, for the whole table (inserted), not per each row. So it fails, as it tries to use the same (last ID1) value for all the values of the Table2.
To solve this problem (thanks to article http://support.microsoft.com/kb/111401 ) I am using cursoring (iterating).
as I cannot use DELETE approach for 'inserted' table I am using slightly modified second approach.
However, for that to work, I modified a view to be View (ID1,Value1,Value2)
My input file would become
Note, that the only requirement to the first column, is that values must be in the ascending order.
My final trigger is...
CREATE TRIGGER MyTrigger on [dbo].[MyView]
INSTEAD OF INSERT
DECLARE @row_id CHAR( 11 )
SET ROWCOUNT 1
SELECT @row_id = i.ID1 from inserted i
WHILE @row_id is not null
INSERT INTO dbo.Table1 (Value1) SELECT i.value1 FROM inserted i WHERE i.ID1 = @row_id
INSERT INTO dbo.Table2 (ID2, Value2) SELECT scope_identity(), i.Value2 FROM inserted i WHERE i.ID1 = @row_id
SELECT @row_id = min( i.ID1 ) FROM inserted i WHERE i.ID1 > @row_id
SET ROWCOUNT 0
I tested this approach on my machine and it works great either using bcp.exe utility, or BULK INSERT operator, such as
bulk insert MyView from 'c:\my.txt' with (FIELDTERMINATOR = ',', FIRE_TRIGGERS )
bcp commands are: (either works on my machine)
bcp pubs.dbo.MyView in C:\my.txt -c -T -t , -h "FIRE_TRIGGERS"
bcp pubs.dbo.MyView in C:\my.txt -c -S peterv21855 -Usa -Psa -t , -h "FIRE_TRIGGERS"
Note: we must specify -h "FIRE_TRIGGERS" option to hint a system to use the trigger.
I was so excited about my solution, so I quickly embed it in our system and fired up the test run. Unfortunately it did not go well on real tables on different database (different server).
I went back to my test files and this time I created them on different machine.
Strangely it did not work. I run the bcp command from that remote machine to update my test files on my machine- that worked perfectly. So bcp was not an issue. I run bcp -v and the versions were the same (Version: 8.00.194, MSSQL 2005)
The error I am getting is the following:
SQLState = 37000, NativeError = 8105
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]'My_DB.dbo.MyView' is not a user table. Cannot perform SET operation.
BCP copy in failed
The only thing that differ is that my test files are under pubs database.
I run bcp with sa credentials, and with other user name (with all possible permissions, including bulkadmin and sysadmin)
Nothing helps. I did not find anything in MSDN or in Google.
The only hope is this forum.