Is there a better way to feedback? - 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 2005 Forums

Transact-SQL (2005)

Is there a better way to feedback?

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-22 : 04:54:44

The goal is to insert some records from original table into a new table.
I want the new primary key values for the inserted records to update the old table.

The sample data below very much illustrate the problem and the current approach I use.

* There can be (but not always) duplicate values for col1 column in @Target table
* In production environment there are a few million records in @Target table
* The number of records to insert into new table varies between 10 and 100,000

I can also solve this issue by using the OUTPUT operator but that involves using a third intermediate table, which I don't want to do. I can also solve this issue by having a pkTarget column in @Source table. I don't want that either.

This cursor approach do the work in a timely fashion.
I just want an set-based solution without using an intermediate table, if possible.

-- Prevent unwanted resultsets back to client
SET NOCOUNT ON

-- Prepare source table
DECLARE	@Source TABLE
	(
		pkSource INT IDENTITY(10, 3) PRIMARY KEY,
		col1 INT
	)

-- Prepare target table
DECLARE	@Target TABLE
	(
		pkTarget INT IDENTITY(1, 1) PRIMARY KEY,
		col1 INT,
		pkSource INT
	)

-- Populate target table with 6 random values
INSERT		@Target
		(
			col1
		)
SELECT		v.Number
FROM		master..spt_values AS v
INNER JOIN	master..spt_values AS x ON x.type ='P'
			AND x.number < 2
WHERE		v.type = 'P'
		AND v.number < 3
ORDER BY	NEWID()

-- Display initial state
SELECT	*
FROM	@Target

-- Prepare UPSERT
DECLARE	curYak CURSOR LOCAL FORWARD_ONLY FOR
		SELECT TOP 4	col1
		FROM		@Target
		ORDER BY	NEWID()

DECLARE	@col1 INT

-- Open cursor
OPEN curYak

-- Get first record to work with
FETCH	NEXT
FROM	curYak
INTO	@col1

-- Loop all records to work with
WHILE @@FETCH_STATUS = 0
	BEGIN
		--  Insert target record into @Source
		INSERT	@Source
			(
				col1
			)
		VALUES	(
				@col1
			)

		-- Feedback the new ID into @Target
		UPDATE	@Target
		SET	pkSource = SCOPE_IDENTITY()
		WHERE	CURRENT OF curYak

		-- Get next record to work with
		FETCH	NEXT
		FROM	curYak
		INTO	@col1
	END

-- Clean up
CLOSE curYak
DEALLOCATE curYak

-- Show the final results
SELECT	*
FROM	@Source

SELECT	*
FROM	@Target

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-22 : 05:09:37

This is a solution I don't want

-- Prevent unwanted resultsets back to client
SET NOCOUNT ON

-- Prepare source table
DECLARE	@Source TABLE
	(
		pkSource INT IDENTITY(10, 3) PRIMARY KEY,
		col1 INT,
		pkTarget INT
	)

-- Prepare target table
DECLARE	@Target TABLE
	(
		pkTarget INT IDENTITY(1, 1) PRIMARY KEY,
		col1 INT,
		pkSource INT
	)

-- Populate target table with 6 random values
INSERT		@Target
		(
			col1
		)
SELECT		v.Number
FROM		master..spt_values AS v
INNER JOIN	master..spt_values AS x ON x.type ='P'
			AND x.number < 2
WHERE		v.type = 'P'
		AND v.number < 3
ORDER BY	NEWID()

-- Display initial state
SELECT	*
FROM	@Target

INSERT		@Source
		(
			col1,
			pkTarget
		)
SELECT TOP 4	col1,
		pkTarget
FROM		@Target
ORDER BY	NEWID()

UPDATE		t
SET		t.pkSource = s.pkSource
FROM		@Target AS t
INNER JOIN	@Source AS s ON s.pkTarget = t.pkTarget

-- Show the results
SELECT	*
FROM	@Source

SELECT	*
FROM	@Target

E 12°55'05.63"
N 56°04'39.26"

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-22 : 05:44:11

I think you've swapped Target and Source

. Here is an approach -

-- Prepare source table
DECLARE @Source TABLE
(
pkSource INT IDENTITY(10, 3) PRIMARY KEY,
col1 INT
)

-- Prepare target table
DECLARE @Target TABLE
(
pkTarget INT IDENTITY(1, 1) PRIMARY KEY,
col1 INT,
pkSource INT
)

-- Populate target table with 6 random values
INSERT @Target
(
col1
)
OUTPUT inserted.col1 into @Source
SELECT v.Number
FROM master..spt_values AS v
INNER JOIN master..spt_values AS x ON x.type ='P'
AND x.number < 2
WHERE v.type = 'P'
AND v.number < 3
ORDER BY NEWID()

UPDATE @Target
SET t.pkSource = (10 + ((t.pkTarget-1)*3)) --s.pkSource

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources