Solved

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

Posted on 2009-04-07
13
470 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
Comment Utility
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
Comment Utility
HI,
IF U R SPECIFYING EXPLICIT VALUES FOR THE IDENTITY COLUMN, THEN RUN THE BELOW QUERY
SET IDENTITY_INSERT outboundtriggerevent_file ON;
0
 
LVL 5

Expert Comment

by:mfhorizon
Comment Utility
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
Comment Utility
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
Comment Utility

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:tadeel
Comment Utility
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
Comment Utility
Hi,
Send me the table structure, execute this and send me the result:
sp_columns outboundtriggerevent_file
0
 

Author Comment

by:tadeel
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks. It worked. You all are great.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now