How to insert PK column values into FK column of another table

Question

Hi,

I want to insert tableA P_ID (GUID) values into tableB tableA_Parent column. I couldn 't insert these GIDs into the second table. Following are the definitions and sample data of tableA and tableB.

CREATE TABLE dbo.tableA (
[P_ID] varchar NOT NULL Primary key,
[Acc] varchar NULL,
[P_date] varchar NULL,
[Num_S] varchar NULL,
[LId] varchar NOT NULL ) ;

INSERT INTO dbo.tableA(P_ID,Acc,P_Date,Num_S,LId)
Select NEWID(),acc_p,pdate,num,ids From dbo.x ;

P_ID Acc P_Date Num_S LId
1-a 3434 03-10-2022 111 50
2-b 222 05-10-2022 23 50

CREATE TABLE dbo.tableB (
[P_ID] varchar NOT NULL Primary key,
[tableA_Parent] varchar(100) NULL,
[Period_S] [datetime] NULL,
[Period_E] [datetime] NULL,
[LId] varchar NOT NULL ) ;

INSERT INTO dbo.tableA(P_ID,tableA_Parent,Period_S,Period_E,LId)
Select NEWID(),@tableA_pid,adate,bdate,ids
From dbo.y ;

 P_ID                   tableA_Parent                         Period_S                           Period_E                        LId  

----------------------------------------------------------------------------------------------------------------

11-s 1-a 03-10-2022 02-12-2022 50
22-d 1-a 05-10-2022 01-11-2022 50
33-a 2-b 05-10-2022 01-11-2022 50

I am not able to insert tableA Primary key (GUID) values into tableB (tableA_Parent) column.

Answer 1

Hi @hamb

I am not able to insert tableA Primary key (GUID) values into tableB (tableA_Parent) column.

Are there any error messages? Please post them.

How about set the default of a PK column to be NEWSEQUENTIALID(), like this: [P_ID] UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I am not able to insert tableA Primary key (GUID) values into tableB (tableA_Parent) column.

Your post is not very clear. Why can't you insert into the "second" table? Do you get any error message (which one)?

Answer 3

Try one of solutions:

declare @tableA_pid uniqueidentifier = NEWID( )  
  
INSERT INTO dbo.tableA(P_ID,Acc,P_Date,Num_S,LId)  
Select @tableA_pid,acc_p,pdate,num,ids From dbo.x  
  
INSERT INTO dbo.tableB(P_ID,tableA_Parent,Period_S,Period_E,LId)  
Select NEWID(),@tableA_pid,adate,bdate,ids From dbo.y  

Answer 4

You can use the OUTPUT clause to capture the generated value:

   INSERT (...)  
   OUTPUT inserted.gid, inserted.acc INTO @mapping(gid, acc)  
   SELECT ...  

This requires that there is something to map the child table (unless you are only inserting one row into the parent.

By the way, varchar(100) is a very funny data type for something that holds guids - the normal type to use is uniqueidentifier.

Answer 5

Hi @hamb

You want to insert values from Table A into Table B

But in the question the query in the end you provided shows that you are trying to insert into Table A itself (I am guessing just a confusion) and you are doing the insert from table dbo.y not from table dbo.tableA like you said you want to do.

INSERT INTO dbo.tableA (P_ID,tableA_Parent,Period_S,Period_E,LId)  
Select NEWID(),@tableA_pid,adate,bdate,ids  
From dbo.y ;  

Error: Violation of PRIMARY KEY constraint PK_tableA_FC47868DADCB2466. Cannot insert duplicate key in object dbo.tableA. The duplicate key value is (4A2DAD5D-3506-4D9B-84AB-66AAF13DC9F1).

Now, the error above basically says the values you are trying to insert in the Table A (again not Table B) already exists in the Table A and primary key doesn't allows duplicate values to be entered in the table all values should be unique.
You said:

I want to insert tableA P_ID (GUID) values into tableB tableA_Parent column.

To insert the values of column P_ID from Table A into column tableA_Parent of Table B the command will be:

INSERT INTO dbo.tableB ((tableA_Parent)  
SELECT P_ID from dbo.tableA  

Note: But you need to decide on what filter or condition you need to do this and add where or join accordingly in the select query, ie which PID from table A should become tableA_Parent for which P_ID in tableB

Ps. You need to be clear about from which table , what values , on what conditions into which table and it will help all of us including you to solve this much more quickly.

Regards,
Shivam