How to Make an Entry in a Table and Do it in Another

Well i am currently making a system using ASP.net C# and SQL Server

What i want to achieve is when i create an entry in a Table. Lets say "Customers" with primary key "customerID".

When i create entries in the table and i have a Column with Identity and i successfully entered every data needed.

I want to create an entry in another table with the same value as my primary key.

So in the first table

Customers
customerID: 1
Name: Mr. Random
Pass: RandomPassword

it will create an entry in another table like this and the other data are empty.

CustomersOrder
customerID: 1
order1: <empty>
order2: <empty>

i want to do this because i separated some data on tables and i want them to have the same key or ID to search or edit them easily.

The way this is normally accomplished is using Foreign Key constraints in the database e.g.

Customers
customerID: (generated by database as primary key identity)
Name: Mr. Random
Pass: RandomPassword

CustomersOrder
orderID: (generated by database as primary key identity)
customerID: (the value from customerID in the Customer table)

OrderedItems
orderedItemID: (generated by the database as primary key identity)
itemID: (This will point as a foreign key to your items table where you get the details of an item - description, price, availability, etc.)
orderID: (foreign key tying to order)
quantity: (number of this item included in this order)

You create a foreign key constraint on the customerID column of the CustomersOrder table tying it to the customerID column of the Customers table

This allows the database to ensure that you do not have an order that is not associated with a customer. It also ties order records to customer records in a one to many way (each customer can have many orders). Now for your searches, you can join the tables in the from on customerID.

Orders also have a one to many with items (an order can have many items).

This is known as "Referential Integrity", it alows the database to ensure that if a record in one table "refers" to a record in another table, that the record being referred to exists.

genius, i was already there, but lets say i have 2 tables

table 1 with primary key
table 2 with no primary key but set a foreign key to table 1

is it possible that when i input data on table 1 and generates a value in its primary key, it will automatically create the record on table 2 with the same value from table 1 primary key

like

table2foreignkey=table1primarykey, even though i haven't filled in the values in table 2

a visual should explain better

http://i1082.photobucket.com/albums/j369/ashieboy/1.png

This is the table with the primary key and has identity set to 1 seed. When i fill every data in there
i want its primary key value (in this case 1) to be the same value of the foreign key in table2 even without me filling up the values there.

http://i1082.photobucket.com/albums/j369/ashieboy/2.png

i haven't set this foreign key to have identity

First, I would recommend that you have a primary key column on all of your tables. This allows for deeper nested referencing as I demonstrated above and also primary keys are automatically created with clustered indexes for better performance in your searches.

Second, you could accomplish what you are asking using a trigger on the Customer table, however, I fail to see the advantage in creating an order for a customer before they have actually ordered anything.

Normally, the way these things work is...

Customer logs in - during the authentication you go to the database and get the customer information.

Customer wants to create a new order - you use the customerID you obtained during the authentication process to create the new order (write it to the foreign key of the orders table).

Customer selects items to order - you use the orderID obtained while creating the order to write records to the orderedItems table.

Now you have items associated with an order that is associated with a customer.

The process repeats next time the customer needs to order something.

well typically its not going to be used as a customer system.

its a hospital system

when a new patient comes in the hospital gets necessary data like name and etc but they dont need data like diagnosis/cause of death
and etc.

so those info that are not needed in the initial input, i separated in another table, so the reason i want to link those 2 table is to make sure that when the info gets edit/updated, i can easily locate it using the primarykey in table 1 to any other table.

Then my reccomendations stand. I would create an insert trigger on the customer table that on insert, creates a record in the orders table using the primary key value from the newly created customer record.

All of the info needed to accomplish this will be in your SQL Server Books Online.

ill try it tomorrow, thankyou very much, im from not from us so its like 12:30am here. Thankyou very much for your help man.

In a hospital setting, you are going to accumulate a lot of records over time, you should create indexes on the table columns that you use a lot in joins and in your where clauses to improve query performance. If you don't, you'll start eventually getting timeouts because your queries will start taking too long to run. Also, setup a job to re-index your tables once or twice a month.

can you further explain this indexing? i was also considering the speed in query when i have lots of data in the database.

Just Google "SQL Server Table Indexing" there is lots of information on the subject.