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: 1581
  • Last Modified:

New ID In Batch Insert

INSERT INTO Codes ([ID],1,2,3,4 )(
SELECT  (Select MAX(ID) + 1 FROM Codes),1,2,3 FROM Whatever
)

Basically i have a select into and i have to increment the ID in each insert but it just gives me the same ID so how do i increment the ID in this situation.
0
Mikal613
Asked:
Mikal613
  • 11
  • 5
  • 3
  • +3
3 Solutions
 
Mikal613Author Commented:
cursor? if so how would i write it?
0
 
Mikal613Author Commented:
Its not an identity Field its a int 4
0
Technology Partners: 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!

 
LowfatspreadCommented:
tell us the structure of WHATEVER

you basicall need to order whatever into a sequence so that you can use the sequence to obtain the offset to add to
the Max(id) for each row...

No you don't absolutely need a cursor to do this...

but it will depend on how complex WHATEVER is as to what the best solution will be...


 
0
 
Mikal613Author Commented:
INSERT INTO Codes ([ID],1,2,3,4 )(
SELECT  (Select MAX(ID) + 1 FROM Codes),1,2,3 FROM MyTable
)

Its a Simple Insert Into the only thing i need is the ID in the codes Table has to be incremented

Select MAX(ID) + 1 was my attempt but it returns the same number
0
 
SkipFireCommented:
If you are doing this in a stored procedure then you could create a temp table that has identity on the field, insert into that table without specifying the ID column so that it uses the auto increment, then do the following SQL to populate your end table.

Create your table, easiest way is to actually build it in enterprise manager, then script the table, paste the script into your stored procedure just modifying the table name to start with a # which will indicate it as a temp table rather than a perm table.

CREATE TABLE #temp (ID int, C1 varchar(10), C2 varchar(10), C3 varchar(10))
--Use enterprise manager's scripting capability to get the exact syntax for the identity code

INSERT INTO #temp SELECT C1, C2, C3 FROM BatchSource --It now has an auto-incremented ID with a base of 1
DECLARE @BaseID int
SELECT @BaseID = Max(ID) FROM Whatever --Gets the correct ID base
INSERT INTO Whatever (ID, C1, C2, C3) SELECT ID + @BaseID, C1, C2, C3 FROM #temp
--Inserts into the final table while adding the correct base ID to the auto-incremented ID of the temp table starting with Base + 1

DROP #temp --Cleanup
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with Sirees that you should use a IDENTITY field (which is based on the INT):

sample:

create table whatever ( BaseID int identity(1,1), c1 int, c2 varchar(29), c3 datetime )

-- insert a first row
insert into whatever ( c1, c2, c3 ) values ( 2, 'test row', getdate() )
-- see what value has been generated for baseID:
select * from whatever

-- copy the row:
insert into whatever ( c1, c2, c3 ) select 2*c1, 'Copy of ' + c2, getdate() from whatever
-- see what the table contains now:
select * from whatever

-- copy the 2 rows:
insert into whatever ( c1, c2, c3 ) select 4*c1, 'Copy of ' + c2, getdate() from whatever
-- see what the table contains now:
select * from whatever

you will see that the batchid value will be assigned automatically, even if you insert multiple rows at once.
MUCH easier
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
PS: if you don't like that, don't come afterwards crying that the cursor solutions are slow. you were warned.
0
 
Mikal613Author Commented:
I trust you angel!!

I cant change the clients database with the new id.

Anyways i made a project in VB to do it instead.

I will award points if i can see the cursor "code" with my scenario
0
 
LowfatspreadCommented:
mikal613
nobody is saying you need to chage the clients database...
just that you need to use a temprary table to obtain a sequence number...

eg
select a.* , identity(int,1,1) as rowno
  into #temp
 from whatever
INSERT INTO Codes ([ID],1,2,3,4 )
SELECT  maxid + rowno,1,2,3
  FROM #temp as x
 ,(select max(id) as maxid from codes) as y


or if YOU  WOULD TELL US THE STRUCTURE of THE WHATEVER TABLE then we maybe could suggest a solution
without the need for a temporary table being involved , again avoiding a CURSOR...





0
 
Mikal613Author Commented:
Do i have to make a stored prcodure to use the tmp table?
0
 
SkipFireCommented:
That's the easiest way.
0
 
Mikal613Author Commented:
Can i have the code for that?

Basically i need the whole thing so i can just copy and paste Thanks
0
 
SkipFireCommented:
You haven't given us the table structure you are dealing with, so no we can't really do that.  The basic syntax for a stored procedure is:

CREATE PROCEDURE dbo.MyProcedureName
AS
--SQL Logic
GO
0
 
Mikal613Author Commented:
ok

The table im selecting from is from a joined query so i cant give you all the fields

The table im inserting into is

int 4 (NOT AN IDENTITY) ID
smallint 2  Red
smallint 2  Green
smallint 2  Blue
DateTime  TheDAte
varchar(20) My Name
0
 
Mikal613Author Commented:
anyone?
0
 
LowfatspreadCommented:
like this ?

CREATE PROCEDURE dbo.MyProcedureName
AS

select Red,Green,Blue,TheDate,[My Name] , identity(int,1,1) as rowno
  into #temp
 from whatever


INSERT INTO Codes ([ID],red,green,blue,TheDate, [My Name] )
SELECT  maxid + rowno,red,green,blue,Thedate,[My Name]
  FROM #temp as x
 ,(select max(id) as maxid from codes) as y

return
GO
0
 
Mikal613Author Commented:
If lowfatspreads Solution works he gets the points.
0
 
SkipFireCommented:
Mikal613 - you have to tell us if it works or not, you asked the question.  If it works then tell us and award him the points.
0
 
Mikal613Author Commented:
I cant test it. Im way past that point. Sorry
0
 
SkipFireCommented:
Venabili, in the absence of a definitive solution I think the points should be split among everyone that tried to provide a solution as we all were recommending variations on the same solution.
0
 
Mikal613Author Commented:
Venabili  you da boss
0
 
VenabiliCommented:
Mikal613,

This is YOUR question and it is clear that you are here. So can you show some respect to the experts trying to help you and make a decision on your own question? So I can use the time to clean a really abandoned question and not one in which the Asker simply prefer not to do what he is supposed to although he knows how the system works :)  

By the way Lowfatspread's solution would do the trick... I am wondering on a split though... You got great advices at the beginning...

Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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