Incremental value for field using INSERT

Question

Post reply

Incremental value for field using INSERT

PutJatDa

SSC-Addicted

Points: 448
More actions
April 19, 2005 at 2:51 am

#66944

I am populating a table from another table in the following way :-

INSERT INTO Transactions1 (
          Field1,
          Field2,
          Field3,
          Field4
          )

SELECT
          Field1,
          Field2,
          Field3,
          Field4

FROM Transactions2

The problem is field3 is an incremental value, eg for simplicity’s sake say it goes up by 1 for each record. Can someone please show me how to do it?

Thanks in anticipation.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

I think you are refering to identity filed

so you start with

SET IDENTITY_INSERT [dbo].[Transactions1 ] ON

YOUR CODE

SET IDENTITY_INSERT [dbo].[Transactions1 ] OFF

My Blog: http://dineshasanka.spaces.live.com/

PutJatDa SSC-Addicted Points: 448 More actions · Answer 2

Thanks for your reply Dinesh.

I don't want to use identity fields. I want to do it programmatically.

I was thinking more of starting of with a value (a variable) of say 1000. So for every record created in Transactions1 field3 will have value of 1001, 1002, 1003 etc (ie the variable being incremented by 1).

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 3

oh! sorry for the mistake

is field1 your primary key of the table?

My Blog: http://dineshasanka.spaces.live.com/

PutJatDa SSC-Addicted Points: 448 More actions · Answer 4

PutJatDa

SSC-Addicted

Points: 448

April 19, 2005 at 3:48 am

#553375

Yes.

PutJatDa SSC-Addicted Points: 448 More actions · Answer 5

Took your advice in the end Dinesh and went with IDENTITY.

There doesn't seem a way to do it as I wanted to. Thanks anyway.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

What will this be used for? Do you need to store such data in your tables or is this for presentation purposes? Keep in mind that you sooner or later almost always will have gaps in the IDENTITY sequence.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

DanaH1976 Ten Centuries Points: 1046 More actions · Answer 7

Assuming that you really do need to do this, what about using a cursor?

CREATE PROCEDURE FinancialAidAwardReformat

AS SET NOCOUNT ON

DECLARE @StudentID char(9)

DECLARE @Lastname varchar(50)

DECLARE @Firstname varchar(50)

DECLARE @FiscalYear char(9)

DECLARE @Field3 int

SELECT @Field3 = 0 --initialize field

DECLARE abc CURSOR FOR SELECT StudentID, Lastname, Firstname, F.FiscalYear FROM dbo.tblFinancialAidAwards

OPEN abc

FETCH NEXT FROM abc INTO @StudentID, @Lastname, @Firstname, @FiscalYear

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO tblWhatever
(StudentID, Lastname, Firstname, FiscalYear, Field3)
VALUES(@StudentID, @Lastname, @Firstname, @FiscalYear, @Field3 + 1)
FETCH NEXT FROM abc INTO @StudentID, @Lastname, @Firstname, @FiscalYear

END

CLOSE abc

DEALLOCATE abc

GO

Dana
Connecticut, USA
Dana

PutJatDa SSC-Addicted Points: 448 More actions · Answer 8

In answer to your queries as to why I need to do it :-

Basically, this is what the system is doing.

Transactions are stored in a table called Transactions. This table includes

fields called Transaction Code and Trans Reference.

Usually, the information is all copied to a second table called

Transactions_ForProcessing.

However, if a transaction code 400 is found, then an additional record has to be created, in Transactions_Forprocessing,with the same information but with a transaction code of 451. However, the Trans Reference

has to also be different (it has to be unique, no duplicates allowed).

PutJatDa SSC-Addicted Points: 448 More actions · Answer 9

Thanks for your reply DanaH1976 . We've gone with the identity function.