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
2answers
77 views

How can i update my database from another database using temporary tables?

I want to update tables of my database using another big database every day with using temporary tables and without returning result. How can i update it? This my temporary table : use data_pro; ...
0
votes
4answers
55 views

Insert from Another Table + Removing Duplicate Entries in Postgres

I'm trying to insert data from one PostgreSQL table to another while removing duplicate entries but I'm having some trouble with the code. I have two tables I'm working with: mytesttable, which ...
7
votes
3answers
536 views

Use 'default value' in table when NULL

I am building a config table and want one row to be the default value. For the example, I have a list table of all my databases and a msg table of my return values. In the msg table, I have one row ...
2
votes
2answers
51 views

Query Row Count for All Tables

I need help creating an MSSQL query to pass in every table name to the query below, which returns the number of rows. This is how I get the row count for a single table: SELECT Total_Rows = ...
1
vote
1answer
23 views

To find out the sum total of GRNQty and GRNValue of particular item from column 1

Query 1 # SELECT spi.Item,spi.ItemName,supdd.PackageID,spi.Specifics,iteb.brandname,supdd.Qty AS GRNQty,supdd.GP AS UnitPrice,(supdd.Qty * Supdd.GP) AS GRNValue FROM suppdeldetail AS supdd ...
2
votes
1answer
46 views

How to use a view to span two tables that are identical

We have table A that receives data from our shop floor and we currently have it so that the data is moved from table A to table B after 5 years. I want to streamline this but need help creating a ...
2
votes
0answers
64 views

Weekly SQL database backup failing

I have a weekly SQL database backup job that is failing. If I look at the end of the history of the job, I see the following messages: Executing query "USE [msdb] ".: 2% complete End Progress ...
4
votes
0answers
98 views

Why is query thousands of times slower with DESC?

I have a PostgreSQL database (AWS RDS) and ran the following queries in psql: livenet=> EXPLAIN ANALYZE SELECT * FROM outputs ORDER BY height LIMIT 10; Limit (cost=0.57..36.82 rows=10 ...
1
vote
2answers
38 views

Join Two queries with different declarations

I need to join two different queries to get some columns from both. The first one has a CTE. Query1: WITH StatusChanges AS ( SELECT HistoryID,PartID,Status, DateAdded, ...
0
votes
1answer
40 views

How to make sum of minutes into (Hours : Minutes) format

How to add (sum) two fields (Data Type=Money) into hours:Minutes format Query: SELECT ( SELECT ISNULL(SUM(action.service_time), 0) AS Expr1 FROM dbo.act_reg AS action INNER JOIN dbo.act_type ...
2
votes
0answers
54 views

Select number of records until the sum is less than 'n' - Access SQL

I am working in Microsoft Access. My requirement is: user will give any percentage value and I have to find the number of IDs which form the percentage of the 'Value' column. For eg in the below ...
1
vote
1answer
48 views

To find data in Multiple tables and delete it?

I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the ...
2
votes
1answer
51 views

Query to determine start and end dates based on time overlap

Given the following data: id | user_id | started | closed | dead ------------------------------------------------------------------------------------------- 7714 ...
0
votes
1answer
18 views

SQL BCP Utility Writing to SSMS Insead of .csv File

I have a stored procedure in SSMS call sp_testInventoryIssues as follows: USE [ADMIN] GO /****** Object: StoredProcedure [dbo].[sp_testInventoryIssues] Script Date: 06/19/2015 16:02:29 ******/ ...
0
votes
0answers
25 views

How to Create a Recommended Test Planning in SQL Server

Goal: Make lots of different test for a specific stored procedure named 'sp_test' in order to review if you are retrieving the same data based on data input. Purpose: If you have made lots of ...
-1
votes
1answer
25 views

New to SQL- Delete First and Last Day of Each Month From Data Set [duplicate]

I am extremely new to SQL. I am operating on SQL Server 2012. I am working with an Inventory file that contains the Date the Inventory was taken, item #, and quantity. The first and last day of each ...
4
votes
2answers
116 views

Cummulative Sum with Reset Value

Consider the following table: ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL ---+----------+-----------+-----------+----- 1 | 1 | 1 | (null) | 3 2 | 1 | 2 | (null) ...
0
votes
2answers
52 views

Use column alias in GROUP BY

I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly? SELECT KOS_VER_ID AS ...
1
vote
1answer
54 views

Is there a way to add a column to a table without affecting performance?

How do you add a column to a table in production without affecting its performance? Or is this impossible? I'm asking about Postgres.
1
vote
5answers
89 views

TSQL insert table using view — performance issue

I have a big view joining multiple tables that in a regular basis materialists itself into a table (this step occurs for viz's tool read a table instead of a view during data source refresh): select ...
0
votes
1answer
44 views

How to select users that doesn't in the role

I have 4 roles which are Admin, Procurement, Accounting, HR roles and 3 tables which are Users, Roles and Userroles. They user can have many roles as well as the roles can have many users. the problem ...
1
vote
2answers
90 views

Why is my query returning an unexpected result?

Here are the tables I am using: parts(pid,pname,color) suppliers(sid,sname,address) catalog(sid,pid,cost) I need to find the name of suppliers that supply two different parts with the same ...
2
votes
2answers
54 views

Calculate number of consecutive years (not gapless)

I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this: Order_year, Contact 2015, 123 2014, 123 2013, 123 2011, 123 2010, 123 2015, 234 2014, ...
2
votes
3answers
52 views

What schema for different relation from two tables to another?

There are three tables instructors, meeting_points and bookings. Here are the relation I want: 1 instructor has 0, 1 or n meeting_points 1 booking has 1 meeting_point 1 meeting_point can be linked ...
0
votes
0answers
29 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
48 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
17 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
1answer
30 views

Update only some records

I search an update-command, which update me only some records (more than one row) in a table. The update Information comes from an other table. Unfortunately it updates all 3500 records instead of ...
0
votes
0answers
71 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
29 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
53 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 ...
2
votes
0answers
50 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
61 views

Different way to do a LIKE WHERE condition? [closed]

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
38 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
1answer
112 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
59 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
140 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
16 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
74 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
32 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
27 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
77 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
16 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
743 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
56 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 ...