auto incrementing record number while inserting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

auto incrementing record number while inserting

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-02-05 : 22:04:44

Hi,

I have two tables: say Table A and Table B.

Table A has column Source and some other columns.

Table B has column ID, A, B in it.

I want to insert all the data from Source column of Table A into Column A and B in Table B having number ID generating automatically while inserting in that table.

So say, data that I need to insert in Table B comes from

select distinct Source from Table_A

The data which we get from above query should go into both column A and B in Table B. But at that time column ID is an integer column. So, I need to autogenerate incremental number for each record inserted in there.

Hope I made myself clear..

Thanks,
Ujjaval

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 22:09:52

You mean you want to insert auto generated number into Table B from Table A ? If table A has 10 distinct source, you want to insert number 1 to 10 into Table B ?

So Column A & B of Table B will have the same value ? What kind of auto numbering do you want ?

Please provide some sample data & expected result

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-02-05 : 22:17:35

I want to insert data from Table A into Table B.
But data will only come from 1 column of Table A.
This data will go into two different columns of Table B. And Table B also has this ID column where I need incremented number inserted.

So, say column in Table A has 5 records. I want those 5 records inserted into Table B in 2 different columns. And their respective ID column should have number from 1 to 5.

Thanks,
Ujjaval

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 22:26:06

value inserting into Column A, B in tableB is it always starting from 1 ?

Can you use identity for table B ?

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-02-05 : 22:32:30

no in table B the value should start from the last number it has in it.

what do you mean by identity in table b?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 22:34:05

in that case, you can create table b's col A & B with identity


create table tableB
(
    A int identity(1,1),
    B int identity(1,1)
)

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-02-05 : 22:53:28

Oh no.. not that way...

say TableB has ID, ColA, ColB columns.

TableA has ColC.

Now, I want to insert data from ColC of TableA into ColA and ColB of TableB.
At that time TableB has PK of ID which is integer number. So, when I insert a new record in TableB it will be of following format:

(ID, ColC_data_in_ColA, ColC_data_in_ColB)

So, I want ID to be incremented. Other two columns will have same data in both.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 23:08:58

so create ID as auto increment


create table TableB
(
    ID   int identity(1,1),
    colA int,
    colB int
)

insert into TableB(colA, colB)
select colC, colC
from   TableA

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-02-06 : 01:11:56

I already have data in TableB.. is it possible to modify the column as autoincrement?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 01:17:08

yes. use SSMS to do it

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-02-06 : 01:42:19

what is SSMS?

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-02-06 : 01:49:24

I guess you referred to sql server management studio. But I am not sure how to do that in that. Could you please tell me how to do that?

Thanks,
Ujjaval

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 02:29:37

Just go to SQL Server Management Studio , right click on the table and modify

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources