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
 General SQL Server Forums
 New to SQL Server Programming
 Query Design for Adding, Copying and Updating
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bscivolette
Starting Member

USA
9 Posts

Posted - 07/08/2013 :  11:59:24  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 07/08/2013 :  12:35:47  Show Profile  Reply with Quote
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
Go to Top of Page

bscivolette
Starting Member

USA
9 Posts

Posted - 07/08/2013 :  14:18:09  Show Profile  Reply with Quote
Good Afternoon,

I am using 2008 R2.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/09/2013 :  01:41:51  Show Profile  Reply with Quote
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
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.06 seconds. Powered By: Snitz Forums 2000