|
Just after opinions here: which of these would be the best practice for an SQLite database:
DELETE FROM Messages WHERE Account = @acct AND NOT EXISTS (SELECT * FROM temp.IdList WHERE MailId=Id)
or
DELETE FROM Messages WHERE Account = @acct AND Id NOT IN (SELECT MailId FROM temp.IdList)
Indexes exist for both Messages(Account, Id) and temp.IdList(MailId)
Each table may hold in excess of 20000 rows
From what I understand, the correlated exists will need to be evaluated for each row of the Messages table, but will stop evaluating as soon as it finds a match (and being indexed, should be pretty quick). However, with the IN expression, the full result set is evaluated only once, but the comparison will be against that full set.
Cheers,
Mick
|
|
|
|
|
Just going by your analysis, I would say the NOT EXISTS would be better. As you say if the table is indexed it should be pretty quick.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I cannot speak for SQLite however I can for SQL Server(which perhaps does not help very much here).
It is best to never use a NOT IN statement, where the results returned could contain many rows, as it will need to evaluate for every single row in the inner select.
Whereas with a NOT EXISTS or a LEFT JOIN with a IS NOT NULL condition, which is another option, it will evaluate as false as soon as it hits the first row where there is a match.
So basically I agree with your evaluation - for what it's worth I always use NOT EXISTS nowadays.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
|
NOT IN and NOT EXISTS are not directly comparable since they treat nulls differently. EXISTS returns TRUE or FALSE only, IN is trivalent and returns TRUE, <code>FALSE or NULL.
Use the one that gives you the result you expect. (Most probably NOT EXISTS)
|
|
|
|
|
Ok guys - I guess the verdict's in. It's what I thought initially thought would be the case too. Its just that the doco on Correlated vs Uncorrelated sub-queries threw me a little, where correlated sub-queries are re-evaluated for each row of the main query. I do agree, however, that re-evaluating the sub-query will be significantly better than searching the full result set.
Cheers people.
|
|
|
|
|
Hi All,
I am trying to write a SQL Query data on to Excel directly, but it is giving me the following error.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
My code is below
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;Database=C:\Test.xlsx;',
'SELECT [ApplicationName], Date FROM [Sheet1$]')
SELECT [ApplicationName], GETDATE() FROM Application
GO
GO
When initially it gave me above error I copied the excel file to the Server itself and ran the linked server script as below
exec sp_addlinkedserver @server = N'webdevsql1',
@srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'C:\Users\AleemA\Desktop\Dont Remove ThisFolder\LocationsExcel\Test.xlsx;',
@provstr=N'EXCEL 12.0;Hdr=Yes' ;
Then ran the script again, now it is giving me little bit different error (ie. Cannot execute the query "SELECT [ApplicationName], Date FROM [Sheet1$]" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)").
Can anybody please help me in this regards? any help a code snippet, a link or even a suggestion helps me. I am also searching and trying from online, but if you have already familiar may be your experience helps me. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 8-Mar-16 14:01pm.
|
|
|
|
|
Hi guys some how I could able to find a way to write selected query values into an xls file but not on the xlsx file yet. If there is a way out for it from somebody can help me, if not for now I will live with it.
But my bigger issue is the below script is writing on to Excel file without headers, can somebody please help me in writing on the excel file along with headers?
Here is my script
DECLARE @cmd VARCHAR(255)='',
@sqlQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@FilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\LocationsExcel\Test.xls"'
SET @cmd = 'bcp '+ @sqlQuery + ' queryout ' + @FilePath + ' -T -c'
Exec xp_cmdshell @cmd
Any help a code snippet, a link or even a suggestion would be greatly helpful, thanks in advance my friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
BCP does not support headers, I used to insert the header string with a linefeed into position 0 after the BCP operation.
This also indicates a workaround[^].
All of which is a kludge!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK I got an Idea I will create a template file with headers then can I append the contents of the headers file + the output file of the bcp into a final file so that I can have both the headers and content.
Or you can give me how to append row or data in the beginning of the file.
Can you please give me some suggestion or link or code snippet to append header file + content file into a final file using bcp or command shell etc?
Any help is appreciated, thanks in advance I am also searching for it.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 9-Mar-16 12:00pm.
|
|
|
|
|
Here is my code to do it may be can be helpful to somebody else, first I wrote a query to just print Headers and then query to print content then I appended those two files with cmd shell into one final file
DECLARE @cmd VARCHAR(8000)='',
@sqlContentQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@sqlTemplQuery varchar(max) = '"Select ''WA ProviderId'' WebAdmProviderId, ''WA Last Name'' LastName, ''WA First Name'' FirstName, ''WA Date of Birth'' Dob, '
+ '''PS ProviderId'' WebAdmProviderId, ''PS Last Name'' LastName, ''PS First Name'' FirstName, ''PS Date of Birth'' Dob"',
@ContentFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Content.xls"',
@TemplFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\TemplateFile.xls"',
@FinalFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Final.xls"'
SET @cmd = 'bcp '+ @sqlTemplQuery + ' queryout ' + @TemplFilePath + ' -c -C -T'
Exec xp_cmdshell @cmd
SET @cmd = 'bcp '+ @sqlContentQuery + ' queryout ' + @ContentFilePath + ' -c -C -T'
Exec xp_cmdshell @cmd
SET @cmd = 'copy ' + @TemplFilePath +' + '+ @ContentFilePath +' ' + @FinalFilePath + ''
exec master..xp_cmdshell @cmd
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 9-Mar-16 19:55pm.
|
|
|
|
|
But one small limitation here, bcp is creating files only in xls format (97-2003) but any body know how to do the same thing to get document in xlsx format like (2007) format? For now I can live with it but just want to acquire more knowledge on it.
Any help is appreciated, thanks in advance friends 
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 9-Mar-16 19:57pm.
|
|
|
|
|
Hi All,
I am migrating data from one table another table in different databases. When source table has line feed and carriage return the destination table is not getting that. All I am doing is inserting the records using select statements.
Can anybody please help me by giving a suggestion how to select columns by preserving line feed and carriage return characters. I am also searching if you know sure that would be helpful.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Your code must be doing something to change the data. The SQL should return exactly what is stored in the database for each record.
|
|
|
|
|
Can you share a sample of the code?
|
|
|
|
|
Sorry, yeah it was code issue, thanks for all your support friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Sorry for a so stupid question.I am new beginer in C# and ASP.net.
Now I am writing the code for my web connect to database.
I am confuse about the function and procedure.
In VB, I store it in .vb file and then I can call in any form.
How can I do the same in website project asp.net.
|
|
|
|
|
|
Hi All.
I have SQL Server, link to Oracle DB.
In the SQL server I have a T-SQL command
select Moto.ne_code,
Moto.supplier_id,
Moto.ne_type_id,
Moto.sta_datetime,
from PMCALCULATE..[PM_TOOL].[MOTOROLA_CELL_HOURLY_KPI] Moto
and I get the result.
But the sta_datetime in the result is not ok
it is similar to: 2016-02-03 00:00:00 00000000
How to format it to yyyy/mm/dd hh24
|
|
|
|
|
What you are getting is a DATETIME data type which is correct. If you want to display it in a particular format then user CONVERT to get the format you need.
You MUST store the data in your SQL Server table as DATETIME, NEVER store DATETIME as a VARCHAR.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have try
select
convert(datetime2(0),Moto.sta_datetime) as Sta_Date1,
convert(datetime,Moto.sta_datetime) as Sta_Date2,
But the result is not as i want
All The result is:2016-02-03 00:00:00
I Want date only and time only
How can I do this.
|
|
|
|
|
|
Where are you using the result:
storing it in the database - do NOT format the data
displaying it in a UI - the UI needs to do the formatting
using it in a report - the report needs to do the formatting
exporting it to a file - this is the ONLY scenario where you should convert the datetime to a string (varchar) convert(varchar(20),DateValue,103) is an example.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I only Want to get Yesterday data from Oracle store in the SQL database.So that why I want to get exacly date to compare with the date in the SQL Database.
|
|
|
|
|
If Oracle has a DATE type (no time component) then in your query convert the datetime to date and filter the incoming values. Other wise use a range of datetime to determine the date you need > '2016-4-1 11:59:59' and < '2016-4-3 00:00:01'
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
