Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Skipping identity column in SQL with insert statement

Hi
I am using the code below to insert into an SQL table, the first column is an identity field set to populate with an ID but the statement keeps failing(I have typed IDENTITY FIELD where the field is), it needs to be in the syntax below how can I make it skip this field, (I cant write insert into colum1,column2 values etc... as an application produces this code.
insert into dataCharges values(IDENTITY FIELD,'PRINT','2011-05-12 17:16:50',1,'AI80025','','PRIN''d/m/yyyy',0,'2011-05-12 14:37:09.000','2011-05-12 14:37:09.000')

Open in new window

0
sykotex
Asked:
sykotex
1 Solution
 
coldchillinCommented:
First, explicitly name the columns in the INSERT

INSERT INTO DATACHANGES(action,date,bit...)
VALUES('PRINT','2011-05',1)

Also,

'PRIN''d/m/yyyy'

Do you expect this to insert

PRIN'd/m/yyyy

OR

Did you want

PRIN'd/mm/yyy'

which would be

'PRIN''d/m/yyyy'''
0
 
SharathData EngineerCommented:
You need to explicitly mention all the names except the identity column in the INSERT clause. Otherwise, use dynamic sql.
0
 
chandrasekar1Commented:
ya, i agree with above.. check the Identity column is set to running no.. if yes, write the query as below

 
insert into dataCharges values('PRINT','2011-05-12 17:16:50',1,'AI80025','','PRIN','d/m/yyyy',0,'2011-05-12 14:37:09.000','2011-05-12 14:37:09.000')

Open in new window


some thing, i m not getting on this value

'PRIN''d/m/yyyy'

whether, these are two different values ('PRIN','d/m/yyyy') or same value ?
If same then place a comma like above...

Note : 'd/m/yyyy' this value can be only inserted to other than Datetime datatype column
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sykotexAuthor Commented:
I cannot change the statement just the values. So cannot explicitly mention the columns
0
 
coldchillinCommented:
The point is to make 100% sure you have specific the number of needed values to columns.

We pointed out:

'PRIN''d/m/yyyy'

Something looks wrong here, and we've posted potential solutions. Secondly, have a look at the DB again and make sure that "Identity" column is properly setup as an Identity:

In SQL Server > Design Table > click column > column properites > Identity Specification = Yes, (Is Identity) = Yes
0
 
ksparkyCommented:
Try using dynamic SQL as follows:

DECLARE strSQL AS Varchar(500)

Set @strSQL = "insert into dataCharges(action, date, bit, <add other fields>)  values('PRINT','2011-05-12 17:16:50',1,'AI80025','','PRIN','d/m/yyyy',0,'2011-05-12 14:37:09.000','2011-05-12 14:37:09.000')"

EXEC strSQL
0
 
prajapati84Commented:
As per my understanding to your question...

You  are generating identity column field values (i.e. unique ID) and inserting from application.
So, you cannot give IDENTITY "true" to Database table column in which you are going to insert identity value.

Either you can insert via application or via database.

Hope this helps!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now