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 sourceDataC…
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 trigger…
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…
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, le…
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: Y…
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, che…
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 (–) t…
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…