Strictly, yes, the FROM clause of a SELECT statement is not optional. The syntax for SQL-99 details the basic SELECT statment, and the FROM clause doesn't have any square brackets around it. That indicates the standard considers it non-optional:
SELECT [ DISTINCT | ALL ]
{Column expression [ A...
Suppose I run the following query:
declare @i integer
set @i=1
while @i < 2000000
BEGIN
insert into t1 (ID,DESC,date) values (@i%200,concat(NEWID(),newid()),GETDATE())
set @i = @i +1
END
Is the loop actually running on the client machine, and calling insert each time over the network, or is...
I wanted to copy a text file containing geographical information into a PostgreSQL
database. The path of the file is E:\MSc_Semister1\AdvanceDB\HomeWork\data.txt.
I used the COPY command, but it is telling me that I have a syntax error.
I have been trying for the whole day and try but nothing wo...
I retagged this (closed) question with etl assuming the tag should cover all (non-SQL) aspects of getting all sorts of data into an RDBMS.
There is also this question—the only one tagged data-import which I suggest we retag etl. However perhaps I'm interpreting ETL too broadly and we should rest...
@JackDouglas I'd say data import becomes ETL when it gets significant 'E' and 'T' components. If you're just loading a pre-digested CSV file into a table it's probably a stretch to call it ETL because of the lack of E and T in the process.
You could use ETL processes to load all sorts of things - CRM systems, general ledger interfaces, posting regulatory reports to electronic interfaces, payroll processing ...
Looking at the question I'd say it's something you could do with an ETL tool but it doesn't really have a 'T' component. More of a data migration problem.
Having said that people use ETL tools for data migration all the time. If anything I'd say the OP's question should be tagged data-migration.
You can certainly use an ETL tool to populate something that isn't a data warehouse - for example I'm currently working on a process that de-duplicates data and populates a CRM system. I'd call it an ETL process. Looks like a duck, quacks like a duck.
However in the case of the question that t...
As far as temp tables are concerned this is much required as temp tables irrespective of the main database are getting created in the tempdb and also as are meant for temporary calculation purpose or resultset hence there is no need to log records for this table and hence truncate (minimal loggin...
Here is a question from Joey, which is his first post on DBA.SE (his user profile shows exactly one question and no answers so far). However, the first posts review list is empty (and today's listing shows zero reviews). Shouldn't this post show up in this list?
EDIT
Recently I experience the...
im having issues with paging and multiple tasks. Been trying to figure out how to pause all the tasks, update my datatable, and then recommence the tasks. Got the paging Sproc working, just cant figure out how to pause and then resume the tasks without creating new tasks.
I know it's possible to change many of the default paths when installing SQL Server, and generally when I do an install I change the data and log folders to be on separate drives (typically D and E), however I've recently been given a pre-installed machine which is running an instance name other ...
I know it's possible to change many of the default paths when installing SQL Server, and generally when I do an install I change the data and log folders to be on separate drives (typically D and E), however I've recently been given a pre-installed machine which is running an instance name other ...
I need a free database software and thought of PostgreSQL. But then I read it has a table size limit of 32 terabytes, but I need more (and don't want to spend thousand/millions on high end database software like Oracle).
Is there a way to manually increase that limit? And if so, what is the pena...
@MarkStorey-Smith Indeed it has. Went independent last summer full time and it's been crazy since. Still crazy but I've really missed helping folks out this way. We'll see if I keep coming back. I need to just make dba.se my start page then I'll have no choice but to be interrupted by that question that is begging for an answer ;-)
I expect it to be a little smaller (with compression). JavaScript functions will be removed (unless they display something on the website by default). And, I'll rather need 33 billion web pages. But yes, I need them. — user13010365 mins ago
I'm planning to use very big numbers in Python, but wonder if Python can handle very big numbers. The numbers are going to have up to 3,000 zeros.
And, how much bytes does a 1 with 3,000 zeros use?
Third question, how can I save a number as integer into a file with Python without having to str(...
Anyway, can't think of any other options. I'd go for a DDL on a table involved and take the hard parse hit for other queries involved. Would only be a short perf blip
Just read a blog that says a comment on column ... ''; does the trick
@Phil docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_shpool.htm "To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script. The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs. These scripts are not run by CATPROC.SQL."
@Phil hmm i'll give it a shot. Any table involved in the query should trigger it right?
I may have found a 'magic bullet' earlier for some serious performance problems we've had for about 2 years.. A rogue UPDATE that basically updates every row in a table - so it locks out another critical update for the time it runs. Its never long enough to show up on reports or such, but it turns out it runs a few hundred times a minute.
we're shutting down a service, making a tiny change, and restarting it...I think it'll fix it...VERY, very excited
update bigtable set field1 = 0 WHERE not exists(blah blah blah) and field1 <> 0 ;
This is where you find out you've spent way too much on Oracle licenses and beefy boxes because you don't need the horsepower you thought you needed! :)
Happened to me many moons ago - we ended up only partially fixing the code (in a C batch), as making it an order of magnitude faster would have been too embarrassing and we didn't want the customer asking why 90% of their new Sun E15k was now sitting idle
I meant "enterprise" in terms of size. Biggest MySQL server I've played with had just 32GB or ram (Read: COTW's laptop size). rfusca's db is probably a scale or two larger.
I had a similar question open on SO for postgres - now having the same issue with mysql..
I have two tables -
Table A : 1MM rows, AsOfDate, Id, BId (foreign key to table B)
Table B : 50k rows, Id, Flag, ValidFrom, ValidTo
Table A contains multiple records per day between 2011/01/01 and 2011/1...
@headsling Instead of constant contradiction, if I were you, I'd try the same with PKs. Only then would you know whether those are irrelevant or not. (If everybody seems to drive on the wrong side of the motorway, what do you suspect?) — dezso29 secs ago
Title says it all. I had a situation where a moderator answered a question and then marked another answer (that said the same thing, but more directly) for conversion to comment because they felt "its length was not up to community standards".
While I think it is important to maintain some set ...
@swasheck IIRC, an instantclient install is a single directory, and it doesn't create any other directories (eg; $ORACLE_HOME/network/admin ) for you. I think the instantclient works fine with a tnsnames.ora plonked into the installation directory
In terms of answering your actual question, the best bet for what amounts to trying to change the internal postgres code and recompile is to ask on the postgres mailing list. Getting an answer from somebody like Tom Lane is what I'd do before I attempted to do something this drastic. But you'll likely get the same "why in the world would you want to" response. — rfusca1 min ago
For a single string
You can apply the window function row_number() to remember a distinct order of elements. However, with the usual row_number() OVER (ORDER BY col) you get numbers according to the sort order, not the ordinal number of the original position in the string.
You could try and sim...
if you're migrating from Access to SQL, I really reccomend you look at Client-Server migraition options for SQL Server Express. I think that this is an AWESOME platform, and it should allow you to easily bring your application to the next level. SQL Server Express is AWESOME, I use it and freque...
@rfusca riiiiight ... doesnt exist. downloaded the deinstaller and ran with the flag -home T:\app... which did everything but remove the registry entries
That sounds much like/like much work. But since Apache License 2 seems to be free for commercial apps: I just read at Apache Cassandra's website there are Cassandra clusters at 300 TB sizes, which sounds like quite enough. — user130103610 mins ago
@swasheck lol thats oracle ...numb your mind so that writing the check seems painless
@swasheck haha, back in the olden days, I tried to install Oracle9 onto a Solaris (5.9 perhaps). 3 CD set, after finishing with the first one it asked for the second, but did not eject the disc. It took a while and a few phone calls to get around.
@rfusca me and oracle are about to have a "come-to-jesus moment" in a few minutes
at any rate ... my wife and i were very active in our church and we decided that it was more fulfilling so i decided to go to seminary to start moving in the direction of working in a chuch. i chose an academic degree since that's more my style and along the way decided i'd rather NOT work in a church. i'm the only income in our family so i stuck with DBA work to get myself through school.
Failed to delete the file 'L:\app\svcsql\product\11.2.0\client_1\inventory\invDetails.properties'. The file is in use. but it's not ... i love you windows ... i love you oracle ...
Happy to hear that. :) It's strange, something like when you buy a house, oneday you come in and see someone else suddenly is living there! ;D I could have change the code if you had helped me a little. Cheers bro. — Mohammad Goudarzi51 secs ago
Basically what happened is on a developer box which all of the devolopers log into the same account.(I know that absolutely blows and they all have SA...Nothing I can do about that) I build a job that would run two stored procedures every minute. Someone changed the data base these sprocs run on and I want to find out who did it or at least when it happened.
@rfusca there are a few non-denominational churches as well, but outside the membership (or what would be the proper noun) not too many people have ever heard about those
@ypercube ver' orthodox ... it was interesting rolling through the streets of athens with the juxtaposition the orthodox heritage and the signs for upcoming porn convention
@swasheck So who are you on twitter anyway? Pretty neat to see you answer questions in DBA.se and Hermeneutics :-) Interesting to see the church discussion in here, too.. Missed it earlier.
good gracious , the lengths people will go to before they just research something. I see this query with an 'odd' program source in Oracle. So I email the dev and I'm like "whats that?" "Well, the query was taking a long time in Oracle SQL Developer, so I just wrote a program to run it instead of running it in sql dev"....
Suppose I run the following query:
declare @i integer
set @i=1
while @i < 2000000
BEGIN
insert into t1 (ID,DESC,date) values (@i%200,concat(NEWID(),newid()),GETDATE())
set @i = @i +1
END
Is the loop actually running on the client machine, and calling insert each time over the network, or is...
You can certainly use an ETL tool to populate something that isn't a data warehouse - for example I'm currently working on a process that de-duplicates data and populates a CRM system. I'd call it an ETL process. Looks like a duck, quacks like a duck.
However in the case of the question that t...
Here is a question from Joey, which is his first post on DBA.SE (his user profile shows exactly one question and no answers so far). However, the first posts review list is empty (and today's listing shows zero reviews). Shouldn't this post show up in this list?
EDIT
Recently I experience the...
I had a similar question open on SO for postgres - now having the same issue with mysql..
I have two tables -
Table A : 1MM rows, AsOfDate, Id, BId (foreign key to table B)
Table B : 50k rows, Id, Flag, ValidFrom, ValidTo
Table A contains multiple records per day between 2011/01/01 and 2011/1...
For a single string
You can apply the window function row_number() to remember a distinct order of elements. However, with the usual row_number() OVER (ORDER BY col) you get numbers according to the sort order, not the ordinal number of the original position in the string.
You could try and sim...
