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

learn more… | top users | synonyms (2)

0
votes
1answer
120 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
0
votes
2answers
11 views

Merge two tables or keep separate?

A little background We are adding a new feature to our software which allows users to enter text for a specific Customer or Location. The software will then read these from the database when a ...
0
votes
1answer
14 views

What is the use of the select statement in SQL(ite) triggers?

In SQLite, triggers may contain four types of statements: insert, update, delete, and select. What is the use of the select statement, namely, how can I access the result-set of the select statement? ...
0
votes
1answer
22 views

How can I handle a huge amount of logging generated from frequent table rebuilds?

We have several tables which we "refresh" frequently by rebuilding them in a staging-table, then performing a metadata-switch to the production-table using the ALTER TABLE <source> SWITCH INTO &...
2
votes
5answers
9k views

UPDATE … FROM … syntax for iSeries for a MS SQL Server developer

When I wanted to update a table to the values from another table in SQL Server I would use following syntax: UPDATE t1 SET t1.a = t2.a FROM t1 INNER JOIN t2 ON t2.b = t1.b What would be iSeries 7 ...
0
votes
0answers
9 views

SQL pivot/transpose/row-column non-proprietary

Maybe this question has been asked already (it should be quite simple). I've Googled loads of phrases (PIVOT, ROWS to COLUMNS, TRANSPOSE &c.) and all I get is TSQL this, TSQL that and or Oracle ...
1
vote
0answers
10 views

How to remove a listener IP from a SQL Availability Group Listener by T-SQL

The title says it all. I have an availability group that was a multiple subnet, but now the environment have changed ,all replicas exist within a single subnet. How to remove the superfluous second ...
0
votes
1answer
24 views

Necessity of backing up tail log when restoring transaction log?

I have a question about whether or not it is necessary to back the tail log up when performing a restore of a database in Full Recovery mode. I've sprawled for a good while now, and have gotten ...
1
vote
3answers
56 views

SQL trigger when a row is updated question

My problem is as follows. I an creating a MySQL database for a company that whenever a row is updated on the DB i want to have a trigger that calls a cURL request to transfer the updated information ...
0
votes
1answer
60 views

Generate a column from different rows in sql

I have a table that looks like this: SKU DateOut DateIn Status 123 1/2/2015 5/2/2016 1 123 10/2/2016 18/2/2016 1 123 ...
0
votes
0answers
30 views

Paging through data that is frequently changing

When users are browsing pages of data where the data keeps changing, what is the best way to control this so the user experience is not impacted. If I browse catalog of 100 records with 10 per page, ...
1
vote
1answer
126 views

SSIS: To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher

Me and my colleague using the same version of .Net framework (4.5) and installed SSDT in visual studio 2013. He prepared a package (not so complex) and can able to execute the same from SSDT without ...
3
votes
2answers
93 views

What are options for super volatile tables?

I've inherited a very volatile table which is a map of who holds what resource in the system. At any given moment, there could be a dozen inserts/deletes/reads going against that table. However, ...
0
votes
3answers
41 views

Repository for organizing company SQL statements?

We have a few people here that use SQL and have quite a few queries we each individually use and share. I'm wondering if there's a solution to store all of these statements in a central repository ...
0
votes
1answer
30 views

Query - Pivoting 2 columns into 2 rows

I am familiar with PIVOTS but have done only simple ones of one column into a row. I have this query: DECLARE @EndDate DATE SET @EndDate ='20160728' --{?EndDate} SELECT T1.ItemCode,SUM(T0.IssuedQty)...
1
vote
1answer
22 views

correlate subquery in hive

SELECT ndo.sku ParentSKU, visitsWhenSKUWasOnline.s FROM Temp.NumberOfDaysOnline ndo JOIN ( SELECT SUM(gasessiondata.sessions) as s FROM gasessiondata WHERE gasessiondata.date >= ...
0
votes
0answers
22 views

Structure a database for a Blog

I built a database for a blog. I am a student yet, so my knowledge is limited in this area. I post this question to get a briefly description of what I do wrong, why, and how I can fix it. I'm not a ...
6
votes
1answer
27k views

Converting XML to MySQL tables

I have a large XML file in the following format and I need to convert this file into a MySQL table. Please let me know how can I accomplish this? ~~~~~~~~~~ <host starttime="1392325468" endtime="...
5
votes
2answers
27k views

Could not allocate a new page for database because of insufficient disk space

Using the SQL Server Import and Export Wizard, I get this error: Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. ...
1
vote
0answers
72 views

Performance issue with larger resultsets SQL Server

I currently have a stored procedure in SQL Server where I execute a SELECT statement multiple times based on the variables I pass to the stored procedure. The stored procedure counts how many records ...
0
votes
2answers
315 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
0
votes
1answer
39 views

Execution plan - Hash Match

SELECT dbo.Contracts.ContractID AS [Service Unit ID], dbo.Jobs.JobName, dbo.Customers.CustomerName, dbo.Contracts.ContractAutoID, dbo.Contracts.ContractType, dbo.Contracts.ContractStatus AS [...
0
votes
2answers
27 views

What is the best way to backup logs and shrink logs in SQL Server 2008 r2?

What is the best way to shrink and backup our db logs? I have seen many DBAs that do it : ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE DBCC SHRINKFILE(<log_file_name_Log>) ALTER DATABASE [...
-5
votes
0answers
41 views

oracle sql developer [on hold]

i have two main tables out of many tables. i just want to get the data on the year 2004.but it only executing without any output on it.i don't know if my script was wrong because it's just been a ...
0
votes
1answer
15 views

How to query two values from the same table from different records combined with fields from other tables?

I have a query similar to the one below which is getting data from a couple of tables and works as expected. The table eventsink.RedEvent has another column called Finished. I want to add the Finished ...
1
vote
1answer
37 views

How to use Case Statement with BCP

I am using BCP as follows, bcp "SELECT * FROM [dbo].[GpsPoint] where id in (551043, 551044, 551045)" -n This works 100%. I have omitted the rest of it because it is just server, user, password ...
0
votes
3answers
30 views

Table concatenation

I want to have a dynamic query that I can concatenate my table based on current month. I want to have a variable to my FROM and JOIN statements. DECLARE @month_number AS VARCHAR(2); DECLARE @...
1
vote
2answers
148 views

SQL Server restore database file access error

I am trying to restore a database from a file on a remote machine. The script I am using is as follows: RESTORE FILELISTONLY FROM disk = '\\server\path\to\file.dmp' RESTORE DATABASE MyDB FROM ...
0
votes
1answer
2k views

Restoring database backup file gives access error

I am running SQL server 2008 and trying to restore from a backup file. Note: the backup file and folder and read/write access to "everyone". Running the command as "master" Running RESTORE ...
1
vote
1answer
20 views

Many-many relation with specific type

Sorry if this question has already been asked, but I didn't find it performing a search. I have 2 tables (table_a and table_b), linked by a many/many relation. The relation can be of two types, ...
0
votes
1answer
26 views

Help needed in creating a SQL statement containing four joins

Please help. Given a studentID and a TagNumber i want to get the UserID related to that TagNumber. The difficult condition is that TagNumbers can be duplicate within a Users table. So we can pinpoint ...
0
votes
1answer
23 views

I am trying to run this code and I get an error at line 11 [on hold]

CREATE TABLE IF NOT EXISTS `tblproduct` ( `id` int(8) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `code` varchar(255) NOT NULL, `image` text NOT NULL, `price` double(10,2) NOT ...
1
vote
2answers
35 views

Select if colA='A1' is followed by colA='A2' [SQL]

I have a set of entries like: Id ColA 1 A1 2 A2 3 B 4 A2 5 A1 6 C 7 A1 8 A1 9 A2 10 D In pairs 1, 2 and 8, 9 the first line has A1 in colA and the following line has ...
0
votes
1answer
40 views

Which is more efficient: A join or a group by?

I've got a table A with a relation to another table B, where each A row has two rows in B. I want to extract all the values from a specific column in B for each row in A. Ignoring the difference in ...
0
votes
0answers
55 views

SQL Server Deadlock

I am running SQL Server 2012 Service Pack 1 + Non Security Update (KB2793634). I am frequently getting deadlocks occur, 99% of the time with the same 2 queries. How can i prevent this deadlock from ...
1
vote
2answers
37 views

Finding the row with the latest date for each row type

I've got an SQL database (SQLAzure V12 version running on Azure) that looks like the following: DECLARE @VehicleData TABLE ( id int PRIMARY KEY, Registration nvarchar(50) NOT NULL, [...
0
votes
2answers
37 views

How to join to the same table multiple times?

I have two tables, "hierarchy_table" and "name_table". The hierarchy table contains an object which has multiple parents and children. Each parent and child is referenced by id. | object_id | ...
0
votes
0answers
12 views

Counting entites based on status in rolling interval

Given the following table: CREATE TABLE status_log ( entity_id bigint NOT NULL, user_id bigint NOT NULL, status character varying(128), "timestamp" timestamp(6) without time zone ) I want ...
0
votes
1answer
124 views

Count for many-to-many relationship

I have the following tables: surveys properties owners_properties owners ---+------------ ---------- ------------+--------- ------ id | property_id id ...
4
votes
1answer
388 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
1answer
29 views

Join Tables of Varying Criteria in Order of Priority

I am trying to join two tables based on varying criteria in order of priority. The situation I have inherited a clinical database which has inherently bad data quality from years of poor input and ...
0
votes
0answers
9 views

regarding creating two colums using the CONCAT and CONCAT_WS function

Complete newbie here using SQL. Sorry if i don't make much sense here. Basically i'm trying to combine fields on a database that would keep customer's details. For example: fields: customer_title,...
0
votes
1answer
41 views

how to take output from second table by joining in SQL [on hold]

table 1 table 2 id Name value id status 1 a 1000 1 open 2 b 2000 2 closed 3 c 3000 ...
0
votes
3answers
37 views

How to select duplicates into new columns?

I am trying to select distinct data in new columns! My Table productID | imageURL 1 | a1.jpg 1 | a2.jpg 2 | a3.jpg Target Table productID | imageURL1 | ...
0
votes
0answers
15 views

Select all from table where ColA equal to (get list of ColA values that correspond to ColB = 'NOT') [on hold]

I have an SQL table that has entries like: RuleID Symbol 1 OR 1 123 1 363 2 AND 2 847 2 287 3 AND 3 NOT 3 234 3 ...
0
votes
0answers
56 views

SQL Query to calculate total time worked and total distance travelled

I am trying to write an SQL Query which calculates the Total Time worked and Total Distance Traveled by a Driver for each date. The StartOfDay is the MIN(LogDateTime) WHERE EventCode = 1 The EndOfDay ...
2
votes
1answer
55 views

How to show when loan was paid off

First time posting but have been a reader for over 10 years. I have a need to show when a loan was paid off regardless of the rolling balance from refinancing/taking out additional loans. In the ...
0
votes
2answers
249 views

Selecting Only One Record with Most Recent Date Using Table Join

I have a list of 463 CARRIER_CD. I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD. I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the ...
0
votes
0answers
30 views

Split string and and compare parts in WHERE

I had table with string column like 'One Two Three'. A need to split this string by space and use parts in WHERE clause. For example: SELECT * FROM records WHERE regexp_split_to_array(client_name, ...
1
vote
1answer
18 views

How to avoid Msg 9514, Level 16, State 1, Line 3 error for linked server?

I have a query to compare data between my local SQL Server and linked SQL Server. My problem is that the table I compare contains XML column. I get: Msg 9514, Level 16, State 1, Line 3 Xml data ...