Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

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_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')

Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'OutboundTriggerEvent_file', database 'cabinet', owner 'dbo'.
0
tadeel
Asked:
tadeel
  • 4
  • 4
  • 2
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>INSERT permission denied on object 'OutboundTriggerEvent_file',

your syntax is correct.
your login you use does not have insert permissions.
0
 
tadeelAuthor Commented:
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' when IDENTITY_INSERT is set to OFF.
0
 
sateeshcvCommented:
HI,
IF U R SPECIFYING EXPLICIT VALUES FOR THE IDENTITY COLUMN, THEN RUN THE BELOW QUERY
SET IDENTITY_INSERT outboundtriggerevent_file ON;
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
mfhorizonCommented:
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_file(OBTrigger,PendingSignature,EventTypeToSend,NewDocumentStatus,Record_Version)
(SELECT 'delete','NULL','T11','NULL','1'
UNION
SELECT 'edit','N','T08','LA','2'
UNION
SELECT 'edit','Y','T08','AU','2'
UNION
SELECT 'insert','NULL','T01','NULL','1'
UNION
SELECT 'manual','NULL','T08','NULL','2'
UNION
SELECT 'move','NULL','T07','NULL','1'
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!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try the below steps:

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;

Open in new window

0
 
mfhorizonCommented:

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!
0
 
tadeelAuthor Commented:
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.
0
 
sateeshcvCommented:
Hi,
Send me the table structure, execute this and send me the result:
sp_columns outboundtriggerevent_file
0
 
tadeelAuthor Commented:
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
 
0
 
luaniCommented:
just use this (on indentity on of off) :

insert into dbo.outboundtriggerevent_file(OBTrigger,PendingSignature,EventTypeToSend,NewDocumentStatus,Record_Version)
(SELECT 'delete','NULL','T11','NULL','1'
UNION
SELECT 'edit','N','T08','LA','2'
UNION
SELECT 'edit','Y','T08','AU','2'
UNION
SELECT 'insert','NULL','T01','NULL','1'
UNION
SELECT 'manual','NULL','T08','NULL','2'
UNION
SELECT 'move','NULL','T07','NULL','1'
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.
0
 
sateeshcvCommented:
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_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')
0
 
sateeshcvCommented:
And Remove the Single quotes for all the integer values
For Fields: OutboundTriggerEventID and Record_Version
Execute the below query and let me know any error:

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)
0
 
tadeelAuthor Commented:
Thanks. It worked. You all are great.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now