SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 inserting row data while incrementing column data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLIsTheDevil
Posting Yak Master

USA
177 Posts

Posted - 08/21/2013 :  15:10:06  Show Profile  Reply with Quote
I have two tables: One is from a development environment and the other is from a production environment. Both tables are called Model. The two tables are linked via linked servers.

Here is the Model table from dev env:


ID        Type        Name
10        rect        pool
12        cube        rubix


Here is the Model table from prod env:


ID        Type        Name
1         rect        pool
3         cube        rubix
5         triangle    triangle
6         sphere      sun
8         polygon     any


I want to write a query which copies the missing records from production to development and ensure the ID value which gets inserted is incrementing based on the highest ID value in the table . FYI, the missing records are the last three in the production table. I have no problem with inserting the records, other than the ID column.

Here is what the development table should look like after running the query:


ID        Type        Name
10        rect        pool
12        cube        rubix
13        triangle    triangle
14        sphere      sun
15        polygon     any


Here is the query I'm using at the moment. It's run from the production environment.

WITH Model_cte AS
(
SELECT m.* FROM
(
SELECT * FROM OPENQUERY(DevServer,'SELECT * FROM Model') linkedsvr
)subq
RIGHT JOIN
Model m ON subq.Type=m.Type AND subq.Name=m.Name
WHERE subq.ID IS NULL
)
INSERT OPENQUERY(DevServer, 'SELECT * FROM Model')
SELECT (SELECT TOP 1 ID+1 FROM Model ORDER BY ID DESC), m2.Type, m2.Name
FROM Model_cte m2

The query increments the ID but the value is the same for all rows inserted. I want it to increment for each row inserted, though.

Any help would be appreciated. Thank you.

Edited by - SQLIsTheDevil on 08/21/2013 15:11:46

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/21/2013 :  15:30:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
CREATE TABLE	#Dev
		(
			ID TINYINT NOT NULL,
			[Type] VARCHAR(9) NOT NULL,
			Name VARCHAR(9) NOT NULL
		);

INSERT	#Dev
	(
		ID,
		[Type],
		Name
	)
VALUES	(10, 'rect', 'pool'),
	(12, 'cube', 'rubix');

SELECT	ID,
	[Type],
	Name
FROM	#Dev;

CREATE TABLE	#Prod
		(
			ID TINYINT NOT NULL,
			[Type] VARCHAR(9) NOT NULL,
			Name VARCHAR(9) NOT NULL
		);

INSERT	#Prod
	(
		ID,
		[Type],
		Name
	)
VALUES	(1, 'rect', 'pool'),
	(3, 'cube', 'rubix'),
	(5, 'triangle', 'triangle'),
	(6, 'sphere', 'sun'),
	(8, 'polygon', 'any');

-- SwePeso
DECLARE	@MaxID TINYINT;

BEGIN TRAN

BEGIN TRY
	SELECT TOP(1)	@MaxID = ID
	FROM		#Dev
	ORDER BY	ID DESC;

	INSERT	#Dev
		(
			ID,
			[Type],
			Name
		)
	SELECT	@MaxID + ROW_NUMBER() OVER (ORDER BY p.ID) AS ID,
		p.[Type],
		p.[Name]
	FROM	#Prod AS p
	WHERE	NOT EXISTS(SELECT * FROM #Dev AS d WHERE d.Name = p.Name);

	COMMIT TRAN
END TRY
BEGIN CATCH
	ROLLBACK TRAN
END CATCH

SELECT	ID,
	[Type],
	Name
FROM	#Dev;

DROP TABLE	#Dev,
		#Prod;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SQLIsTheDevil
Posting Yak Master

USA
177 Posts

Posted - 08/23/2013 :  09:18:35  Show Profile  Reply with Quote
Thank you very much! This works very well!
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
410 Posts

Posted - 08/23/2013 :  13:40:51  Show Profile  Reply with Quote


INSERT INTO #Dev WITH (TABLOCKX) ( ID, Type, Name )
SELECT
    dev_last_used_id.ID + ROW_NUMBER() OVER (ORDER BY p.ID),
    p.Type, p.Name
FROM #Prod p
CROSS JOIN (
    SELECT MAX(ID) AS ID
    FROM #Dev
) AS dev_last_used_id
WHERE NOT EXISTS(
    SELECT 1
    FROM #Dev d
    WHERE
        d.Name = p.Name
    )


Edited by - ScottPletcher on 08/23/2013 13:41:09
Go to Top of Page

sivadss2007
Starting Member

India
18 Posts

Posted - 08/28/2013 :  05:50:43  Show Profile  Reply with Quote
INSERT INTO #Dev
(ID,TYPE,NAME)
SELECT
ID,TYPE,NAME

FROM
--(
#Prod
WHERE TYPE NOT IN (SELECT TYPE FROM #Dev)

P.Siva
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000