How to insert multiple rows in a table in SQL Server 2000
In SQL Server 2000 I'm an getting an error while trying to insert multiple rows into a table called outboundtriggerevent_file in a database called Cabinet. The query and error are below, what am I doing wrong?
insert into dbo.outboundtriggerevent_f
(SELECT '7','delete','NULL','T11',
UNION
SELECT '4','edit','N','T08','LA',
UNION
SELECT '3','edit','Y','T08','AU',
UNION
SELECT '6','insert','NULL','T01',
UNION
SELECT '5','manual','NULL','T08',
UNION
SELECT '8','move','NULL','T07','N
UNION
SELECT '2','sign','N','T04','LA',
UNION
SELECT '1','sign','Y','T04','AU',
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'OutboundTriggerEvent_file
insert into dbo.outboundtriggerevent_f
(SELECT '7','delete','NULL','T11',
UNION
SELECT '4','edit','N','T08','LA',
UNION
SELECT '3','edit','Y','T08','AU',
UNION
SELECT '6','insert','NULL','T01',
UNION
SELECT '5','manual','NULL','T08',
UNION
SELECT '8','move','NULL','T07','N
UNION
SELECT '2','sign','N','T04','LA',
UNION
SELECT '1','sign','Y','T04','AU',
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'OutboundTriggerEvent_file
ASKER
Thanks. I logged in as sa user and ran the query again, this time I got a different message, which is below.
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'OutboundTriggerEvent_file
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'OutboundTriggerEvent_file
HI,
IF U R SPECIFYING EXPLICIT VALUES FOR THE IDENTITY COLUMN, THEN RUN THE BELOW QUERY
SET IDENTITY_INSERT outboundtriggerevent_file ON;
IF U R SPECIFYING EXPLICIT VALUES FOR THE IDENTITY COLUMN, THEN RUN THE BELOW QUERY
SET IDENTITY_INSERT outboundtriggerevent_file ON;
If you want column OutboundTriggerEventID as auto id and SQL SERVER should generate auto value You should use your query as below:
insert into dbo.outboundtriggerevent_f
(SELECT 'delete','NULL','T11','NUL
UNION
SELECT 'edit','N','T08','LA','2'
UNION
SELECT 'edit','Y','T08','AU','2'
UNION
SELECT 'insert','NULL','T01','NUL
UNION
SELECT 'manual','NULL','T08','NUL
UNION
SELECT 'move','NULL','T07','NULL'
UNION
SELECT 'sign','N','T04','LA','2'
UNION
SELECT 'sign','Y','T04','AU','2')
In contrast If you want to put manual OutboundTriggerEventID then go to Enterprise Manager > Modify Table structure and Set Identity to 'No' or '0' > Save the table and try again.
Cheers!
insert into dbo.outboundtriggerevent_f
(SELECT 'delete','NULL','T11','NUL
UNION
SELECT 'edit','N','T08','LA','2'
UNION
SELECT 'edit','Y','T08','AU','2'
UNION
SELECT 'insert','NULL','T01','NUL
UNION
SELECT 'manual','NULL','T08','NUL
UNION
SELECT 'move','NULL','T07','NULL'
UNION
SELECT 'sign','N','T04','LA','2'
UNION
SELECT 'sign','Y','T04','AU','2')
In contrast If you want to put manual OutboundTriggerEventID then go to Enterprise Manager > Modify Table structure and Set Identity to 'No' or '0' > Save the table and try again.
Cheers!
Try the below steps:
Make sure that you Switch OFF Identity_insert after setting it to ON
Make sure that you Switch OFF Identity_insert after setting it to ON
SET IDENTITY_INSERT outboundtriggerevent_file ON;
insert into dbo.outboundtriggerevent_file(OutboundTriggerEventID,OBTrigger,PendingSignature,EventTypeToSend,NewDocumentStatus,Record_Version)
(SELECT '7','delete','NULL','T11','NULL','1'
UNION
SELECT '4','edit','N','T08','LA','2'
UNION
SELECT '3','edit','Y','T08','AU','2'
UNION
SELECT '6','insert','NULL','T01','NULL','1'
UNION
SELECT '5','manual','NULL','T08','NULL','2'
UNION
SELECT '8','move','NULL','T07','NULL','1'
UNION
SELECT '2','sign','N','T04','LA','2'
UNION
SELECT '1','sign','Y','T04','AU','2')
SET IDENTITY_INSERT outboundtriggerevent_file OFF;
Continue to my above post... if you want OutboundTriggerEventID to be manually inserted you should run you actual query after modifying table structure...
Good luck!
ASKER
Okay, after turning the SET IDENTITY_INSERT outboundtriggerevent_file ON; I'm getting this erro now. Just a reminder this is SQL Server 2000
Server: Msg 8152, Level 16, State 9, Line 3
String or binary data would be truncated.
The statement has been terminated.
Also I coudn'y fomd the "Modify table structure" option in Enter prise Manager.
Thanks.
Server: Msg 8152, Level 16, State 9, Line 3
String or binary data would be truncated.
The statement has been terminated.
Also I coudn'y fomd the "Modify table structure" option in Enter prise Manager.
Thanks.
Hi,
Send me the table structure, execute this and send me the result:
sp_columns outboundtriggerevent_file
Send me the table structure, execute this and send me the result:
sp_columns outboundtriggerevent_file
ASKER
Here it is. Thanks.
cabinet dbo OutboundTriggerEvent_file OutboundTriggerEventID 4 int identity 10 4 0 10 0 NULL NULL 4 NULL NULL 1 NO 56
cabinet dbo OutboundTriggerEvent_file OBTrigger 12 varchar 10 10 NULL NULL 0 NULL NULL 12 NULL 10 2 NO 39
cabinet dbo OutboundTriggerEvent_file PendingSignature 1 char 1 1 NULL NULL 1 NULL NULL 1 NULL 1 3 YES 39
cabinet dbo OutboundTriggerEvent_file EventTypeToSend 1 char 3 3 NULL NULL 1 NULL NULL 1 NULL 3 4 YES 39
cabinet dbo OutboundTriggerEvent_file NewDocumentStatus 1 char 2 2 NULL NULL 1 NULL NULL 1 NULL 2 5 YES 39
cabinet dbo OutboundTriggerEvent_file Record_Version 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 6 NO 56
cabinet dbo OutboundTriggerEvent_file OutboundTriggerEventID 4 int identity 10 4 0 10 0 NULL NULL 4 NULL NULL 1 NO 56
cabinet dbo OutboundTriggerEvent_file OBTrigger 12 varchar 10 10 NULL NULL 0 NULL NULL 12 NULL 10 2 NO 39
cabinet dbo OutboundTriggerEvent_file PendingSignature 1 char 1 1 NULL NULL 1 NULL NULL 1 NULL 1 3 YES 39
cabinet dbo OutboundTriggerEvent_file EventTypeToSend 1 char 3 3 NULL NULL 1 NULL NULL 1 NULL 3 4 YES 39
cabinet dbo OutboundTriggerEvent_file NewDocumentStatus 1 char 2 2 NULL NULL 1 NULL NULL 1 NULL 2 5 YES 39
cabinet dbo OutboundTriggerEvent_file Record_Version 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 6 NO 56
just use this (on indentity on of off) :
insert into dbo.outboundtriggerevent_f
(SELECT 'delete','NULL','T11','NUL
UNION
SELECT 'edit','N','T08','LA','2'
UNION
SELECT 'edit','Y','T08','AU','2'
UNION
SELECT 'insert','NULL','T01','NUL
UNION
SELECT 'manual','NULL','T08','NUL
UNION
SELECT 'move','NULL','T07','NULL'
UNION
SELECT 'sign','N','T04','LA','2'
UNION
SELECT 'sign','Y','T04','AU','2')
also check the length of the fields one of the suplied values exceeds the length of its columns.
insert into dbo.outboundtriggerevent_f
(SELECT 'delete','NULL','T11','NUL
UNION
SELECT 'edit','N','T08','LA','2'
UNION
SELECT 'edit','Y','T08','AU','2'
UNION
SELECT 'insert','NULL','T01','NUL
UNION
SELECT 'manual','NULL','T08','NUL
UNION
SELECT 'move','NULL','T07','NULL'
UNION
SELECT 'sign','N','T04','LA','2'
UNION
SELECT 'sign','Y','T04','AU','2')
also check the length of the fields one of the suplied values exceeds the length of its columns.
I have understood ur problem,
Execute the same query by just removie single quotes for the values 'null'
instead of 'null' type null
Thats it.
insert into dbo.outboundtriggerevent_f
(SELECT '7','delete',null,'T11',nu
UNION
SELECT '4','edit','N','T08','LA',
UNION
SELECT '3','edit','Y','T08','AU',
UNION
SELECT '6','insert',null,'T01',nu
UNION
SELECT '5','manual',null,'T08',nu
UNION
SELECT '8','move',null,'T07',null
UNION
SELECT '2','sign','N','T04','LA',
UNION
SELECT '1','sign','Y','T04','AU',
Execute the same query by just removie single quotes for the values 'null'
instead of 'null' type null
Thats it.
insert into dbo.outboundtriggerevent_f
(SELECT '7','delete',null,'T11',nu
UNION
SELECT '4','edit','N','T08','LA',
UNION
SELECT '3','edit','Y','T08','AU',
UNION
SELECT '6','insert',null,'T01',nu
UNION
SELECT '5','manual',null,'T08',nu
UNION
SELECT '8','move',null,'T07',null
UNION
SELECT '2','sign','N','T04','LA',
UNION
SELECT '1','sign','Y','T04','AU',
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. It worked. You all are great.
your syntax is correct.
your login you use does not have insert permissions.