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 2008 Forums
 Transact-SQL (2008)
 inserting row data while incrementing column data

Author  Topic 

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2013-08-21 : 15:10:06
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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-21 : 15:30:05
[code]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;[/code]


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

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2013-08-23 : 09:18:35
Thank you very much! This works very well!
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-23 : 13:40:51
[code]

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
)

[/code]
Go to Top of Page

sivadss2007
Starting Member

18 Posts

Posted - 2013-08-28 : 05:50:43
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
   

- Advertisement -