0

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

'''

Vahid
  • 13
  • 2
  • can you make a fiddle to explain your issue in more detail http://sqlfiddle.com/ – Nitin Sawant May 17 '22 at 04:17
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**30 years!!** ago) and its use is discouraged – marc_s May 17 '22 at 04:57

2 Answers2

0

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

Amit Kumar Singh
  • 4,393
  • 2
  • 9
  • 22
  • Thank you Amit, Yes, you're right about the first question, but the problem is when I use LEFT JOIN the Member Table is completely copied into the PersonInformation table that I don't want that. I want only one row with this condition "Members.id = @PersonId" copied into the PersonInformation table and also add the "Username", "Password" and "PersonID" in from of the row. – Vahid May 17 '22 at 04:56
  • Updated answer for inclusion of one record from pii table – Amit Kumar Singh May 17 '22 at 05:37
0

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.

Vahid
  • 13
  • 2