How to UPADTE using identity from insertion

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

SQL Server Development (2000)

How to UPADTE using identity from insertion

Author

Topic

tdaplyn
Starting Member

1 Post

Posted - 2004-06-17 : 10:37:33

Here's how my problem boils down:
I have a table 'table1' with a 'value varchar(20)' field.
I want to create a new table 'table2' with some of the values from table1 in it.
Ok, so I can create table2 and do an 'INSERT..SELECT FROM table1' to achieve this.
Table 2 has an IDENTITY column. The thing is (and this seems sensible to me), I want take the @@IDENTITY created by the insertion of data from row n of table1 into table2, and set that back into another field in row n of table1.

I feel like I should be able to do this in one go somehow. If not, how would I do it using cursors?

Thanks for any help!
TD...

sorengi
Starting Member

48 Posts

Posted - 2004-06-17 : 11:47:25

CREATE TABLE table1
(
table1_pk int NOT NULL,
value1 varchar(20) NOT NULL,
table2_pk int NULL
)
go
CREATE TABLE table2
(
table2_pk int identity(1,1) NOT NULL,
table1_pk int NOT NULL,
value1 varchar(20) NOT NULL
)
go
INSERT INTO table1 (table1_pk, value1, table2_pk) VALUES (1,'value1',NULL)
go
INSERT INTO table2 (table1_pk, value1) SELECT table1_pk, value1 FROM table1
go
UPDATE table1
SET table2_pk = table2.table2_pk
FROM table1
INNER JOIN table2
ON (table1.table1_pk = table2.table1_pk)
go
SELECT * FROM table2
SELECT * FROM table1
go

Michael D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources