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.
LVL 1
H-SCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Vikas GargBusiness Intelligence DeveloperCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

H-SCAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
H-SCAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Try this then:
WITH CTE_resource
AS (
	SELECT id
	FROM resource_shared
	WHERE resource <> 'YourStaticValueHere'
	GROUP BY id
	)
INSERT INTO resource_shared (id, resource)
SELECT id, 'YourStaticValueHere'
FROM CTE_resource

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.