Insert row into an empty table with INSERT ... SELECT ... FROM command in SQL-CodePudding

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

'''

CodePudding user response：

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

CodePudding user response：

Thank you for your help Amit,

The second problem is solved with the below changes:

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

The first problem still persists, but I added a dummy row to the table for now.