August 13, 2010 at 12:35 pm
no it sticks around permanently. They literally want a copy of the data. The temp tables will go away every time i make a copy cause i'm create a table variable.
Declare @MyLocationPkey Table
(
newPkey int,
oldpkey int
);
August 13, 2010 at 1:05 pm
ok i get what you are trying to do.
Have you thought about triggers? You could maybe add an insert trigger the MainInformation and Locations tables. You would probably need to add 1 more column like OriginalMainPKey so that you can determine if the record being inserted is a copy or a new record.
then on insert to MainInformation if the new record is a copy, you could insert into Locations [select whatever from Locations where MainPKey = inserted.OriginalMainPKey ]
Then you could setup the same type of structure for the Equipment table. Your tables would look something like this.
Create Table dbo.MainInformation(
MainPkey int IDENTITY(1,1) NOT NULL,
Name varchar(100),
OriginalMainPKey int
)
Create Table dbo.Locations(
LocationPKey int IDENTITY(1,1) NOT NULL,
MainPkey int not null,
LocationName varchar(100),
LocationAddress varchar(100),
OriginalLocationPKey int
)
Create Table dbo.Equipment(
EquipmentPKey int IDENTITY(1,1) NOT NULL,
LocationPKey int NOT NULL,
PartNumber varchar(100),
Quantity int
)
Something like this work?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2010 at 1:08 pm
Ya I think that's about what I ended up with. I didn't use a trigger tho just did it with the output command in the insert. So sounds like i am doing it the right way.
Thanks for your all help.
August 13, 2010 at 1:11 pm
np hope it works. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply