New ID In Batch Insert

cursor? if so how would i write it?

Check this

http://www.databasejournal.com/features/mssql/article.php/3307541

Its not an identity Field its a int 4

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


 

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

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

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

Do i have to make a stored prcodure to use the tmp table?

That's the easiest way.

Can i have the code for that?

Basically i need the whole thing so i can just copy and paste Thanks

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

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

anyone?

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

If lowfatspreads Solution works he gets the points.

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.

I cant test it. Im way past that point. Sorry

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.

Venabili  you da boss

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