Skip to content

Tag: sql

Check if role exists in DB?

I want to add users to the same role in more than one database. However the role may or may not be present in each database. How can I check if the role exists in each database and if it does, add users to that role? e.g. IF role exists BEGIN Add user in role END Answer try:

SQL method to replace repeating blanks with single blanks

sql

Is there a more elegant way of doing this. I want to replace repeating blanks with single blanks…. (its sql server 2000 – but I would prefer generic SQL) Answer Here is a simple set based way that will collapse multiple spaces into a single space by applying three replaces. Your Update statement c…

How to verify if two tables have exactly the same data?

Basically, we have one table (original table) and it is backed up into another table (backup table); thus the two tables have exactly the same schema. In the beginning, both tables (original table and backup table) contains exactly the same set of data. After some time for some reason, I need to verify whethe…

Finding duplicate rows in SQL Server

I have a SQL Server database of organizations, and there are many duplicate rows. I want to run a select statement to grab all of these and the amount of dupes, but also return the ids that are associated with each organization. A statement like: Will return something like But I’d also like to grab the …

How to create a summary view from multiple views?

Given I have the following view where N=1..100 Currently we have summary views which insert to a table like The table is then used to create an overall summary like Is there a way to create summary_view_all_plants without having to create each individual summary_view_N? I would like to be able to iterate thou…

Should I quote numbers in SQL?

sql

I remember reading about quoting stuff when doing a SQL query and that when you quote something, it becomes a string. I also read that numbers should not be quoted. Now, I can’t find that quotation and I need to refresh my memory to see if I should quote numbers. Answer You should not quote numbers if y…

Using alias in query and using it

I have a doubt and question regarding alias in sql. If i want to use the alias in same query can i use it. For eg: Consider Table name xyz with column a and b Is this possible in some way ? Answer You are talking about giving an identifier to an expression in a query and then reusing that