Output fields that are not inserted rephrased

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)

Output fields that are not inserted rephrased

Author

Topic

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-12-23 : 09:17:08

Hello,
I realise my last question on this subject wasn't very clear so here is some sample code that explains what I want to do:



--This is an example of the old database data with strange ids.
DECLARE @OldDataBaseTable TABLE(
	Id char(5),
	OtherData varchar(50),
	EntryDate datetime
)

INSERT INTO @OldDataBaseTable
SELECT 'A1000','Some data',getdate()
UNION ALL
SELECT 'A1001','Some different data',getdate()
UNION ALL
SELECT 'B1000','Some of the same data',getdate()
UNION ALL
SELECT 'A2000','Some of the same data',getdate()


--this is the new database table with identity generated ids (has other datasources besides old database)
DECLARE @NewDatebaseTable TABLE(
	Id int identity(1,1),
	OtherData varchar(50),
	EntryDate datetime
)

--Stores old to new ids
DECLARE @OldtoNewIds TABLE(
	New_Id int,
	OldId char(5)
)


--inserts data into new database from old
INSERT INTO @NewDatebaseTable(otherdata,entryDate)
	OUTPUT
		INSERTED.Id,
		Null	--This should be O.Id, but it wont work
	INTO @OldtoNewIds
SELECT O.OtherData,O.EntryDate
FROM @OldDataBaseTable O
	

--This table should be the new ids next to the old ones
SELECT * FROM @OldtoNewIds

Does anyone know how to do this? Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-23 : 09:37:48

I think the solution is to add an old_id_field to the dest table and feed it in insert statement.
Then there you got the required value in INSERTED.old_id_field to take in output statement.

Later you can drop that old_id_field.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-12-23 : 09:45:12

This is going to be a regular import. So do you mean I'll need create a field for old ids everytime then drop it again after I have the data out? I'm sure there's a better way...

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-12-23 : 09:49:25

Maybe I'll have to resort to cursors... which also makes me think there must be a better way!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-23 : 10:05:53

Is it imaginable for you not to drop the old_id_field?
Then you can go without table OldtoNewIds.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-12-23 : 10:12:10

Wont this cause all kind of locking problems? Luckily on this project that is not an issue as the table is not accessed that much, but I wonder how someone on a regularly accessed table would get around this... Thanks very much for the help though! I also posted this here and got the same answer:

http://www.dbforums.com/showthread.php?p=6373141#post6373141

michael.appleton
Posting Yak Master

160 Posts

Posted - 2009-01-19 : 06:23:24

Just to let anyone know who stumbles apon this old post, I ended up using a cursor to achieve this in the end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources