CODEWITHSUNDEEP
sqlsql-server
Spidy
Spidy

Reputation: 23

How to add values of primary key column into foreign key column of other table

How to add values of primary key column into foreign key column of other table: I'm using SQL Server 2012

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, 
ADDRESS  CHAR (25) , SALARY   DECIMAL (18, 2), PRIMARY KEY (ID));

CREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references
CUSTOMERS(ID), AMOUNT VARCHAR (255), PRIMARY KEY (ID));

Here i need to take all values from primary key table 'customers' from column 'ID' to foreign key table orders to column 'ID' 

DECLARE @A INT, @DATE DATETIME, @C_ID INT, @AMOUNTS INT;

SET @A =1;
SET @DATE ='2009-10-08 00:00:00';
SET @C_ID = 100
SET @AMOUNTS=1000;

WHILE @A <= 7

BEGIN
SET @DATE = DATEADD(DAY,1,@DATE);
SET @C_ID = @C_ID + 1
SET @AMOUNTS = @AMOUNTS+100;

INSERT INTO ORDERS(ID, DATE, CUSTOMER_ID,AMOUNT)
    SELECT ID, @DATE, @C_ID, @AMOUNTS FROM CUSTOMERS WHERE AGE like'%';

SET @A = @A+1;
END

Upvotes: 0

Views: 824

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

You have set your Orders table up with a Foreign Key on Customer_ID that references Customers(ID). So you can you only add rows to Orders if the ID exists in Customers. Right now you are trying to insert 101-107 in that column every time, so check if those IDs exists in Customers.

It seems to me that you SHOULD want to insert the Customers.ID column in Orders.Customer_ID, rather than Orders.ID. Is that what you meant?

Upvotes: 1

Related Questions