1

I have 2 tables. Tab A and Tab B

Tab A

Id       Name
2        John
3        Peter
4        Rachel

I need to insert records in table B to get following:

Tab B

PrId    ID       Resident     Date.
1       2        Yes          7/1/2018
2       3        Yes          7/1/2018
3       4        Yes          7/1/2018

PrId is the primary key of table B, Id comes from Table A and rest of the values are hard coded.

Please suggest the script to do the same

user1326379
  • 170
  • 1
  • 2
  • 10
  • The ID of table one would be considered a `foreign key` in table 2, therefore you should have no problem inserting the key from table 1 into the `foreign key` column `ID` in table 2 – Ryan Wilson Jul 30 '18 at 16:07
  • 1
    Sounds like you are asking [**how to create a foreign key**](https://stackoverflow.com/questions/48772/how-do-i-create-a-foreign-key-in-sql-server) – S3S Jul 30 '18 at 16:08
  • Is PrId an IDENTITY? – critical_error Jul 30 '18 at 17:16

2 Answers2

5

Are you looking to simply do a straight-forward insert from one table into another? If so, here's an example you can run in SSMS:

-- create table variables for illustration purposes --

DECLARE @tableA TABLE ( [Id] INT, [Name] VARCHAR(10) );
DECLARE @tableB TABLE ( [PrId] INT IDENTITY (1, 1), [Id] INT, [Resident] VARCHAR(10), [Date] SMALLDATETIME );

-- insert sample data into @tableA --

INSERT INTO @tableA ( [Id], [Name] ) VALUES ( 2, 'John' ), ( 3, 'Peter' ), ( 4, 'Rachel' );

-- show rows in @tableA --

SELECT * FROM @tableA;

/*
    +----+--------+
    | Id |  Name  |
    +----+--------+
    |  2 | John   |
    |  3 | Peter  |
    |  4 | Rachel |
    +----+--------+
*/

-- insert records from @tableA to @tableB --

INSERT INTO @tableB (
    [Id], [Resident], [Date]
)
SELECT
    [Id], 'Yes', '07/01/2018'
FROM @tableA;

-- show inserted rows in @tableB --

SELECT * FROM @tableB;

/*
+------+----+----------+---------------------+
| PrId | Id | Resident |        Date         |
+------+----+----------+---------------------+
|    1 |  2 | Yes      | 2018-07-01 00:00:00 |
|    2 |  3 | Yes      | 2018-07-01 00:00:00 |
|    3 |  4 | Yes      | 2018-07-01 00:00:00 |
+------+----+----------+---------------------+
*/
critical_error
  • 6,306
  • 3
  • 14
  • 16
0

If you have your tables set up with a Primary Key and Foreign Key then you can run the following select query to join the two tables into one. 

select a.PrId, b.ID, a.Resident, a.Date 
from Table a inner join 
Table b on a.PrID = b.ID

look up inner joins here https://www.w3schools.com/sql/sql_join_inner.asp

and foreign key https://www.w3schools.com/sql/sql_foreignkey.asp

In the future please do some research before posting

Sudosu0
  • 167
  • 12