retrieve identity values with insert into (x,y) select... ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

retrieve identity values with insert into (x,y) select... ?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-16 : 09:27:04

junior writes "I have to copy/synch project data from an old paradox database to an new SQL-server environment.
To speed up the copying of records i would like to use the insert into..... select... notation.

There is one problem though, i have to store the paradox projectid (old) and the sql-server projectid (new) in a new table so we can map the old projectid's to the new ones in case of mutations.

How do i keep track of the records added with an insert into [db2].dbo.table1 (x,y) select (x,y) from [db1].dbo.table1
so i can do an insert into another table where i store old projectid and new project_id.

Should i use this notation or for example use a cursor which inserts/updates all the data row by row so i can use a select @@identity?

What is the best option here?"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-16 : 12:22:00

Just get the MIN and MAX from the select.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-16 : 14:42:02

I don't know how you plan to get the "new" sql server id (you mention @@IDENTITY), but how about this...why not keep them in the same table?


USE Northwind
GO

SELECT * INTO New_Orders FROM Orders

ALTER TABLE New_Orders ADD Old_ID int

UPDATE New_Orders SET Old_Id = OrderID
GO

ALTER TABLE New_Orders DROP COLUMN OrderId 

ALTER TABLE New_Orders ADD New_ID int IDENTITY(1,1) NOT NULL
GO

SELECT Old_ID, New_ID FROM New_Orders

DROP TABLE New_Orders
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources