Query Design for Adding, Copying and Updating

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

General SQL Server Forums

New to SQL Server Programming

Query Design for Adding, Copying and Updating

Author

Topic

bscivolette
Starting Member

9 Posts

Posted - 2013-07-08 : 11:59:24

Good Afternoon,

I have a rather complex query (to me at least) that I need to create but I am unsure of where to start. The query requires me to copy existing data into a new row (which will then create a new ID) as well as update all existing records with the newly created id.

More specifically, I need to separate the data associated with LocationID 219 from it's parent, CompanyID 992.

Ideally I want to copy the data associated with LocationId 219 and then make a new CompanyId with the copied data (which will also create a new LocationID). Since this new record is no longer going to be associated with CompanyID 992 I will want to remove/delete/drop it from that record.

Finally, and perhaps most difficult of all, I need to update all tables that reference the old ID's together (992 / 219) to reflect the newly created Company ID and Location ID.

I'm obviously very new to SQL but I am a fast learner and I learn best by asking questions and attempting to do it on my own. I'm grateful for any advice, options, feedback, etc.

PS - If I didn't provide sufficient information please let me know. I'll be happy to provide more details as needed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 12:35:47

which version are you using? If SQL Server 2005 or above you can use OUTPUT clause for this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bscivolette
Starting Member

9 Posts

Posted - 2013-07-08 : 14:18:09

Good Afternoon,

I am using 2008 R2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 01:41:51

quote:
Originally posted by bscivolette

Good Afternoon,

I am using 2008 R2.

Then you can do something like


declare @INSERTED_LOCATIONID int,@INSERTED_COMPANY int

--to create a new location from existing 219 details
INSERT INTO LOcation
SELECT othercolumns...
FROM Location
WHERE LocationID=219

--Capture id created
SET @INSERTED_LOCATION = SCOPE_IDENTITY()

--To create new companyID
INSERT INTO Company 
SELECT columns...
FROM Company
WHERE CompanyID=992

--Capture id created
SET @INSERTED_COMPANY = SCOPE_IDENTITY()

--To update the relationships in other tables
UPDATE t
SET CompanyID=@INSERTED_COMPANY,
LocationID = @INSERTED_LOCATION
FROM Table t 
WHERE CompanyID=992
AND LocationID = 219

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources