|
|
Thanks for your suggestion.
I did some research and it looks that SQLite can handle big data very well. So this could work for me.
Nevertheless I think simple key/values store databases meight be faster. But I can also be wrong as I don't have much experience with NoSQL DBs.
|
|
|
|
|
Quote: I think my use case is not good for relational databases
Why you are thinking a relational db is wrong here? For me it looks like a rdb fits very good.
For example:
Quote: before adding a new entry, check if the key (string) is already stored
For this you can define the key as Primary or at least Unique. So, before insert you do not Need to check whether it is allready in. Simply insert, the db will tell you then, whether it was allready in. The Advantages: No multiply checks for unique values(one from you explicit, one from DB while checking constraints) and last but not least, no race condition.
[Edit]
One Thing more, MS SQL with its "Clusterd Index" fits Performance whise perfect for key/value pair.
There is only one Thing: "billions of string/number values" looks like you can not go for the free Version because of restricted db size.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Thanks for your answer.
I just thought that a key/values store meight be a better solution. They are usually designed to be fast with key/values... But I'm not very experienced with NoSQL DBs, so I meight be wrong with this.
If I use a relational db I know how to use PK. For the first table I still need to read it first, because if the key already exists, I also need to update the value of this entry. For the second table it would work just to let the DB check the constraint if the entry already exists or not.
MS SQL is not my first choice, because I also want it to work on Linux systems as well.
|
|
|
|
|
Some points to consider:
- are your keys case-sensitive? In MS SQL, string comparison is normally not case-sensitive, while with postgres or Oracle it is.
- what is the relation ship between the two tables? Do I understand you correctly that there are keys which exist in Table1 only, and other keys which exist in Table2 only?
- do you need some kind of reporting? I.e. how many different keys can be found for a value for keys exiting in both or only one table. Complex aggregation queries work fastest in MS SQL or Oracle, while mysql copes with simple aggregations only (but with two tables, that should still be ok).
- What would you do if a key to be added already exists? Update the record or throw an exception?
|
|
|
|
|
Thanks for your reply.
- the keys are all in uppercase in both tables, so a case sensitive match works perfect.
- The first table is the leading data store, the second table is more or less a lookup table. I want to check which entries in the lookup table exists also in the data store table.
- I don't need to search for any values, I need to search only for keys.
- If a key in the first table already exists, I need to update the value (increase the value)
- If a key in the second table already exists, nothing happens, just continue with the next one. But inserting doublicated entries in the secend table are extremly unlikely, so there is no need to check them. It's no problem if for some reason there are really doublicates, to add them twice. But if I use a rdbms and define the key as PK, this check is done automatically.
I will give it a try with a rdbms (not MS SQL because I want it to be available also on Linux systems).
But I'm wondering if a simple NoSQL DB (simple key/value) store meight not be faster in this case. I don't have much experience with NoSQL dbs, but I think they exist for a reason. And I want a really lightning fast solution 
But if you think a normal sql db can be as fast (or faster) than I give it a try.
|
|
|
|
|
Your first point isn't a dealbreaker, it's easy enough to change collation in Sql Server or NLS-settings on an Oracle db.
|
|
|
|
|
Hi,
Im trying to make a ssis to load data from dynamic excel file that store in the folder. i've try every totorial but it still give me the error like this
[Connection manager "Excel Connection Manager"] Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
Error: The result of the expression "@[User::FileName]" on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
1. DelayValidation is set true.
2. Excel Connection String is set as
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\folde\Data\Book2.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";
that i store in variable
please advice.
thanks,
Vinny
|
|
|
|
|
Does the file "D:\folde\Data\Book2.xlsx" exist?
|
|
|
|
|
|
Just a thought. Maybe the connection string parser is being upset by the = inside the extended properties string.
Can you try without HDR=YES just to see if that is the problem?
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
its give me another error
Error 3 Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. ---> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(Object vSource, Boolean vbSourceIsLocation, IDTSEvents100 pEvents) at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.Project.LoadPackage(IProjectStorage storage, Package package, String streamName, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package() at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow) 0
but, when i put HDR=YES the error was change. it says neesd new metadata, its seem like i have different format of file. event thought the file was the same.
|
|
|
|
|
It is very difficult to guess what is going on without more information about the actual code that causes the exception. Please show the code where the error occurs, indicating the exact line that raises the exception, and the exact content of all variables that are being used.
|
|
|
|
|
I want to encrypt email to 6-8 character alphanumeric string in sql server. Is this possible?
Thanks
|
|
|
|
|
Most unlikely no. How would you encrypt something like "ihaveaverylongnameincludingahyphen@mylogsurname.microsoft.com", without losing some characters?
|
|
|
|
|
i have three tables
1) products
=======================
with following columns:
product_id
product_cat
product_cat_brand
product title
product_price
product_image
product_Category
=======================
with following columns
cat_id
cat_title
product_Brand
=======================
with following columns:
brand_id
brand_title
===============
Question???:
================
so my question is:
which table will receive the foreign key of which other table?
or in other words how i can make relations between these tables..
|
|
|
|
|
From what you show only the following can be done/makes sense:
products
(
....
CONSTRAINT FK_products_cat FOREIGN KEY (product_cat) REFERENCES product_Category(cat_id)
)
You need to think again about the layout. It seems that products needs something like product_Brand instead of product_cat_brand. In case this assumption is right then:
products
(
....
CONSTRAINT FK_products_cat FOREIGN KEY (product_cat) REFERENCES product_Category(cat_id)
CONSTRAINT FK_products_brand FOREIGN KEY (product_brand) REFERENCES product_Brand(brand_id)
)
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Hi every body,
I have a very special an uncommon! issue with CDC data migration, this is my problem: we had enabled CDC backup on our DB setting cleanup date for 2 years, so after 2 years we have old CDC data only in DB backups, now our customer needs reports base on CDC data in past 4 years, so the only way we can do that is reinserting old CDC data in corresponding tables by script, we replaced "change_tables" and "lsn_time_mapping" and set the data cleanup date for 10 years. every thing is OK until the cleanup job runs and removes the old data, is some data missed for restoring or is any setting avoid this cleanup?
every suggestion would be appreciated
thanks
|
|
|
|
|
I found the solution, may be useful to someone,
I should update the start_lsn field in the change_tables to map to first entry in the CDC table.
This table is queried by most of the system-defined procs/functions, hence this needs to be updated with the accurate start_lsn:
UPDATE cdc.change_tables
SET start_lsn = (SELECT MIN(__$start_lsn) FROM cdc.dbo_MyTable_CDC)
WHERE capture_instance = 'dbo_MYTable';
|
|
|
|
|
Hi,
I am trying to update the customers MySQL table using SELECT from the same table but getting:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(destination_table.title) =
here is the SQL statement:
UPDATE customers destination_table
SET (destination_table.title) =
(
SELECT source_table.title
FROM customers source_table
WHERE source_table.customer_id = 18
)
WHERE destination_table.customer_id = 14;
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
Not sure about MySQL but you cannot use an alias in an update statement, try removing destination_table and just using customers
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
a.) (destination_table.title) should not be in brackets
b.) Alias in Update, Looks strange but works  (at least MSSQL)
UPDATE t
SET t.name= (SELECT s.name FROM CPTest1 s WHERE s.id = 1)
FROM CPTest1 t
WHERE t.id = '4'
modified 19-Jan-21 21:04pm.
|
|
|
|
|
UPDATE source_table
SET source_table.title = (SELECT destination_table.title FROM customers destination_table WHERE destination_table.customer_id = 18)
FROM customers source_table
WHERE source_table.customer_id = 14
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM customers source_table
WHERE source_table.customer_id = 14' at line 21
Technology News @ www.JassimRahma.com
|
|
|
|
|
Thank you for your feedback, maybe I Need to install MySQL to check. Either way I would go as Mycroft Holmes suggested not to use an alias for the "UPDATE" itself.
UPDATE customers
SET customers.title = (SELECT destination_table.title FROM customers destination_table WHERE destination_table.customer_id = 18)
WHERE customers.customer_id = 14
[Edit]
I just made a test on "SQL Fiddle". Without Alias I get also an error: "You can't specify target table 'CPTest1' for update in FROM clause" 
[Edit1]
Finally I found here mysql update from select - same table - Stack Overflow[^] the answer
update CPTest1 as t,
(
select name from CPTest1 where CPTest1.id = 1) as s
set t.name = s.name
where t.id = 4
Tested on SQL Fiddle[^], MySQL 5.6
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Jassim Rahma wrote: UPDATE customers destination_table
Do you see the un-escaped blank between customers and destination_table? That looks so wrong.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
