ID from 1 table to be used in other table.

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 2008 Forums

Transact-SQL (2008)

ID from 1 table to be used in other table.

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2013-04-15 : 17:06:51

Hi,

1 help needed..

I have a table say A which has an identity column.
Structure :
A
ID INT identity(1,1)

This identity value is used in table B as PK.
ID INT PK

Now I want to bulk load into A and have that bulk loaded ID's from A into B.

Has anyone worked on any SQL that can take care of this?

Thanks in advance
Sam

xpandre
Posting Yak Master

212 Posts

Posted - 2013-04-15 : 17:42:56

[code]
set rowcount 5000
declare @startpoint int , @endpoint int
select @startpoint = MAX(ColA)+1 from A
set @endpoint = @startpoint+ 6000

set identity_insert A on
insert into A(ColA,created_by,created_dtm) values (@endpoint,SUSER_SNAME(), GETDATE())
set identity_insert A off
/*
B has 300k records with NULL ColB
*/
update B
set ColB = @startpoint+1
where ColB is null[/code]

I need values between @startpoint and @endpoint to be updated into B.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-16 : 02:07:51

use composable DML method
see illustration below


declare @a table
(
id int identity(1,1),
val varchar(100)
)

declare @b table
(
id int primary key
)
insert @b
select id
from
(
insert @a (val)
output inserted.id
select (89+number)
from master..spt_values
where number >0
and type='p'
)t

select * from @a
select * from @b

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources