Rohan.SQL (4/2/2012)

---

this should work fine...

SELECT UPPER(os.cpt4_id)

FROM #TRANS t

CROSS APPLY ( SELECT TOP 1 cpt4_id

...

scope_identity() as I mentioned earlier.

capn.hector (4/2/2012)

---

SQLKnowItAll (4/2/2012)

---

capn.hector (4/2/2012)

---

i was wrong, here it is with the join and only pulling authid's in the trans table

;WITH row AS (SELECT ROW_NUMBER() OVER (Partition by auth_id ORDER BY...

capn.hector (4/2/2012)

---

i was wrong, here it is with the join and only pulling authid's in the trans table

;WITH row AS (SELECT ROW_NUMBER() OVER (Partition by auth_id ORDER BY key_id) AS...

Ahh... Now I see. Try this.

SELECT os1.auth_id, os2.min_key_id, UPPER(os2.cpt4_id)

FROM #outpatient_service os1

INNER JOIN #TRANS t

ON os1.auth_id = t.auth_id

INNER JOIN (SELECT auth_id, MIN(key_id) AS min_key_id

...

You can also do the same, but move the query to a CTE to generate the min numbers, then join.

DECLARE @auth_id INT

SET @AUTH_ID = 95385

SELECT UPPER(cpt4_id)

FROM #outpatient_service os1

INNER JOIN

(SELECT MIN(key_id) AS min_key_id

...

Can you...

How is your SQL Instance configured? Named Pipes, TCP/IP, Remote connections enabled... We need to know these things, as it could simply be a configuration setting. Also, since you have...

I'm not really sure what you are asking. 800gb is 800gb, so how can you store 800gb on less space? Compression may help, but you still need the space that...

If you are looking to generate scripts for your existing synonyms, try working with this:

SELECT 'CREATE SYNONYM ' + name + ' FOR ' + base_object_name

FROM sys.synonyms

I'm not exactly sure what you are looking for in terms of logins, but this article may help: http://support.microsoft.com/kb/246133

For scripting synonyms... You mean scripting them out of the current database...

What does it mean that you are starting anew project? Are you going to be a director, DBA, project manager? KT is all subjective based on what you need to...

drew.georgopulos (3/29/2012)

---

thanks very much, i appreciate the reminder.

would you mind please commenting on the idea that without a uniqueness check, surrogate keys promote duplicates to uniqueness? maybe that wasnt phrased...

My guess is that it is the UNION ALL. Break apart your statements to see which is taking the longest and let us know. Also, we cannot do...