INSERTING VALUES BASED ON DISTINCT FIELDS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

INSERTING VALUES BASED ON DISTINCT FIELDS

Author

Topic

Chirag23
Starting Member

13 Posts

Posted - 2012-10-09 : 04:04:49

Hi,

I have a table that is populated with a USERID, CLIENTID and PROJECTID. One client can have multiple projects and so for each project a new entry has to be put in for a single user. For example:

USERID_CLIENTID_PROJECTID
ABB_012345_000001
ABB_012345_000002
ABB_012346_000034
CBA_012346_000034

What I'm trying to do is for each distinct project to add a new userID to it. So for example I want to add USERID ZXY to every project. So first I identify all the unique PROJECTID's

SELECT DISTINCT PROJECTID FROM TABLE

PROJECTID
000001
000002
000034

What I need help on is how I use these values and INSERT INTO TABLE all these entries again with the USERID ZXY. So that my table looks like this:

USERID_CLIENTID_PROJECTID
ABB_012345_000001
ABB_012345_000002
ABB_012346_000034
CBA_012346_000034
ZXY_012345_000001
ZXY_012345_000002
ZXY_012346_000034

The CLIENTID and PROJECTID are both unique values independent of each other. I hope this makes sense. I would really appreciate some help.

Thanks

Chirag

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-09 : 07:30:40

Would this work for you?

INSERT INTO YourTable
SELECT 
	'ZXY',MAX(CLIENTID),PROJECTID
FROM
	YourTable
GROUP BY
	PROJECTID;

Chirag23
Starting Member

13 Posts

Posted - 2012-10-09 : 10:39:33

Not quite: This worked for me:

INSERT tbl(userid, clientid, projectid)
SELECT DISTINCT 'ZXY', a.clientid, a.projectid
FROM tbl a
WHERE NOT EXISTS (SELECT *
FROM tbl b
WHERE b.userid = 'XYZ'
AND b.clientid = a.clientid
AND b.projectid = b.projectid)

quote:
Originally posted by sunitabeck

Would this work for you?
INSERT INTO YourTable
SELECT
'ZXY',MAX(CLIENTID),PROJECTID
FROM
YourTable
GROUP BY
PROJECTID;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources