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

learn more… | top users | synonyms (2)

2
votes
1answer
106 views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT ...
2
votes
2answers
99 views

SQL Insert Statement Persists Two Records When I Need Just One

I have altered it to include hardcoded variables when I pulled this chunk of code from the stored procedure. I ran it independent of the application with the same result. I need one insert statement ...
2
votes
3answers
711 views

Rolling count of total transactions over time

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...
2
votes
1answer
4k views

How to pass a parameter into a function

At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date? Also is it good ...
2
votes
1answer
483 views

Need help with SQL Server PIVOT

I know about the PIVOT feature in SQL Server and I've read a few other questions and answers on here but I just can't seem to grasp it in order to do what I need. I'm trying to run some calculations ...
2
votes
1answer
2k views

A network-related or instance-specific error occurred

I have been using a local database on my laptop for the last view weeks. But today I cannot access it. I get the error: A network-related or instance-specific error occurred while establishing ...
2
votes
2answers
224 views

Is there really any benefit in wrapping random ad-hoc queries in a transaction?

I am no db expert, but I was very surprised when someone working at our client's company asked us to send all queries wrapped in a transaction like below: SET TRANSACTION ISOLATION LEVEL READ ...
2
votes
2answers
4k views

Running a query within a scheduled job

I am looking at running the following query within a scheduled job weekly: Select TOP 10 s.database_name, m.physical_device_name, CAST(DATEDIFF(second, ...
2
votes
1answer
152 views

how to show only changed rows

if want querying the following table : EventDateTime (DateTime), Host (guid), Flag ( bit) 2013-04-01 05:00, {id-x}, 0 2013-04-01 05:01, {id-x}, 0 2013-04-01 05:02, {id-x}, 0 2013-04-01 05:03, {id-x}, ...
2
votes
4answers
11k views

Search every column in every table in Sybase Database

I'm been taxed with the task of creating an application that pulls data from our Micros Point of Sales system. The POS is using a Sybase database running on one of our servers. The database schema is ...
2
votes
3answers
8k views

How to select the first 10 records for each table in the same database using sql

I have 50+ tables in a database. I don't want to use the following SQL on each table in a database. select * from {table_name} So, is it possible to display the first 10 records for each table ...
2
votes
1answer
96 views

Selecting values that are not associated with one side of a many to many relationship

I have a junction table between categories and values that indicate which categories are associated with which each value. I would like to find the values that are not associated with each category. ...
2
votes
3answers
6k views

Easy or Schedulable way to move data from Production to Test

What I am looking to do, is schedule a copy of data from my production database to my dev/test database. The dev/test database will be newer than the production database in terms of schema, but the ...
2
votes
1answer
199 views

Migrate Data and/or Schema from DBv1 to DBv2

I have a production database which has been plugging away for several months. I have been making modifications to a duplicate copy on a test/dev server. I've added some additional fields (null ...
1
vote
1answer
30 views

Insert multiples rows/columns - PostgreSQL 9.2

I've got a table that has several columns, But I need to insert data into two columns of that table. I need to add data into those columns, from a select statement. Would it be something like: ...
1
vote
1answer
82 views

Select ONE most recent post for each author

I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry. I use recent versions of PostgreSQL and MySQL. I have 2 tables: CREATE ...
1
vote
5answers
157 views

Should I have a column for every data type or 1 column that stores everything

I have a database table that currently looks like this: PageField -Id (Int, PK) -FieldType (String) Can be Text, Decimal, Integer or Bit -Value (This stores the value regardless of the FieldType, so ...
1
vote
2answers
209 views

Sql getting debit, credit and balance issue

I am using the query below to get Debit/Credit Balance from table statement SELECT t.[InvoiceID], t.S_Type as Type, t.Date, t.Debit, t.Credit, b.Balance FROM Statement as t CROSS apply ...
1
vote
1answer
35 views

MYSQL - order by indexed column

I have this table with 500,000 rows: CREATE TABLE IF NOT EXISTS `listings` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `source_id` int(10) unsigned NOT NULL, `cat_id` mediumint(5) NOT ...
1
vote
1answer
840 views

how to insert a list of varchar values, into a single column table?

In SQL Server 2005: CREATE TABLE __RADHE ( itemNo varchar(10) not null primary key clustered ); GO INSERT INTO __RADHE ??? '34926840', '34927020', '34927202', '34927384', '34927566', ...
1
vote
3answers
266 views

Remove duplicate values from query result

I am using Postgres 9.3.9. I have 2 tables : f_agent : CREATE TABLE f_agent ( f_agent__id bigserial NOT NULL, f_agent__total_users integer NOT NULL, f_agent__dim_time_id integer NOT NULL, ...
1
vote
1answer
1k views

How can I select data from two differents servers?

I have two databases: data1 and data2 in two different servers (server1 and server2). I am trying to join two tables of my two databases in MySQL like this: SELECT left(a.code,9) as fourn, ...
1
vote
2answers
855 views

Can I select data inserted in the same uncommited transaction?

Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. ...
1
vote
2answers
215 views

Reset the sequence of a serial PK with dynamic SQL

How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this? My goal is to do ...
1
vote
4answers
88 views

PostgreSQL execute a default query when no row are returned

I need to calculate the average score of a list of userId. I have a table user_place_score: (user_id, place_id, score) Sometimes I do not have the score (no row for (user_id,place_id) in the ...
1
vote
2answers
405 views

Deleting duplicate rows with condition(Using SQL and LINQ)

I have a table with 5 columnsID,CPT4,CPT4Mod,ItemCharge,Eff_Date. Deletion Condition: When rows exists with duplicate CPT4and CPT4Mod field values but different ItemCharge and Eff_Date values, ...
1
vote
2answers
100 views

Convert date and time

I have a column with about 100 records in datetime (for example 2004-08-09 12:20:41:000) and I would like to convert all records or update same time just date only with format mm-dd-yyyy. Thank you. ...
1
vote
2answers
80 views

Making a Database Online [closed]

I am so confused with this issue, I have vb.net application and it has a sql database . Now, I want this application to access the sql server from anywhere , because there are many clients sharing ...
1
vote
1answer
13k views

Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )

I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue. issue is: when I wanted to integrate OWASP ...
1
vote
1answer
401 views

Optimistic Paging and separate query for COUNT?

I am using SQL SERVER 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a Total count. There are 2 ways to achieve this. 1) Paging ...
1
vote
2answers
3k views

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
1
vote
1answer
78 views

Oracle - Date of Selection on table

how to get last date when someone perform selection on table? Last accessed in terms of select statement only not last updated date
1
vote
4answers
214 views

Backup from Server1 and restore it to Server2?

IF we have two Servers installed with SQL Server 2008 instances, Server1 has three drives C, D and E with 100, 500 and 500 GB respectively and Server2 has three drives C, D and E with 100, 300 and ...
1
vote
1answer
213 views

Get maximum number with combination of columns [closed]

I have table with columns like AutoID, Number, Name, City, State, Country. What I wanted is the maximum number entered in the “Number” column with the combination of Name, City, State and Country. ...
1
vote
3answers
218 views

Creating a copy of primary database and attaching the copy database in the same sql instance

I just got into a project in which we have only one SQL Server 2012 copy. We have 12 databases attached to the primary instance of SQL Server 2012. As I don't have SQL Server 2012 or supporting OS, I ...
1
vote
3answers
4k views

SQL services wont start due to incorrect drive letter after windows restore

SQL services wont start due to incorrect drive letter after windows restore Had a server crash, was successfully able to reinstall windows and restore the server SQL and all the necessary services ...
1
vote
3answers
6k views

How do I use SSMS to compare Database 1 Table 1 against Database 2 Table 1?

I have two database - Database1 and Database2. Both database contain a table that have similar structure as follows: ...
1
vote
3answers
11k views

How to import Sybase .db file into SQL Server database?

I've been given a Sybase .db file by a client and I need to import that into a new SQL Server database so that I may see the data as well as the tables and their relationships, any ideas on how that ...
0
votes
2answers
1k 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 ...
0
votes
2answers
196 views

Counting unique records across 2 columns and computing a score based on boolean values

I have a table that looks like this in Google Big Query: custID InteractionDate Purchased? Sales 1 20150312 F 0 1 20150312 T 200 1 20150314 ...
0
votes
0answers
69 views

Auditing SQL Server 2012 [closed]

I work for the State of California. I am working on a project setting up a Data Warehouse sharing data between 3 state agencies. The Data Warehouse will be encrypted. I would like to know what are the ...
0
votes
2answers
60 views

MySQL Case problem

I am trying to run a SQL code (I use MySQL). What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The ...
0
votes
1answer
173 views

Splitting Columns

I want to ask a complicated question and I think its advanced a little. I have a table has this design, its an attendance system. I need to split the time if the attendance count per day was not 2: ...
0
votes
1answer
574 views

How do I count the number of different child id's belonging to a parent record?

For instance I have two tables in a music database. Table album and table track. Each track record has (amongst other things), an album_id and an artist_id Each album record has a unique album_id ...
0
votes
1answer
5k views

pivot a table with cell name as column name

I have a table structure is : year | code | name| value | wef | 2014 | a001 | abe | 2000 | 2014-04-01 | 2014 | a001 | def | 3000 | 2014-05-01 | 2014 | a002 | abe | 2000 | 2014-06-01 | ...
0
votes
2answers
157 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each ...
0
votes
1answer
377 views

How to rollback last executed sql script fired as sysdba

I have fired one sql script as sysdba which I have to fire using other oracle user. I have already created some user and each application running on each oracle user. By mistake I had fired a script ...
-1
votes
2answers
43 views

how to Create A SP with table variable and execute 3 SP statements in the same statement

how to Create A SP with table variable and execute 3 SP statements in the same statement . exec [dbo].[sp_KPI_Churn_MTD_Current_Year] exec [dbo].[sp_KPI_Churn_MTD_Previous_Year] exec ...
-1
votes
1answer
3k views

The SQL server Log folder is expanding because of the SQL Dump files (what to do with it ?)

The "Log" folder SIZE in the SQL server root directory (X:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log) becomes too big , ~80 GB. When I checked it, I see that there are a lot of ...
-1
votes
1answer
198 views

Validate each parent intermediary is also a parent to itself

I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT if table has any. I need to do is first of all get all parents ...