INSERTING VALUES BASED ON DISTINCT FIELDS

Question

Post reply

INSERTING VALUES BASED ON DISTINCT FIELDS

_chirag

SSC Rookie

Points: 25
More actions
October 9, 2012 at 4:12 am

#282813

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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

DROP TABLE #SampleData

CREATE TABLE #SampleData (USERID CHAR(3), CLIENTID CHAR(6), PROJECTID CHAR(6))

INSERT INTO #Sampledata (USERID, CLIENTID, PROJECTID)

SELECT 'ABB', '012345', '000001' UNION ALL

SELECT 'ABB', '012345', '000002' UNION ALL

SELECT 'ABB', '012346', '000034' UNION ALL

SELECT 'CBA', '012346', '000034'

SELECT * FROM #SampleData

INSERT INTO #Sampledata (

USERID,

CLIENTID,

PROJECTID)

SELECT

USERID = 'ZXY',

CLIENTID,

PROJECTID

FROM #Sampledata

GROUP BY CLIENTID, PROJECTID

SELECT * FROM #SampleData

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Or with using DISTINCT instead of GROUP BY:

INSERT INTO #Sampledata (USERID, CLIENTID, PROJECTID)

SELECT DISTINCT 'YYY', CLIENTID, PROJECTID

FROM #Sampledata

Actually your data model doesn't look right.

If for every user, you should have the same combination of CLIENTID & PROJECTID's,

why do your store them in one table?

Also if every CLIENTID should be associated with every possible PROJECTID, you don't need them both in the same table.

What you have looks like Cartesian Product of three tables: USER, CLIENT and PROJECT!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Could be because it's homework season, Eugene.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

_chirag SSC Rookie Points: 25 More actions · Answer 4

Thanks Eugene,

The table is used as a lookup by the application. So for example:

We have a table called Table.Project

PROJECTID_CLIENTID_PROJECTNAME_STATUS

000001_012345_PROJECTRED_R

000002_012345_PROJECTRED_R

000034_012346_PROJECTBLUE_R

000033_012343_PROJECTFISH_O

For all the projects that are marked with the status 'R' These are restricted to everybody except those users that are listed in the table.RESTRICTEDPROJECTS

USERID_CLIENTID_PROJECTID

ABB_012345_000001

ABB_012345_000002

ABB_012346_000034

CBA_012346_000034

So what I'm trying to do is to create is a script that I can add a USERID to all of the matters that are restricted. You raise a valid point as to why is it necessary to include the CLIENTID if PROJECTID is unique anyway. But unfortunately this is required by the system so I'm having to just work around this.

Chirag

_chirag SSC Rookie Points: 25 More actions · Answer 5

This worked with the following:

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 = 'ZXY'

AND b.clientid = a.clientid

AND b.projectid = b.projectid)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 6

Here's another way using ROW_NUMBER():

;WITH Projects AS (

SELECT CLIENTID, PROJECTID

,rn=ROW_NUMBER() OVER (PARTITION BY CLIENTID, PROJECTID ORDER BY (SELECT NULL))

FROM #SampleData)

INSERT INTO #SampleData

SELECT 'XYZ', CLIENTID, PROJECTID

FROM Projects

WHERE rn=1

Note that this assumes that a project ID may not be unique across clients.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St