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.
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 Name10 rect pool12 cube rubix Here is the Model table from prod env:ID Type Name1 rect pool3 cube rubix5 triangle triangle6 sphere sun8 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 Name10 rect pool12 cube rubix13 triangle triangle14 sphere sun15 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)subqRIGHT JOINModel m ON subq.Type=m.Type AND subq.Name=m.NameWHERE 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.NameFROM Model_cte m2The 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], NameFROM #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');-- SwePesoDECLARE @MaxID TINYINT;BEGIN TRANBEGIN 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 TRANEND TRYBEGIN CATCH ROLLBACK TRANEND CATCHSELECT ID, [Type], NameFROM #Dev;DROP TABLE #Dev, #Prod;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2013-08-23 : 09:18:35
|
Thank you very much! This works very well! |
|
|
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.NameFROM #Prod pCROSS JOIN ( SELECT MAX(ID) AS ID FROM #Dev) AS dev_last_used_idWHERE NOT EXISTS( SELECT 1 FROM #Dev d WHERE d.Name = p.Name )[/code] |
|
|
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 |
|
|
|
|
|
|
|