SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 INSERTING VALUES BASED ON DISTINCT FIELDS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chirag23
Starting Member

United Kingdom
13 Posts

Posted - 10/09/2012 :  04:04:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/09/2012 :  07:30:40  Show Profile  Reply with Quote
Would this work for you?
INSERT INTO YourTable
SELECT 
	'ZXY',MAX(CLIENTID),PROJECTID
FROM
	YourTable
GROUP BY
	PROJECTID;
Go to Top of Page

Chirag23
Starting Member

United Kingdom
13 Posts

Posted - 10/09/2012 :  10:39:33  Show Profile  Reply with Quote
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;


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000