0

I am new to database design. I am trying to create two tables which are linked by 'name' from A table and 'Aname' from table B. But what i want to do is creating a new increment int column 'id' as a representation for 'name' in A table and then add a new column in B called 'Aid' which is the foreign key.

My idea is to first (SQL SERVER)

SELECT row_number() over(order by A.name) id, A.* from A;

But how can I link this id to 'Aid' in B table?

Or if there is any existing statements for this question?

Thank you!

Eleanor
  • 2,647
  • 5
  • 18
  • 30
  • 2
    I would urge you to read your question and ask yourself if you can make sense of what your question is. I get a general idea of what you want but what are you expecting as an answer? – Sean Lange Sep 26 '18 at 16:57
  • You are not clear. Also please read & act on [mcve]. If you don't have a natural key shared between tables then the database cannot tell you what id value goes with what row in the other table & you cannot update the table(s) en masse. But you can insert to the FK table each time you insert to the PK table: https://stackoverflow.com/q/42648/3404097. But you are not clear. Also if you did clearly state what you mean in one phrase you could google & find this obvious faq. Lack of such research merits downvotes--see the downvote arrow mouseover text. – philipxy Sep 26 '18 at 17:37

2 Answers2

1

If you have the columns already defined, you can use join in an update:

update b
    set b.a_id = a.a_id
    from b join
         a
         on b.name = a.name;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I think you need to first look up how to add an auto-increment column to an existing dataset, which there are a number of resources for online. You can find one that is specific to your version of SQL Server. Then you can look into joining your B table to your A table on that. Don't add data to a table that is intended to be joined on a ROW_NUMBER result though. This is not good database design.

Colin G
  • 155
  • 6