Insert row into an empty table with INSERT … SELECT … FROM command in SQL

I’m going to make a new table (PersonInformation) with columns of another one(Members) and some more columns like “Username”, “Password” and “PersonId”.

The command I used is: ‘INSERT INTO myTable(Columns_in_PersonInformation…) SELECT (Columns_in_Members + new_columns) FROM Members, PersonInformation WHERE Members.id = PersonInformation.PersonId’

I have two problems, the first one when the PersonInformation table is empty. The result is “0 rows affected” the second one when I add one row in the PersonInformation table, in each run, the code adds rows exponentially (2, 4, 8, 16, 32,…).

Here is my code: ”’

INSERT INTO dbo.PersonInformation (FirstName, LastName, PhoneNumber, EmailAddress, PersonId, Username, Password)
SELECT dbo.Members.FirstName,dbo.Members.LastName, dbo.Members.PhoneNumber, dbo.Members.EmailAddress ,@PersonId, @Username, @Pass
FROM dbo.Members , dbo.PersonInformation
WHERE dbo.Members.id = dbo.PersonInformation.PersonId

”’

Answer

First one when the PersonInformation table is empty – You can use left join in the query below. That will bring data from members even if personinformation is empty.

The second one when I add one row in the PersonInformation table – data increases exponentially : Use the join syntax below with may be a distinct in query.

INSERT INTO dbo.PersonInformation (FirstName, LastName, PhoneNumber, EmailAddress, PersonId, Username, Password)
SELECT members.FirstName,members.LastName, members.PhoneNumber, dbo.Members.EmailAddress ,@PersonId, @Username, @Pass
FROM dbo.Members members INNER JOIN dbo.PersonInformation pii
ON members.id = pii.PersonId

You can change it to RIGHT JOIN with DISTINCT or NULL check according to your condition

If you want only one record from the pii table, use a SELECT TOP 1 CTE or a CROSS apply