Unanswered: cycling through results of a select statement
I am new to stored procedures and T-SQL so please stick with me. I have a table that holds information about companies. I am trying to write a stored procedure that when run will query that table and find out if there are more than one entry of that company. All company names in that table must be unique (they can only occur once), if they occur more than once I need to flag it for reporting.
So what is the best way to go about this? Essentially what i was thinking was doing a select * on the table and then going from the first entry to the last and at each entry running a select * from table where companyname = @nameofcompany. @nameofcompany would be the name for that entry. If the select statement revealed more than one entry then i would know there was a problem.
Like I said I am new and this is probably very simple but i need a little help getting started
I am originally from Vernon (Mountain Creek). Went to school at Stevens Institute of Technology in Hoboken, NJ.
I didn't create the dll for this database so i just loaded the schema by the .sql file. Right now I am handed an excel template and i wrote somce vb code to go through that excel file pull out the information i want and then write it to a text file delimited with "#" and then i load it into SQL server with a bulk load command. If the user sends me a template that already has duplicate entries in it and i try to load the data into SQL column that has a unique indentifier what will happen? Will it throw an error? If this is the case then it would probably be better to get the data in the database and then decided whether or not it is a duplicate.
Redefining the table seems like the simplest way to go but i don't want to break functionality in the process
I just tried to add this code and it is complaining on the second line in reference to the o. Here is the error: Error 170: Line 2: incorrect syntax near 'o'. As I said i am new to stored procedures and T-SQL do i need to declare the o and i as variables somewhere?
I didn't test the code, so you gave me a start...but the code does work...
Where are you running this from? Do you have query analyzer and the other sql server client toools?
SET NOCOUNT ON
CREATE TABLE myTable99 (Company_Name varchar(50))
INSERT INTO myTable99(Company_Name)
SELECT 'Vernon Valley' UNION ALL
SELECT 'Mountain Creek' UNION ALL
SELECT 'Hidden Valley' UNION ALL
SELECT 'Campgaw' UNION ALL
SELECT 'Break Neck Road' UNION ALL
SELECT 'High Point' UNION ALL
SELECT 'Octogon Lounge' UNION ALL
SELECT 'Great Gorge' UNION ALL
SELECT 'Mountain Creek'
FROM myTable99 o
WHERE EXISTS ( SELECT Company_Name
FROM myTable99 i
WHERE o.Company_Name = i.Company_Name
GROUP BY Company_Name
HAVING COUNT(*) > 1)
SET NOCOUNT OFF
DROP TABLE myTable99