0

I am having a problem whose solution I can't seem to figure out at the moment. Say I have a table

CREATE TABLE #Test
(
     SomeValue1 INT,
     SomeValue2 INT
)

Now, I have two other tables, say TableA and TableB, in which TableB inherits from TableA by sharing its primary key. I want to insert SomeValue1 into TableA and SomeValue2 into TableB, but in a way such that when you join TableA and TableB by their primary key you get #Test, but I can't figure out how to do it. Can anyone please help me?

B.M
  • 533
  • 2
  • 8
  • 16

2 Answers2

0
SELECT SomeValue1, SomeValue2 FROM TableA A JOIN TableB B ON A.PrimaryKey=B.PrimaryKey

That sounds like what you're asking for, from what I can tell. Maybe you're also saying you want to create a View on the two tables of existing data?

roberttdev
  • 42,762
  • 2
  • 20
  • 23
  • No, its not that. I want to be able to make a join in the way I said, but I must store the values in two separate tables independently. – B.M Jun 09 '11 at 12:29
  • Could you use CREATE VIEW and the above SQL statement to define #Test as a view that has your requested display of data from Tables A and B? If not, some background would help as the question is confusing. – roberttdev Jun 09 '11 at 12:46
0

If TableB inherits I assume TableB has the foreign key to an IDENTITY column in TableA

INSERT TableA (SomeValue) VALUES (SomeValue1)
INSERT TableB (SomeValue) VALUES (SCOPE_IDENTITY(), SomeValue2)

Otherwise, you can adapt the OUTPUT clause from your other recent questions: Output to Temporary Table in SQL Server 2005

Note:

How does this relate to Iterating Through Table in High-Performance Code? What is your actual end to end requirement... you are asking very specific questions that probably don't really help

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Yes, that's true, but I must insert several values in a row, so I can't use `SCOPE_IDENTITY()`. – B.M Jun 09 '11 at 12:33