DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

0
votes
0answers
12 views

Sybase: Delete foreign key IF exists fails

I've a database, on which we will have to delete of tables/foreign keys/view. We did deleted all the required element in PowerDesigner, then we did the "Apply model change to the database". We ...
1
vote
2answers
38 views

Looking for application which will provide to determine project database growth and cost per day or month

I am using SQL Server 2008 for product development Currently application is live with 20 users now if I want to check how my database is growing and how much it will grow in month or year projection ...
0
votes
0answers
14 views

Passing DataTable using SqlBulkCopy

Should I convert the DataType of DataColumns in to pass DataTable using SqlBulkCopy? This are the fields in my sql server database
0
votes
0answers
70 views

Need your help in query tuning

I read ​one article which shows how to use different aggregations effectively. http://sqlblog.com/blogs/paul_white/archive/2011/12/04/is-distinct-aggregation-still-considered-harmful.aspx I am ...
2
votes
1answer
23 views

Querying on multiple joined rows

I have a table called visits that is structured like so: +----+--------------+-------------+ | id | name | visitor_id | |----+--------------+-------------+ | 1 | event1 | 1 ...
1
vote
1answer
41 views

How to get the sum of filtered fields in SQL

How can I filtered values in SQL? When I execute this query, I'm getting the result below. SELECT Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription, InvoiceItems_TBL.StoreName, ...
0
votes
0answers
13 views

JDBC - accessing a query, then querying that query

Sorry if the way I phrased that is confusing. I'm writing a program that acts kind of like a filter - it retrieves an answer to a query, counts the rows, then I want to do a slightly more narrow ...
0
votes
0answers
24 views

How to copy all database users from one database to another? - SQL Server 2008 R2

I am making a database from a backup file (I'll call this db 1) and I need to duplicate the users of one database (and this db 2) to the newly restored database. The first step I know must be done is ...
0
votes
1answer
52 views

SQL Server : query code

I am trying to write a T-SQL query for SQL Server to pull some information from all the databases. I will then run this nightly and email the report to who it needs to go to. I have two separate ...
1
vote
0answers
42 views

Select last row for a certain group

I have two tables: Table users columns | type | notes --------|--------|------- id | bigint | PK ... | ... | ... Table events columns | type | notes ...
1
vote
2answers
60 views

Different way to do a LIKE WHERE condition? [on hold]

I have a query which works fine: SELECT month_date= month(SendTime) ,year_date =year(sendtime) ,MessageType ,COUNT(MessageType) AS MESSAGE_COUNT FROM Message WHERE ...
2
votes
3answers
33 views

Calculating overlap between groups

I have a table with two columns of interest, item_id and bucket_id. There are a fixed number of values for bucket_id and I'm okay with listing them out if I need to. Each item_id can appear multiple ...
1
vote
0answers
20 views

Get n data based on the same day (timestamp) [migrated]

I have this table "sensor" which has these fields : id (int,PK) timestamp (timestamp) latitude (varchar) longitude (varchar) data (float) As in one query I want to get 5 rows that were on the ...
1
vote
1answer
50 views

FROM keyword not found where expected (Oracle) ORA-00923

I'm getting the error in the following query: SELECT DISTINCT UNIQUE_ID as uid, CONFIDENCE_IS_SAME FROM ( (SELECT UNIQUE_ID, CONFIDENCE_IS_SAME, FIRST_NAME, LAST_NAME, POSTAL_CODE FROM ...
2
votes
1answer
46 views

MySQL Performance: conditionnal left outer join vs where only

I have three tables: FORUM ( id PRIMARY KEY, public BOOLEAN ) MESSAGE ( id PRIMARY KEY, content VARCHAR(255), user_id NULL FOREIGNKEY user.id, forum_id FOREIGNKEY forum.id) USER( id PRIMARY KEY, ...
4
votes
1answer
122 views

Slow storage writes: how many milliseconds exactly? SQL Server 2008 R2

We're running SQL Server 2008 R2. I'm using SPBlitz and it's saying that I have slow storage writes on Drive H. Blitz defines these as "averaging over 20ms." But of course, someone asked "How much ...
1
vote
1answer
10 views

JDBC prepared statement - make a token null

So, I have an insert statement in a Java program I'm writing. Under some conditions, I want it to insert some values as null. However, before I can execute the statement, I have to set all of the ...
2
votes
1answer
23 views

Query to get number of items from different table reference in another table [closed]

I have 5 tables which are - Project(PK Project_ID), - Bottles (PK Bottle_ID, FK Project_ID), - Plastics (PK Plastic_ID, FK Project_ID), - Glasses (PK Glass_ID,FK Project_ID) and - Cups (PK ...
0
votes
0answers
25 views

Query Performance BIT column vs New Table

Would it be more efficient to flag 50 rows with BIT 1 / 0 in a table with 2000 rows OR create a new table to hold only 50 PK values from the previous table The query on this would just be a WHERE ...
1
vote
2answers
73 views

Is there's another function to optimize this query in MySQL?

I want to use a function that returns for me the same result in one line is that's possible?? MIN( CASE WHEN (data.pro LIKE '1 %' ) THEN 1 WHEN (data.pro LIKE '2 %' ) THEN 2 ...
0
votes
0answers
29 views

Retrieving a record for the last date received and returning the value of another column in that row

I have the following query which is returning me total number of expected records : select a.New_FirstName + ' ' + a.New_LastName as 'Account Name', a.New_MPRNNumber,MAX(b.New_readdate) as 'Last SC ...
0
votes
1answer
23 views

Postgres INNER JOIN same table failing on CartoDB

I have a table with data which looks like this +----+------+-------+ | cp | year | count | +----+------+-------+ | 1 | 2000 | 10000 | | 1 | 2001 | 9000 | | 1 | 2002 | 9500 | | 2 | 2000 | 8000 ...
6
votes
1answer
63 views

SSDT Schema Compare fails when comparing my DB Project to my Azure Server

I have a SQL Database project that I have built our enterprise DB on. It has deployed several times on internal and AWS hosted SQL servers using SSDT's Schema Compare tool. The problem when I post ...
0
votes
1answer
13 views

Unable to delete a SQL Server 2012 publication

I've set up a transactional replication for two databases, let's call them A & B. Replication for the database A worked just fine and I was able to delete it afterwards without any issues. ...
7
votes
3answers
669 views

How to insert a large amount of records to the database in a short time?

I have to insert a bunch of records(500,000) to the database at once, using the fastest way. I've tried inserting 1,700 records at once but it took twenty minutes!! I am using SQL-SERVER, and C#. ...
0
votes
3answers
54 views

Query for total downtime of site using datestamps and status columns

what want to find the total downtime for a site. I'm thinking I can subtract the 'down' DateStamp from the following 'up' DateStamp. then add the totals together for all downtime. I want to get a ...
-1
votes
1answer
66 views

CTE Expression not working

I am trying to get the employee hierarchy as output with empLevel indicating the reporting layer. However, my query returns only one row i.e. The CEO of the company while it's supposed to return all ...
0
votes
1answer
30 views

trouble creating a stored procedure to print out values for each day of week

I am having difficulty in understanding how to get this working correctly. I am trying to have create a stored procedure that is run automatically on a sunday and will get the data for the previous ...
1
vote
1answer
29 views

SQL Server Query For Getting data in Specific Format

We have two tables. One is PriceGroupTable and other is PricesTable. There is a one-to-many relationship between them. Please refer to the attached pic for more details. I am looking for a SQL ...
0
votes
0answers
45 views

PostgreSQL query taking a long time

I have a query that is taking hours to run. There is a large table to parse, is there any way to optimise this? with VehData as ( SELECT * from dblink('host=databasesrv port=1234 ...
1
vote
1answer
35 views

Count number of duplicate rows, sort from most to least (statistic) [closed]

I'm trying to make statistic of top-10 most visited URL. I have one simple table. For each URL visit, one row added with that URL as content. Most visited URL has the most number of duplicate. I've ...
1
vote
1answer
14 views

Join tables up together to get different counts of account type per postcode are

I am trying to deterine the amount of account types per postcode range. I have the following code that works but I would like help in refactoring it as only 1 line of code changing between each ...
3
votes
4answers
83 views

Pairwise intersections

I have a table with two columns, let's say FirstName and LastName. I need to get another table, which for every pair of FirstName's from the first one contains a count of the common LastName's. Is ...
1
vote
1answer
67 views

Dealing with single quotes in a dynamic query [closed]

I'm building a query as a string in my application layer to be executed over jdbc. The string is constructed as follows: "SELECT COUNT(DISTINCT ROOT_ID) as rows1 FROM DANIEL.UNIQUE_PHYSICIAN " ...
1
vote
1answer
19 views

Union of data from all tables with name containing a substring

I have 500+ tables all containing a column called COLUMNDATA. The table names are like: TABLE_FIRST, TABLE_SECOND, ... ,TABLE_FIVE_HUNDREDTH. What I am trying to get is a column containing all the ...
0
votes
1answer
20 views

Error with my trigger. What should I do?

This trigger fails when it's called: create or replace TRIGGER UNIQUE_PHYSICIAN_TRG BEFORE INSERT ON UNIQUE_PHYSICIAN FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> ...
-1
votes
3answers
50 views

Query to return zero entries with COUNT

I have a problem with one task. I need to display various information through two diferent queries.(or one if it could be possible) The table has these fields. Location is set to x ...
1
vote
2answers
19 views

How to populate another column with a sequence value generated for an identity column?

I have a table that has a column that is determined by a sequence using the identity column thing, so after a record is inserted (or during, I'm not sure how it works behind the scenes, only that it ...
4
votes
1answer
132 views

Moving Transaction Data To Another Database For Reporting Purpose

We got a requirement from our developer group, to do the followings: Frequently (every 30mins) move the transaction data out of live database to another database Secondary database will be utilize ...
1
vote
0answers
18 views

Publishing Database Changes With SSDT that include Adding a New Column [closed]

Similar to Publishing database changes with SSDT that include altering data types of columns But I have the issue when I'm adding a new column to my database. I tried publishing incrementally by ...
0
votes
1answer
41 views

Query trans log or tempdb?

New DBA, all of our databases are SQL 2005. All of our servers run in Simple recovery mode. I work for a school district, where we report student attendance to the state. I run a query to fix ...
4
votes
1answer
28 views

Doing a select based on a count(), a date range, and string match

I'm going to do my best here. I Have a list of citations that were written and a and some information about those citations. I need to select the citations that have been voided in a given date range ...
1
vote
0answers
29 views

Update all foreign keys to a different primary key

In a MySQL database, I have a table full of business information. Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys. ...
1
vote
1answer
38 views

generate new column from another table column, treating nulls are unique

I have a table that looks like the following: |device_id | login_id | ----------------------- | 1 | NULL | | 2 | A | | 3 | A | | 4 ...
1
vote
4answers
75 views

How to select first row from a join that returns multple rows on the primary key

This is related to this question: Joining multiple tables results in duplicate rows I have two tables that I am joining. They share a key. The person table has one name per primary key but the ...
2
votes
1answer
62 views

Wrong results from joining two CTEs

I've had some help to create a query to solve a problem I had and the solution seemed to be to create two CTE's and then join and query them. If I run the two CTE's as single queries each table ...
2
votes
1answer
42 views

Filtering by minimum value per group problem

I have a select statement, which when simplified produces the following: +------------+----------+-----+ | Group | Name | ID | | vegetables | potatoes | 1 | | vegetables | carrots | 6 ...
0
votes
2answers
48 views

Can I Show Multiple “WHERE” Filtered Results In A Single Query/Output Table?

I am using Postgres 9.2.4 on Red Hat 6. Tables --------------------------------------- | Table 1 | --------------------------------------- | WorkflowID | DataSet | ...
1
vote
1answer
43 views

Combining data from 3 tables depending values stored in 1 table

I'm trying to make a SQL query to return values potentially from 3 tables. This will combine all booking information for a booking which can span over multiple days with multiple guests and a single ...
0
votes
1answer
69 views

Cross Join and Left Join within a query

I have query which has a cross join and also a left join which produces an error Unknown column 'a.hour' in 'on clause' simplified version of query is like this SELECT b.*,a.hour,c.datetime FROM ...