how to copy column values(where set Identity sp to yes) from one table to another [Resolved]

Posted by Oswaldlily under Sql Server on 12/27/2012 | Points: 10 | Views : 876 | Status : [Member] | Replies : 6
I have sno(column1) in Table1 and
UniqNo(column1) in Table2

I have set Identity specification to yes
for sno(column1) in Table1

Now these values shd get copy to UniqNo(column1) in Table2

Again i dont want to set Identity specification to yes for second table2

UniqNo- shd hold sno values
any query or any method?




Responses

Posted by: Pavanandey on: 12/27/2012 [Member] Bronze | Points: 50

Up
0
Down

Resolved
insert into value(Value,Name,dept,branch,code) (select Sno,Name,@dept,@branch,@code from sno)

Since @dept,@branch,@code are parameter you can directly keep them in select statement

Thanks
Pavan Kumar
Mark Answer if this fits the need

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 12/27/2012 [Member] Bronze | Points: 25

Up
0
Down
select * from sno
sp_help sno
Sno int --Identity coloumn
Name varchar
select * from value
sp_help value
Value int --Identity coloumn
Name varchar

insert into sno values('Pavan')
insert into sno values('tanvik')
insert into sno values('Kiran')

select * from sno
Sno Name
******************
1 Pavan
2 tanvik
3 Kiran


insert into value values('Krishna')
select * from value
Value Name
******************
1 Krishna

SET IDENTITY_INSERT value ON --Turns OFF identity
insert into value(Value,Name) (select Sno,Name from sno)
SET IDENTITY_INSERT Value OFF --Turns ON identity

select * from value
Value Name
******************
1 Krishna
1 Pavan
2 tanvik
3 Kiran

Thanks
Pavan Kumar
Mark Answer if this fits the need

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 12/27/2012 [Member] Bronze | Points: 25

Up
0
Down
This helps you.

Mark asnwer if this helps you

Thanks
Pavan Kumar
Mark Answer if this fits the need

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 12/27/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

Excecute the following query......

It helps you for the same.....


Declare @temp table(

sno int identity,
name varchar(10)
)

Insert into @temp(name)
select 'A'
union
select 'B'
union
select 'C'

Select * from @temp

Declare @temp1 table(
id int
)

insert into @temp1(id)
select sno from @temp


select * from @temp1


Sourabh07

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 12/27/2012 [Member] Starter | Points: 25

Up
0
Down
SET IDENTITY_INSERT value ON --Turns OFF identity
insert into value(Value,Name) (select Sno,Name from sno)
SET IDENTITY_INSERT Value OFF --Turns ON identity

hi this is good for sno.but i have 10 columns
Value Name dept branch code .....
a)I can fill the Value and Name using your insert query..but how to insert for rest of the columns?
b)Am i to write to insert queries like this for filling rest of the columns??
SET IDENTITY_INSERT value ON --Turns OFF identity
insert into value(Value,Name) (select Sno,Name from sno)
insert into value(dept,branch,code) values(@dept,@branch,@code)

SET IDENTITY_INSERT Value OFF --Turns ON identity

note(@dept,@branch,@code are passing as arguments)




Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 12/27/2012 [Member] Bronze | Points: 25

Up
0
Down
They need not be a table coloum

Thanks
Pavan Kumar
Mark Answer if this fits the need

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response