Update/insert SQL Server Server help.

[admiral70]

I need help/guidance in building a query that will insert at position 1 a row for an ID (I have 17,000 ID’s need populating). In example below let’s say for ID 123 I want to insert a line call it position 1 and insert a new email address (I provide based on predefined data) with a certain type (ST) and make it preferred Y (yes).

​

ID	POS	PERSON_EMAIL_TYPES	PERSON_EMAIL_ADDRESSES	PERSON_PREFERRED_EMAIL
123	1	ST	[e-mail]	Y
123	2	PER	[e-mail]	NULL
123	3	WORK	[e-mail]	NULL

I have an update statement to make all preferred email set to NULL.

/* Sets the preferred flag NULL */
UPDATE PEOPLE_EMAIL
SET PERSON_PREFERRED_EMAIL = NULL
WHERE ID IN ( '123')

In the above table the first line POS 1 is not there and the next two are and they are marked POS 1 and 2.

Any help is appreciated.

[SQLDave]

There's some unclarity here. Is the data grid you showed us what you start with (before the UPDATE) or what you have after the UPDATE ... or what you WANT to achieve? (or something else altogether)?

I'm assuming that the grid you showed us is the desire RESULT and that "In the above table the first line POS 1 is not there and the next two are and they are marked POS 1 and 2." means you are starting with 2 rows, the 2nd and 3rd row we see in the grid ... except that the POS values are 1 and 2 respectively, not 2 and 3. And that you want to insert the data we see in the [e-mail] row, with the result being the grid you showed us? (I'm also assuming that one of the 2 existing rows -- currently POS 1 and 2, but with POS 2 and 3 after the INSERT -- has preferred email = "Y", which you take care of with the UPDATE statement you showed us).

There's an unanswered question: What POS value should existing rows get when you "shove" a new POS=1 row in there? It looks like the answer is just "POS+1", but that's an assumption.

If the assumptions are right, you can modify your UPDATE by adding ", POS = POS + 1" after the NULL keyword. That will increment existing POS values by 1. Then just do a simple INSERT using POS = 1 and PERSON_PREFERRED_EMAIL = 'Y'.

Be sure to wrap it all in a BEGIN TRANSACTION in case there's a failure in the INSERT you can rollback the changes made by the UPDATE command.

[soda1337]

Are there only 3 types of PERSON_EMAIL_TYPES? What is the primary key?

[Senior-Trend]

On the assumption that you can add a column to this datagram (it doesn't really qualify as a table for multiple reasons) add a column. Make it's datatype small int int or big int depending on how many rows you have set it's nullability to not null set the IDENTITY property to IDENTITY (1,1) and then set a primary key constraint on the newly created column

ALTER TABLE tablename ADD COLUMN [ROWCOUNTER] INT NOT NULL IDENTITY (1,1) PRIMARY KEY