• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Dynamically add Ids by 1 in SQL insert.

Hello Experts,

I am inserting data into a single table and I would like to do this in bulk. The only thing that is stopping me is one field that does not accept nulls and is an id that I assign to each item. I am trying to create a procedure that will dynamically add this field (i++) instead of me going in one by one.

Is this possible? Do I need to use script?

thanks.
0
Tim
Asked:
Tim
  • 3
  • 2
3 Solutions
 
Walter RitzelSenior Software EngineerCommented:
if you are using SQL Server, I'm pretty sure that you can change the type of this field to autoincrement and then, do not refer this field on the insert clause.
0
 
TimSr. System AdminAuthor Commented:
Is there a way of doing this without modifying the table?
0
 
Ron MalmsteadInformation Services ManagerCommented:
Without modifying the table, you would be facing another issue.

You could get the "next value" easily...but what if two seperate processes are both looking for the "next value" at the same time ...and get the same value ?

Even if you did ...MAX(ID) + 1... the same problem remains.

The best advice is to modify the table to increment the field automatically.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
TimSr. System AdminAuthor Commented:
Ok, but what if there are two fields? Will MS SQL let me have two fields with Identity turned on?
0
 
Walter RitzelSenior Software EngineerCommented:
I dont see why not...
0
 
TimSr. System AdminAuthor Commented:
Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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