Click here to Skip to main content
15,936,046 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i need to share the id value for both tables like
df
1
2
3
5
6
10
mf
4
7
8
9

What I have tried:

i have created a auto increment number for one table
CREATE TABLE df (
id IDENTITY(1,1)
);

CREATE TABLE mf (
id IDENTITY(1,1)
);
Posted
Updated 18-Jan-18 21:48pm
Comments
RDBurmon 19-Jan-18 1:41am    
so you like to insert record in first table "df" with auto increment ID (suppose 1) and then would like to insert the record in second table "mf" with similar id like in table "DF" (which is also 1)

am I correct?
Pasupathy Msc 30-Jan-18 7:41am    
Yes sir

1 solution

No, you don't want to do that. The idea of an IDENTITY field is that it is a unique identifier of a row within a table - and multiple tables will have different rows with different identities.
The only time you would want or need the same row identity for each row in two different tables is when you have badly designed your system, creating two tables where one is needed. The solution to that is simple: make it one table. If you don't, then the multiuser aspect of SQL Server is going to bite you - and badly - when you get to production.
If there is intended to be a correlation between the two tables, then that's simple: each table had a separate ID value (which doesn't have to be the same) but one table has a column which contains a Foreign Key relationship with the other and which contains the ID of the row in the second table:
CustNames
ID      INT, IDENTITY
CName   NVARCHAR(255)

ID      Cname
1       Jones Designs
2       Smiths Instruments
3       Joes Chips

CustAddresses
ID      INT, IDENTITY
Addr    NVARCHAR(1024)
CID     INT, FOREIGN KEY to CustNames.ID

ID      Addr                            CID
1       Smiths House, Smiths Lane       2
2       JonesTown                       1
3       High Street, Potato Town, Idaho 3
See what I mean?
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900