Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

0
votes
0answers
30 views

Count responses based on tale structure given below

Table:Answer_Master id_answer answer id_question 1 Yes 1 2 No 1 3 Yes ...
0
votes
0answers
343 views

Run a specific SQL Server job step from a stored procedure

I have a job with 5 steps that are scheduled to run after each other. I want to have a stored procedure where a user can execute the stored procedure and give the step number as a parameter. Only ...
5
votes
5answers
28k views

Tempdb is getting full very quickly in microsoft sql server 2008

I am developing a log analyzer tool and After queering it and generating reports Tempdb is getting full very quickly . Is there any solution to clean the tempdb file in microsoft sql server 2008 on ...
12
votes
1answer
15k views

Performance implications of using OPENQUERY in a view

Please see this question on stackoverflow : I am using an EasySoft ODBC driver to link a SQL Server 2008 R2 Express instance to Interbase and I am having some difficulty with getting metadata ...
0
votes
2answers
593 views

update column based on sum of data from another table

I have two tables : Table1 (col1, col2, col3, col4, val1, status) Table2 (col1, col2, col3, col4, val2) For Table1 and Table2, The columns (col1, col2, col3, col4) are the composite primary key. ...
0
votes
0answers
327 views

SQL Query to get the Sales Qty of the Item and the its Returned Quantity for past 6 Months

I want retrieve the Sales Qty and return qty of the Items in the following format for the past six months. From two different tables,I TxnInvoice Table :Invoice header TxnInvoiceLine Table :Invoice ...
0
votes
0answers
43 views

SQL Database query working really slowly

I have a database with two columns: 1)Word 2) Times Used When the user enters a word it find the combination of words already typed (ex: combo = hello world) and updates the database using a ...
1
vote
0answers
36 views

Enforce referential integrity for FriendRequest table that has UserA and UserB

I have a table called User that has UserName as a primary key. I want to establish a relationship with enforced referential integrity between User and FriendRequest. FriendRequest has the following: ...
0
votes
1answer
258 views

Delete duplicate records by column 1 but having differ values in column 2 and column 3 [closed]

I have a table like: SSN FName LName 1 a b 1 a b 1 b b -------------> to delete 2 c d 2 c d as above example I want to delete such records where SSN has different values in ...
0
votes
2answers
32 views

Report individual changes to data

I need to produce a daily extract on the individual changes to a dataset. So, there are 14 tables containing product and customer information and the extract must only show records from each table ...
1
vote
1answer
1k views

PostgreSQL, UPDATE CASE statement uses huge amount of space

I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well. I am using ...
1
vote
1answer
197 views

MySQL created_by or updated_last-by [closed]

I've been giving the task of taking SQL DDL from data modeler and to transcribe it into mySQL for an internship. The only thing I cannot figure out is how to take Created_By VARCHAR2(30) DEFAULT ...
2
votes
1answer
410 views

Will MyISAM prevent multiple users inserting data at the same time?

I am using a MyISAM database to store user data because I am using full text searching. I have looked in to options like Sphinx but cannot install this on my shared hosting plan which uses MySQL ...
3
votes
1answer
14k views

Column name or number of supplied values does not match table definition error

I have a table, with two foreign keys defined on two columns of that table. The problem is that, when I insert the values from SQL Server, I get an error: Column name or number of supplied values ...
0
votes
3answers
193 views

How can I speed up an sql query that round trips between two tables

These tables are not that big, but the time taken to process this query is long. What can I do with the following queries to speed up the process taken to return results? $msc=microtime(true); ...
5
votes
2answers
10k views

Find which session is holding which temporary table

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session ...
0
votes
2answers
615 views

Excel - How can I use a SQL query to retrieve data on a database sever?

I have connected to a SQL server in Excel . I want to be able to import data from a table located on the server. However this table is too big to import into Excel, I'm talking millions of rows. I ...
3
votes
1answer
200 views

Why is casting a string to DATETIME not accurate to the ms? [closed]

Why does SELECT CAST('2014-11-28 15:06:02.165' AS DATETIME) return: 2014-11-28 15:06:02.167
-1
votes
1answer
60 views

Creating Triggers to insert into a table where the table is altered by other application

I have a database in my local machine which is actually updated by an online application. I want to create triggers for a particular table ( say table1) such that it inserts values (From Table1) ...
4
votes
1answer
1k views

One foreign key for two columns

I have two tables, Results and Teams Teams has the following columns: team_id team_name Results has the following columns: match_id home_team (foreign key references teams [team_id]) away_team ...
2
votes
1answer
82 views

How can I speed up an sql query that round trips between two tables

I first posted this question on stackoverflow, and now I see this site, seems more relevant. These tables are not that big, but the time taken to process this query is long. What can I do with the ...
3
votes
1answer
360 views

Help with indexing narrow table with many millions of rows

I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ ...
-1
votes
1answer
48 views

MySQL Multi Count [closed]

How can i get this result? ╔═════════╦═══════╦═══════╗ ║ EventID ║ User1 ║ User2 ║ ╠═════════╬═══════╬═══════╣ ║ 1 ║ 1 ║ 2 ║ ║ 2 ║ 2 ║ 3 ║ ║ 3 ║ 3 ║ 1 ║ ...
1
vote
1answer
124 views

I need help migrating data through databases

I have a web page with some data about the users. Recently, I made a complete change and start developing a new portal and of course a new DB structure. My question : What is the best way to migrate ...
2
votes
2answers
3k views

Find nearest values from MySQL Table

I want to fetch nearest rates for a given value from below table. mysql> select * from rates; +------+----------+----------+ | ID | Type | Rate | +------+---------------------+ | 1 | ...
2
votes
1answer
586 views

Mysql update adding subquery field value to field

I want to update a field adding it the value of a subquery field. I'm trying this: UPDATE table a SET a.field=(a.field+b.subfield) INNER JOIN (SELECT subid, subfield FROM subtable WHERE ..... ) AS ...
1
vote
1answer
3k views

need to replace the blank value as 0

need to replace the blank column into 0 for example the table is i have tried this query to bring the output but it is taking more number of time because i have put this table in to procedure ...
1
vote
0answers
50 views

Veryfing integrity of custom order column with pure SQL

I have two tables, one stores structure (using so called parent field), the other entities that can be embeded into that structure. As certain level of structure can contain both entities and deeper ...
5
votes
4answers
1k views

optimizing a compare query

Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
4
votes
2answers
9k views

Reduce Clustered Index seek cost SQL Server 2008 R2

I am running a query and it has records in the hundreds of thousands, it takes more than 20 mins to fetch data. After running the execution plan i noticed that clustered index seek cost can be be the ...
4
votes
1answer
576 views

Get list of characters in column

I have a database table (UTF-8) with a lot of names in different languages with lots of special characters. In another script I use these names and one part is to replace the special characters. To ...
0
votes
1answer
429 views

SQL Server - Error 3202 during Restore

While attempting to restore a 4 TB MDF/data database with a 1.8 TB LDF/log file, I'm receiving the following error: Write on '???' failed: 1117 (The request could not be performed because of an I/O ...
4
votes
4answers
15k views

Execute permission denied on object sp_start_job

I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified): ALTER PROCEDURE ...
0
votes
0answers
32 views

preventing FullTextSearch from using wildcards

I am using FTS in a sql2008 sql server and had a user complain about some searches breaking. after root cause analysis we found only searches like U.S.A. fail because FTS tries to run wildcard matches ...
6
votes
2answers
541 views

Is this update, select combination query thread safe?

I have a table called seats, which has a schema like this id,taken For each user, I take a random untaken id and assign to that user, here for simplicity say, I make taken =1. The query I am ...
0
votes
1answer
46 views

Move/Copy unique entries only from TableA to TableB, if it's not in the destination table

I'm required to move every unique entry from TableA to TableB, it the unique entry from TableA is not already in TableB. Both tables look the same, they don't have any keys, but the combination of to ...
0
votes
1answer
430 views

self referencing tables with SQL and entity framework

I have many tables in a database and the tables are nearly identical. There are limitations currently on what objects reference other objects. For example I have company,contact and project. Contact ...
4
votes
2answers
4k views

Database Implementations of ORDER BY in a Subquery

I am using an application (MapServer - http://mapserver.org/) that wraps SQL statements, so that the ORDER BY statement is in the inner query. E.g. SELECT * FROM ( SELECT ID, GEOM, Name ...
8
votes
1answer
41k views

Best practice to shrink Tempdb in a production environment

What is best practice to use when shrinking Temporary db in SQL Server 2008? Is it risky to use the following? use tempdb GO DBCC FREEPROCCACHE -- clean cache DBCC DROPCLEANBUFFERS -- clean buffers ...
1
vote
2answers
536 views

Delete duplicates with specific columns as identifier

I'm required to delete all duplicate entries of a table. The duplicates are identified by two non-key values num (int) and com (varbinary(512)).. My amateur-ish SQLfoo lead me to this non-functioning ...
0
votes
1answer
475 views

Select all results from two independent tables

I have two tables: Comments -------- id comment_content time_stamp Activities ----------- id activity_content time_stamp And I would like to combine the results from both, ordered by their ...
1
vote
1answer
125 views

What is my database name?

I'm trying to connect to a microsoft SQL database but I dont know its name. I suspect it will be something of the form servername\databasename It would be good if I could just use SELECT ...
0
votes
2answers
222 views

SQL Query: Selecting a person who is a member of all existing groups

I'm tinkering around with my very first SQL queries and am completely baffled by a problem I encountered. Let's say I have the following database: PERSON(PID, Name) MEMBER(PID, GroupID) How would ...
1
vote
1answer
359 views

How to restrict access to current user record and its rights in postgres

Postgres 9 database contains additional information about postgres users: create table public.user ( user char(10) primary key, -- CURRENT_USER in postgres name char(30) ); and user roles; ...
0
votes
2answers
90 views

Summarizing Pay Checks Based on Employee's Job Code

What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job ...
1
vote
1answer
82 views

How to combine two queries? [closed]

I can't explain this very well except by spelling it out. I start a query on ID, and from there I can get TITLE, DATE, and REGNUMBER. The problem is I also want to get 'where,' which is also stored ...
0
votes
3answers
930 views

SQL MAX function

Suppose I have a table with attributes Name, Age as follows: MyTable (Name, Age) Now, to get the oldest age I can write the following query: SELECT MAX(Age) AS "Oldest Age" FROM MyTable; ...
0
votes
0answers
63 views

Continuously copying an SQLite table

I have a SQLite database associated with an online application (Skype) on my computer, which is often changed by that application when I am using Internet. These are the columns of the database: ...
4
votes
1answer
1k views

SQL Server 2008 R2 Restore constantly hanging

I have a SQL Server 2008 R2 SP2 test server that I have to refresh the databases on fairly often, but every time I have to do a restore on, it will hang on me. I use a custom script to check the ...
0
votes
1answer
256 views

Database Normal Form (financial statement)

I would like some advice on how to design the database for financial statement data which objectively addresses the tradeoffs between usability, performance, maintenance and adherence to normal form. ...