I have two tables, Houses and Stores. Both have Latitude and Longitude fields. I need to make a view that would return for each record in Houses HouseId and StoreId, where StoreId is the ID of the …
Tag: sql-server-2008
Transform and group row data into columns
I’m looking for a way to combine different rows into a single row with columns. Let’s take this example table: Desired output: Please note that Data centers are not necessarily these three, they can be N different data centers that I will not know in advance. Answer With a known set of sourceDataCenter values, you can just use a simple
Sql Server 2008 Update query is taking so much time
I have a table name Companies with 372370 records. And there is only one row which has CustomerNo = ‘YP20324’. I an running following query and its taking so much time I waited 5 minutes and it was still running. I couldn’t figure out where is the problem. Answer You don’t have triggers on update on that table? Do you
How to convert varchar column values into int?
I have a column toysid which is of varchar datatype in one table. Also another table having same the column toysid with int datatype (I know I can modify column for first table but I have to follow some procedure to keep it as varchar). I have written following query to join the table: The query returns error: Conversion failed
Add/remove columns of a table – code maintenance / optimisation
What is the best way to maintain code of a big project? Let’s say you have 1000 stored procedures, and you have to add a new column to a table (or remove) There might be 1-2 or 30 stored procedures, that might be affected. Just a single “search” for the tablename might not be good enough, let’s say you only
Backup a single table with its data from a database in sql server 2008
I want to get a backup of a single table with its data from a database in SQL Server using a script. How can I do that? Answer There are many ways you can take back of table. BCP (BULK COPY PROGRAM) Generate Table Script with data Make a copy of table using SELECT INTO, example here SAVE Table Data
Drop Several Tables at Once
I have some tables that I need to drop on a regular basis. The names of the tables sometimes change but the table names always begin with ‘db_comp_temp’. Is it possible to write some SQL that will in effect do something like: Thanks in advance, Answer No, there is no wildcard support in DDL. Or: You could also do it
sql-ex exercise 39 failed on second server error?
Exercise 39: Define the ships that “survived for future battles”; being damaged in one battle, they took part in another. Database Schema: http://www.sql-ex.ru/help/select13.php#db_3 My approach: sql-ex says Your query produced correct result set on main database, but it failed test on second, checking database correct result matched with my output. Where I failed? Answer Solved! needed to add Distinct
How do you leave comments in SQL Server 2008 R2 view with SSMS?
According to multiple sources Microsoft , SQL Server Administration Blog | zarez.net adding comments to SQL and doing so with SSMS is a piece of cake. And for the most part they are probably right. But when I log in and create a view I have been unable to leave comments in it. If I use two hyphens (–) the
Unpivot with column name
I have a table StudentMarks with columns Name, Maths, Science, English. Data is like I want to get it arranged like the following: With unpivot I am able to get Name, Marks properly, but not able to get the column name in the source table to the Subject column in the desired result set. How can I achieve this? I