An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

201
votes
4answers
46k views

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?
0
votes
1answer
57 views

Any way to get database data back?

So I've entered a couple hundred rows of data into a database manually and have begun implementing a Java frontend. However, I got an unexpected result and now when I go back into RazorSQL, the ...
1
vote
1answer
46 views

Detect data spikes (loops) in MySQL database and ignore these

i must create some statistics out of a WWW access log database, but i found out that there are some redirect loops after a login if the user has disabled cookies. So there are 10-1000 mostly identical ...
0
votes
0answers
19 views

How to transfer Right-to-Left text from Excel to SQL Server?

I have a collection of data in my Excel which the language is right-to-Left. My data collection is very large. I want to transfer this to my SQL Server database and insert the values into a table. ...
0
votes
0answers
33 views

MSSQL Insert takes over 5 hours, when inserting more than specific number of rows [duplicate]

I have a question connected with MS SQL. When inserting less then about 1350000 rows to the table it all takes about 2 minutes, but when number of inserted rows is bigger, then a time needed to insert ...
0
votes
2answers
2k views

Oracle query is slower the second time

I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried ...
0
votes
1answer
29 views

export and import all the permissions on schema objects within a database SQL Server 2008

Is it possible to export all the users roles and their permissions on schema objects within a database in a .sql file and import them later in an other database ?
0
votes
1answer
26 views

MySQL select data over time that matches pattern over several rows

We currently log data from our plants running remotely for diagnostic purposes on MySQL. I have been asked to come up with a typical running profile for several variables. For this, I would require ...
3
votes
1answer
44 views

Transforming multi column table to different structure table

I am a software developer who got data in the following format: TABLE [dbo].[SensorValues]( [timestamp] [datetime] NOT NULL, [Sensor1] [float] NULL, [Sensor2] [float] NULL, [Sensor3] ...
0
votes
0answers
16 views

Need separate columns that calculates percentage [duplicate]

I have the following table: NAME ------ Alex Bob Bob Tim Alex Roger I need to produce the following: Name Count Percentage ------------------------- Alex 2 33% Bob 2 33% ...
0
votes
1answer
55 views

How do I count and order by unique entries?

I have this: SELECT tblClient.[Printed Name], tblClient.[Address 1], tblClient.[Address 2], tblClient.City, tblClient.State, tblClient.Zip, tblClient.Phone, tblClient.[Email Address] FROM tblClient ...
1
vote
3answers
77 views

How to query for records based on most recent version

I have the following table structure ID, ORDERNUMBER, DATE, STATUS 1, 1, 1-1-01, proposal 2, 1, 1-2-01, proposal 3, 1, 1-3-01, active 4, 2, 1-1-02, proposal 5, 2, 1-2-02, cancelled 6, 3, 1-1-03, ...
3
votes
1answer
1k views

Error 18456 error severity 14 state 5 SQL Server 2012 SCCM2012 SP1

We had a primary site for SCCM 2012 SP1 running fine. I then installed CAS for SCCM on another server. The prerequisites required me to change the collation on the existing server. I did this and ran ...
0
votes
1answer
290 views

date wise report and order by date

I have a table where I need to get this report format like this in datewise totals. I have 3 columns which are t_reference,t_pa_number,t_pa_name,t_date_time_issued from tickets table. date 17 ...
0
votes
1answer
83 views

Recommendations for how I should create my table based on data given?

I'm asking this as I'm kinda overwhelmed by the data presented to me (I'm still fairly new in programming and SQL). Basically I need to create a database to store warehouse equipment information, and ...
0
votes
0answers
36 views

How to model relation between customers, tariffs and discout

I have following tables (apart from others, that I will omit here): Customers, Tariffs, Discounts and Products (EDIT: and Orders). Each product has its price, and is kept in appropriate column in the ...
0
votes
1answer
143 views

Nested query not recognize external table

i need to a query that show week by week the total amount of imports of any company and in addition add a ranking position depending on that amount. I've made this query but the problem is the inner ...
1
vote
0answers
78 views

Implementing a functionality for processing heavy insertion

I use PostgreSQL 9.1. The scenario is: I receive heavy insertion into Table1 (about 100 rows per sec). For each new row, I have to check the previous and next row of the source table if those are ...
0
votes
0answers
22 views

Undo SQL Update Azure DB [duplicate]

I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table. I've read around and found ApexSQL Log ...
0
votes
1answer
71 views

Handling empty fields

SELECT Name, SUM(Switch([Date] = DateAdd('d', - 7, DATE ()), NetAmount) )AS DAY1 FROM Customer GROUP BY Name My question is how to return the value 0 when the date is not present (an empty field). ...
-1
votes
2answers
86 views

Remove a character from a records enclosed between double quotes

Hi I table in which every row have data like: 0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014 What i want is to remove every comma(,) that is enclosed between double quotes " ". ...
3
votes
2answers
154 views

Enhance performance of update query

I have a query that is running against a table with 50 million rows. The phone number is stored as a CHAR(10) and an ETL process is changing the phone number if it has certain values. We are seeing ...
1
vote
1answer
21 views

Handling exceptions from inside a function

I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update ...
3
votes
1answer
60 views

How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

I have the following table: InStores +--------+---------+-----+ | ProdID | StoreID | qty | +--------+---------+-----+ | p1 | s1 | 25 | | p1 | s2 | 70 | +--------+---------+-----+ ...
1
vote
3answers
82 views

Trigger or Stored Procedure?

Let's say I have two tables, foo and bar: CREATE TABLE foo( foo_id serial NOT NULL, count integer NULL, PRIMARY KEY(foo_id) ); CREATE TABLE bar( bar_id serial NOT ...
0
votes
1answer
48 views

How to JOIN more than two tables?

Following is the schema of my Tables: Marks_Table (SubjectCode,TypeID,MarksObtd) Subjects_Table (SubjectCode, Subject) Types_Table (TypeID, Type) I want to Select the Marks_Table table with ...
1
vote
2answers
124 views

How to count the rows from 1 table but from two different column

SELECT distinct Major, COUNT(Major) AS MajorCount FROM [College Management System].[dbo].[ApplicantCourseDetails] group by Major SELECT distinct MinorI, COUNT(MinorI) AS MinorCount FROM [College ...
0
votes
2answers
49 views

Oracle GROUP BY question

I am using Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production. Yes an ancient version but the license is embedded in an application that we use and this is the Oracle version that they use. ...
0
votes
0answers
34 views

mysql select latest per group using 2 different tables along with self join

I have a query SQL Fiddle which contains 2 tables tbl_data CREATE TABLE tbl_data ( id int(10) unsigned NOT NULL, type varchar(32) NOT NULL DEFAULT '', title varchar(255) NOT NULL DEFAULT ...
2
votes
1answer
83 views

Database backup taking longer than usual to a networkshare [closed]

The SQL server does backups with this command to a network share \BackupServer\weekeklybackups" BACKUP DATABASE DBNAME TO DISK = '\\BackupServer\weekeklybackups\DBNAME.BAK' There is a 1 gig network ...
18
votes
3answers
11k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
3
votes
5answers
2k views

How different are the top DB vendor's SQL (DML-) dialects from each other?

Does someone who has worked extensibly SQL-wise with at least two top DB products (such as Oracle, SQL Server, Informix, Sybase, DB2, Teradata) know how different the DB vendor's SQL dialects are from ...
0
votes
2answers
30 views

inconsistent type after querying search_condition column in all_constraints table Oracle

I'm trying to get the not null constraints in my tables, so I'm using the following sql request : select * from all_constraints where owner='userx' and constraint_type='C' and ...
0
votes
1answer
57 views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval ...
0
votes
1answer
35 views

How to generate a sequence in mysql

Consider this table in mysql create table numbers (number int); insert into numbers values (3), (2), (9); select * from numbers; +--------+ | number | +--------+ | 3 | | 2 | | 9 | ...
0
votes
1answer
140 views

Database Mirroring and Snapshots

As part of a SQL Server deployment, it was proposed to use Database mirroring as a redundancy option as well as allowing for Reporting and MI to be detached from the Operational DB. The proposal is ...
-2
votes
0answers
30 views

Midnight Datetime difference - SQL Oracle

We have an application which reads information from SQL and Oracle (testing purposes). A datetime value is placed inside the table: Example: 05/09/2014 00:00:00 When we have this date in SQL: The ...
1
vote
1answer
100 views

Profiling parameters

I was trying to fine the parameters of a query I suspect to be causing unnecessary logical read and eventually captured it in profiler, however parameters @p3 and @p4 are not listed. Is anyone able ...
0
votes
1answer
356 views

Select from a list of values from JSON encoded data

I have saved the details of the product in JSON format. I am wondering how to select from a list of values from a "JSON_ENCODED DATA" For Example I want to select TV with screen_type as LED or LCD. ...
0
votes
1answer
108 views

Dealing with data stored as arrays in a MySQL DB

So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to ...
0
votes
2answers
22 views

mysql combine tables

I have three tables which has the same schema. all of them has 10000000+ rows and has unique data as well as duplicate keys but with a datetime column can differentiate them and get the newest row. I ...
0
votes
1answer
121 views

Need some advice creating this complex view

I need to create a view or query in MySQL to obtain if product has one or more product_detail. This are the create sentences for tables: CREATE TABLE IF NOT EXISTS `stock` ( `product` INT NOT NULL, ...
1
vote
2answers
42 views

Merits of inserting to temp table and renaming versus just adding/removing columns

If I add or drop columns from a SQL Server table I presume I get page splits or gaps. Since the size of the row has changed. When I use RedGate SQL Compare to create conversion scripts its strategy ...
0
votes
1answer
35 views

How can I transfer Mysql data to another computer and add it to an exist database

I'm trying to transfer Mysql data to another computer and add it to an exist database Here is the condition: 1. Computer A, A has a database named 'Test'; 2. Computer B, B has a database named ...
0
votes
0answers
13 views

Query from database a set of infomation and sorting them out while mataining its structure

I am currently using mysql 5.5.32. Below is my database design. It is used for storing student infomation, however each student have 61 "entries" or more(some schools might have more options), eg. ...
2
votes
1answer
41 views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
2
votes
1answer
70 views

Select the max from two max values

I need to find the airport(departure or arrival) that had the most passengers in the last year. The tables are: PASSENGER(id,name,surname) TAKES(id_passenger,id_flight) ...
2
votes
1answer
137 views

Find if Column Belongs to User Table

I want to compute the number of columns with not null constraint in my database. So I thought about querying the table sys.all_columns: select * from sys.all_columns The problem is that it ...
1
vote
2answers
4k views

How to Drop Database in Single_User Mode

How do I drop a database which shows DatabaseName (Single User) as its name? When I try to delete it, I get the following error: Alter failed for Database 'DatabaseName'. ...
0
votes
1answer
102 views

Query to get reposts from people the user is following

I have a table posts where all the posts by users are stored, the structure of this table is as follows. | post_id | post_user | post_content | post_date | The users table is as follows. | user_id ...