asked on

insert a record for each distinct id

Hello,
I would like to run an insert to a table to insert a new row for each distinct id within the table.
for example:

the current data may look like this
id data
100 test
100 test2
100 test3
200 test1
200 test2
200 test3

I would like the insert to see id's 100 and 200 and insert a row for each if the data column content does not exist.

for example the insert would not add a 200|test2 but would add a 200|hellothisisatest

Thanks in advance.

Jim Horn

Give us some names behind 'a table' and 'the table, and explain in greater detail with sample data 'if the data column content does not exist.' and how 'hellothisisatest' comes into play.

So far..

INSERT INTO target_table (id) 
SELECT DISTINCT id 
FROM source_table

Open in new window

Vitor Montalvão

I would like to run an insert to a table to insert a new row for each distinct id within the table.

By your sample they aren't distinct. I can see 3 rows of each id (100 & 200).

for example the insert would not add a 200|test2 but would add a 200|hellothisisatest

If you create a Primary Key on id+data it will raise a duplicate error if you try to insert 200|test2.

Vikas Garg

Hi,

Not clear with your requirement

but

you can try this

INSERT INTO target_table (id) 
SELECT DISTINCT id ,'hellothisisatest'
FROM source_table

Open in new window

H-SC

ASKER

Maybe I was not too clear on the example, apologies.
So, I have a table called resource_shared, it has an identity column called control_number and another int column called id and a column called resource (nvarchar 100).

I would like to write an insert statement to insert into the resource_shared table for each distinct id that exists in resource_shared table and limit the insert to only insert if the said resource does not exist for that id.

Vitor Montalvão

I would like to write an insert statement to insert into the resource_shared table for each distinct id that exists in resource_shared table and limit the insert to only insert if the said resource does not exist for that id.

This statement is not clear. You want to insert in resource_shared table from the same table?

H-SC

ASKER

Yes insert into resource_shared a static value into column resource (nvarchar 100) for each distinct id where the static value that I pass does not already exist for the given distinct id.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial