Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to insert multiple rows in a table in SQL Server 2000

Posted on 2009-04-07
13
480 Views
Last Modified: 2012-05-06
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
Comment
Question by:tadeel
  • 4
  • 4
  • 2
  • +3
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24094091
>INSERT permission denied on object 'OutboundTriggerEvent_file',

your syntax is correct.
your login you use does not have insert permissions.
0
 

Author Comment

by:tadeel
ID: 24094127
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
 
LVL 3

Expert Comment

by:sateeshcv
ID: 24094193
HI,
IF U R SPECIFYING EXPLICIT VALUES FOR THE IDENTITY COLUMN, THEN RUN THE BELOW QUERY
SET IDENTITY_INSERT outboundtriggerevent_file ON;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:mfhorizon
ID: 24094208
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24094210
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
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24094211

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
 

Author Comment

by:tadeel
ID: 24094274
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
 
LVL 3

Expert Comment

by:sateeshcv
ID: 24094305
Hi,
Send me the table structure, execute this and send me the result:
sp_columns outboundtriggerevent_file
0
 

Author Comment

by:tadeel
ID: 24094357
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
 
LVL 7

Expert Comment

by:luani
ID: 24094367
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
 
LVL 3

Expert Comment

by:sateeshcv
ID: 24094384
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
 
LVL 3

Accepted Solution

by:
sateeshcv earned 500 total points
ID: 24094396
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
 

Author Comment

by:tadeel
ID: 24094574
Thanks. It worked. You all are great.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question