?
Solved

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

Posted on 2009-04-07
13
Medium Priority
?
491 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
[X]
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
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

752 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