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

learn more… | top users | synonyms (1)

0
votes
0answers
153 views

Reviewing ways to generate monthly tables using PL/SQL automatically

I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL. The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic ...
1
vote
1answer
43 views

Is there a way to get disc space remaining using an SQL query on an Oracle database?

We experienced a problem recently where the disc space ran low and some queries started giving unpredictable results. The root cause was that we'd take too many restore points and filled up the disc. ...
2
votes
1answer
153 views

SQL Server 2012 - Dropping Snapshots takes hours

We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL ...
2
votes
1answer
51 views

Function to convert first names to a specific format

I'm trying to figure out a way to show a name properly. Let's say I have 2 variables: @Firstname = 'Marc-Oliver' @Lastname = 'Saint-Onge' I would like to have name shown as M.-O. St.Onge Or at ...
0
votes
2answers
59 views

SQL Server choose at random from giving data

My problem is that I don't know how to choose the random data from a giving set. For example: I have a Node column which need to produce different inserts but only from these numbers: 16, 17, 18, 22, ...
0
votes
1answer
82 views

Different runtimes using parameter vs. constant

A strange thing is happening to me during execution a specific query. I have this query: DECLARE @P0 as varchar(4000) DECLARE @P1 as varchar(4000) SET @P0 ='P0' SET @P1 ='2015-02-21 00:00:00' ...
0
votes
2answers
330 views

how to identify table name using a column name if we are not aware about the table name

how to identify table name using a column name if we are not aware about the table name Imagine if you have 10000 tables in Database I am aware about the column name , but i dont know the exact ...
0
votes
0answers
34 views

How to fetch data on a single column from a table having composite key [duplicate]

I am working on a messaging application, where the users can have a group conversation with multiple recipients. So in my database, I have a table called user_message where user_id and message_id is ...
-2
votes
1answer
1k views

Arithmetic overflow error for type varchar [closed]

This is my query: insert into [Consolidated_LoB].[dbo].[consold] ([shp_to_pstl_cd] ,[LoB] ,[catlg_nbr] ,[shpd_qty]) select [POSTAL_CODE] ,[LoB] ,[PART] ...
3
votes
2answers
401 views

Searching multiple tables in SQL

This question will involve some C# code but I hope that is fine, in the end it is the SQL that is the issue. (To see short question look at end of post) So I have a site, where you can add a site in ...
0
votes
2answers
59 views

Searching a database safely using SELECT

I have a site that allows users to search the database for several items. However I am not that good with SQL and I have read that allowing user to type in boxes that then use "SELECT-statements" is ...
-2
votes
1answer
64 views

What are the ways to handle temp table with more than 9000 rows? [closed]

I am creating a temp table in my stored procedure which has more than 9000 rows and I read some articles saying that its bad practice to use temp table with more than 9000 rows as it uses temb db for ...
0
votes
1answer
342 views

Oracle update join tables

I want to update my db records, which are related one to many. Please consider the following example : A person can have many contacts Stating above implies that contact table has a person_id ...
2
votes
2answers
163 views

Normalising Customers and Employers with a people table

At what normalisation stage is it recommend to split say Customers and Employees into a people table and then link to the unique attributes of Customers and Employees. I cant seem to find the ...
0
votes
1answer
95 views

Best query to exclude existing friends from query

I have a User table / id_user / username / .. friends table (pair AB and BA): / friend_of / friend_to and a friend_request table (used for pending friend requests) / id_req/ sent_from / sent_to ...
0
votes
0answers
40 views

How to fix slow performed query with joins while pagination?

I've following tables with respective records. 1) wi_individual_g with 85K records 2) wi_organization with 550 records 3) wi_program with 2 records I've used following query for creating ...
5
votes
1answer
727 views

System.Data.Entity.Infrastructure.CommitFailedException: C# Multithreading & SQL Server 2012

We have a C# multi threaded (100 threads) program which reads the records from the DB and each thread picks up one record (one Entity Framework connection per thread) and update the a single DB table. ...
0
votes
1answer
20 views

How do I write this MySQL query?

I have a table with a column of numbers that are consecutive but not concurrent. So 91 may be followed by 92 or it may be followed by 96, for example) with intervening numbers not in the column. I ...
0
votes
0answers
47 views

Database Hangs while trying to run sql

I have a challenge with SQL Server. We are running SQL with an inhouse developed application. Lately we have been having a challenge where the DB hangs and users are unable to access the application. ...
0
votes
1answer
1k views

How to create a stored procedure with columns from three different tables in SQL Server 2008?

I'm a fresher and I would like to get some suggestions on how to create a stored procedure in which I have to select columns from three different tables. 2 columns customername and customercode from ...
0
votes
2answers
71 views

WHERE clause converting INT to DATETIME

Looking for advice on how to match two differing column types: I have two tables: RT-A and DA-B. Under both tables there is a column named DeliveryDate, but the individual tables use differing ...
1
vote
1answer
49 views

Finding the current prices for n Fuelstations at a certain point in time

I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database. CREATE TABLE public.de_tt_priceinfo ( id integer NOT NULL DEFAULT ...
0
votes
0answers
35 views

Postgresql : Table historisation

I would like to do a script that gather data from an API everyday. (8000 lines/days) So for each object would like a object1 table for the data of the day. And a object1_hist for archived data. ...
1
vote
1answer
182 views

SSIS Transaction Problem

I am trying to use SQL transaction with my SSIS package so if any error comes changes are rollback. I am using an "Sequence Container" in Control Flow for this and setting its "Transaction Option" to ...
0
votes
1answer
432 views

What is the correct SQL for using where ID in List and the items are with AND operator

I need a SQL Statement to use IN operator within the items with "And" connected. SELECT * from tbl_Users WHERE id in [1,2,3] is equivalent to: SELECT * from tbl_Users WHERE (id=1) or (id = 2) or ...
0
votes
0answers
42 views

how to optimize a query with a lot of join

I have the following query: SELECT DISTINCT s0_.id AS id0, s0_.created AS created1, s0_.style AS style2, s0_.styleTitle AS styleTitle3, s0_.styleTitleDescription AS ...
0
votes
1answer
29 views

call a file contains a list of table

I'm using Ingres Database and I've this procedure that create a text file with a list of tables. How i can call the file into the where condition? select request_num,table_name,temp_table from tables ...
0
votes
2answers
734 views

SSIS import data or insert data if no match

I have a situation in which I need to import data from one table to another. The challenge here is in tables I have some columns which are references to other table values (kind of foreign key). I ...
0
votes
1answer
100 views

set unique key checks 0 in mydql

In a table i have unique value check for the combination of box_number,price,start_val,end_val so there can not be a two box number with same number which have same price , start_val and end_val.. ...
0
votes
1answer
44 views

Optimizing a MySql query

Here's the query: set @_YEAR = 2014; set @_MONTH = 1; set @_PRODUCTS = '53'; set @_START = date(@_YEAR * 10000 + 1 * 100 + 1); set @_END = date_add(date(@_YEAR * 10000 + 1 * 100 + 1), interval 1 ...
0
votes
1answer
64 views

How to remove the duplicate records from primary filegroup after partitioning?

Basically, our database is created based on primary filegroup alone. Now, I have done partitioning for a large table which was approx 25 GB. The partitioning was done on secondary filegroups which ...
-1
votes
1answer
145 views

Query optimization and takes less execution time

CASE-1: SELECT * FROM A JOIN B ON B.CustomerID=A.CustomerID CASE-2: SELECT * FROM A JOIN (SELECT * FROM B) C ON C.CustomerID =A.CustomerID Which CASE is efficient ?
0
votes
0answers
48 views

SQL Server 2012 Downgraded from Enterprise to Standard. Permission errors

We downgraded our SQL Server from Enterprise to Standard. Now on 3 of my 5 non system databases a lot of users no longer have effective permissions. My admin account is in the same boat. I have ...
2
votes
1answer
305 views

Select a range of values

I have the following data in rows: 1 2 3 6 7 15 16 I want to write a SELECT statement to return contiguous ranges. Given the sample data above, the output would be: 1,2,3 6,7 15,16
0
votes
2answers
25 views

Added a unique index, but cannot find it in TABLE_CONSTRAINTS

I'm running the following command in my DB: CREATE UNIQUE INDEX [UK_NAME1] ON [TABLE1] ([COLUMN1] ASC); Now, I'm running the following query: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ...
1
vote
1answer
141 views

Join and count boolean columns by logical OR

There are two tables: user_table and user_schedule. Each user has four schedule rows (one for each 6 hour period per day) with one column for every workday. The desired result is to count the number ...
2
votes
3answers
3k views

Find duplicate records across one-to-many relationship tables

Let's say I have two tables that have a one-to-many relationship: People and Attributes. I'm trying to find duplicates based on the first_name, last_name, and all attributes must match exactly. ...
1
vote
1answer
118 views

PostgreSQL isn't using indices in FULL OUTER JOIN

I have these tables I'd like to do a FULL OUTER JOIN between. They both have an index on (ts_minute, user_id, campaign_id, host_id) (which matches the join condition). In the following query, if I ...
2
votes
1answer
312 views

INSERT multiple values via join

I have three tables, representing two entities and a connection relationship. Each of the entity relations has an artificial surrogate key used for foreign key constraints in place of natural keys. ...
1
vote
2answers
613 views

Idea for manking tables for a pharmacy

OK I'm not into Database management, but I got this problem, also not sure if I'm posting it at the right place or not. So, my Dad works in a pharmacy and all the work there is on paper and is done ...
-1
votes
1answer
59 views

System.Data.SqlClient.SqlException when try to run simply queries

I've installed SQL Server 2012 Express. I'm doing some test with this and Server 2008 Express. With 2008, all is ok. But with the 2012, I get some troubles. I'm using it from my c# app, with code ...
1
vote
1answer
1k views

SELECT from Function that return CURSOR

I would like to select from a function that return a SYS_REFCURSOR Type value. for example : CREATE OR REPLACE FUNCTION my_funtion ( my_param IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
0
votes
1answer
70 views

How can a primary key be inserted as foreign key?

Hi I am building an order intake form that will have information submitted belonging to 3 tables. The order table will have the primary key used for the property and customer table. How can I ...
0
votes
2answers
152 views

Process staging table by getting unique rows only (incremential)

I had simple problem with etl on staging table that was answered here: Process staging table by getting unique rows only Now after apllying solution I spotted that will be unusable in production ...
-2
votes
1answer
69 views

Process staging table by getting unique rows only [closed]

I have several staging tables where I bulk import data with duplicates and sometimes I upload historical data mixed with real-time data, just importing everything without caring if it is already in ...
1
vote
1answer
75 views

how to get a sorted result from mysql query?

I am trying to get a sorted result from a MySQL query and i dont have any idea of making the query. I have a example Mysql table as follows : id | cat_type 1 | free 2 | free ...
1
vote
1answer
312 views

Reuse SELECT query by adding results in array?

I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first SELECT query to an array as well, so I can ...
0
votes
1answer
401 views

How to delete the old backup differencial files?

I'm (trying) managing a large number of sql server instances among a number of machinces running SQL Server 2005 and 2008. Almost all the DB are in Simple Recovery Model. I'm doing full backups every ...
1
vote
1answer
40 views

Instant File Initialization on Backup drive

I know IFI (Instant File Initialization) is good to have enabled on SQL Servers. How about backup archive locations? We backup our DBs to a local SSD drive (so it backs up quickly), then throughout ...
-1
votes
2answers
76 views

SQL Server counting rows [closed]

I have an imported database and I'm running a query. I want to count the rows found and output it. Here is my query: select mualbum.album, mutitle.title, mutitle.comp from MUALBUM inner ...