Tag Info

New answers tagged

0

3 million rows is not an outrageous amount of data. You'd be stretching it for an Access database, but SQL Server, Oracle, DB2, or any number of other RMDBs can handle this much or much more data. If you were looking for state or transactional data streamed in real-time from many, many radio stations or services with tens of thousands of state-checks a day, ...


0

Another way to solve this: WITH cte AS ( SELECT q.query_id, COUNT(d.id) AS sample_size, SUM(d.weight) AS weight, SUM(CASE WHEN d.var BETWEEN 9 AND 10 THEN d.pond END) AS promoter, SUM(CASE WHEN d.var BETWEEN 0 AND 6 THEN d.pond END) AS detractor, MAX(d.week) AS max_week FROM generate_series(1, ...


0

metric columns are redundant in survey table. we can create a new metric type column to differentiate multiple metrics and to normalize survey table. we can even do this normalization in inline view. following sample query may explain this. it use SurveyNormalized inline view and it take aggregate group by metricType. CREATE TABLE #Surveys(id int, VAR ...


1

Thanks to @Lennart, with the usage of count(distinct c.id), I can successfully both count the creations and the sum of amount: select date_part('year', c.created_at) as yearcrea, date_part('month', c.created_at) as monthcrea, p.reference, count (distinct c.id) as nb_crea, (sum(o.amount_ati_cents) / 100) as ca_euros from creations as c inner join products ...


1

yearcrea and monthcrea does not exists at the time when the sub-select is evaluated. The following should work: and date_part('year', c2.created_at) = date_part('year', c.created_at) and date_part('month', c2.created_at) = date_part('month', c.created_at) but do you really need creations as c2 and products as p2 in the sub-select? Should not: ( ...


1

Finally we have decided that the best way to do it is with the checksum. The reason is: we want to compare rows per rows for the specified columns (wich checksum lets you specify). At first we wanted to go with hashbytes but as Mikael told me, it has a limit of 8000 bytes, and that is really too small. And although Adam's solution is a good one, it is not ...


2

This can only be if ... you did not actually commit your transaction (yet) and running the second query in a different transaction. or you have other transactions writing to the table in the meantime or something is seriously broken or you are dealing with two different tables: It's worth mentioning that your ALTER TABLE commands are on ...


2

This may not be exactly what you are looking to do, but you could use the EXCEPT statement to get the rows that are different: SELECT Column1, Column2 FROM DatabaseName1.dbo.TableName1 EXCEPT SELECT Column1, Column2 FROM DatabaseName2.dbo.TableName2 If you do it this way though, make sure you also do the opposite EXCEPT so you know the rows that don't ...


1

I have always gone with Option #2, and have done so in such a way that there was minimal impact on the system as it was always assumed that that application would be running continuously, or down for 30 minutes tops. So, I would build the new table and slowly migrate the data into it over the course of a week. I would use a SQL Agent job to insert rows and ...


5

A FOREIGN KEY constraint cannot reference a table in a different database. It's by design. Here's an example: CREATE DATABASE test1 GO USE test1; GO CREATE TABLE tableA ( PK int PRIMARY KEY CLUSTERED ,someColumn varchar(50) ) GO CREATE DATABASE test2 GO USE test2; GO CREATE TABLE tableB ( PK int PRIMARY KEY CLUSTERED ,aForeignKey ...


0

the below script helped :) thanks DECLARE @command varchar(1000) SELECT @command = 'USE [?] select DB_NAME() as DatabaseName, @@SERVERNAME as ServerName, Object_ID = (id), OBJECT_NAME (id) as StoreProc, Text from syscomments where text like ''%Janice\SQL%DTS%''' EXEC sp_MSforeachdb @command


1

You could use user defined variable in mysql to achieve your goal: create table t ( id int not null auto_increment, col_a int not null, col_b int not null, primary key(id) ); insert into t(col_a, col_b) values (1,20), (1,70), (1,110), (1,30); set @sum := 0; select *, col_a * col_b, @sum as s from t ...


0

SQL Server is designed to use as much memory as possible. The perfect situation is caching all the databases in memory. It is good that it uses a lot of RAM - it means faster data access. However, it is recommended to leave some memory for the operating system. You can run the following query to determine how much memory is recommended for your system. ...


1

I think what you'll need to do is create another temp table that has the same columns but with an identity column so you can use it for joining purposes. I also noticed that your date format had day.month.year which threw me off initially.... This will get you pretty close: create table newtable ( cid int, fromdate datetime, todate datetime) go insert ...


4

I would change your table layout. If you used something like: (LineId int, BallSequence tinyint, BallVal int) The combination of (LineId, Ballsequence) would be your PK. Then your query would be: SELECT COUNT(*) FROM ( SELECT 1 FROM dbo.Lines GROUP BY LineID HAVING SUM( CASE WHEN BallVal IN (@ball1, @ball2, @ball3, @ball4, @ball5, ...


3

Your LEFT JOIN is converted to a INNER join because the WHERE contains conditions about the columns of the right (linkcats) table. Then, since the conditions are contradicting, the query will always return 0 rows: WHERE linkcats.cat_id IN (17,1,35,33,50) AND linkcats.cat_id NOT IN (28) You need a semijoin and an anti-semijoin, which can be written with ...


1

You could create a table-valued function like this: CREATE FUNCTION dbo.OrdersWithProduct (@ProductId int) RETURNS TABLE AS RETURN ( SELECT o.* FROM dbo.[Order] AS o WHERE EXISTS ( SELECT * FROM dbo.OrderDetail AS od WHERE od.OrderId = o.OrderId AND od.ProductId = @ProductId ) ); And ...


3

I have no idea about efficiency* but a view like this: CREATE VIEW Orders_with_a_product_View AS SELECT p.ProductID, o.* FROM dbo.Product AS p JOIN dbo.[Order] AS o ON EXISTS ( SELECT 1 FROM dbo.OrderDetail AS od WHERE od.OrderID = o.OrderID AND od.ProductId = p.ProductId ) ; ...


0

You can get the server principles through the following query: SELECT name, principal_id, sid FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE' This works in every database, so you do not need to target a specific database.


1

No, don't use sys.syslogins - this is a deprecated compatibility view that is only there so people have time to change their existing code based on SQL Server 2000. Also, I'm completely unclear how you think a CASE expression will help you. You need to join to sys.server_role_members to find out which logins belong to which roles, and then back to ...


1

Columns specified in WHERE and JOIN clause predicates are the candidates for index keys. ORDER BY columns are also candidates if it can avoid an expensive SORT operator for many rows. Columns specified only in the SELECT clause are candidates for non-clustered index INCLUDED columns when the cost of the additional storage and maintenance outweigh the ...


0

Try this, select * from capturainfo where date <= 'your last date' AND datefin='null' hope it will help.


1

Aftee searching the Ideone FAQ, it seems that it uses Sqlite3 for the SQL language. Sqlite needs PRAGMA foreign_keys = ON; to enable foreign keys checks. As they say in their docs: SQLite Foreign Key Support : Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA ...


-2

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; BEGIN select @@SERVERNAME as ServerName, DB_NAME() as DatabaseName, Object_ID = (id), OBJECT_NAME (id) as StoreProc, Text from syscomments where text like '%Janice\SQL%DTS%'. END


1

This line represents a cartesian product of 3 tables: FROM student a, enrolled i, class d After creating a cartesian product you filter out rows that match the predicate. WHERE a.id= i.student_id AND i.disc_id = d.id If you look closer at this query it is equivalent to: SELECT a.name, d.name. d.grade FROM student a JOIN enrolled i ON ...


0

I hope that you put values according to SQL standard, so it it can be queried. Then you can use where not exists clause to filter out according to your requirement. For example select id from items where not exists(select id from activities); Hope it helps.


0

You had one closing parentheses too many in the latter query. You also have columns date and datefin and the distinction between the two is not clear. If you want to do a comparison per day, you have to make sure both rows are from the same day in the inner query (a.date = b.date) and there has to exist b.dateinit where a.dateinit is smaller (<) not ...


2

The solution heavily depends on how much work you are willing to put in your initial query and what is going to consume the data (and how). -- Option 1 -- If this is a one-off requirement, you can use variations of sp_MSForEachDB to retrieve a separate data set for each database. Personally, I don't like sp_MsForEachDB for a number of reasons, mainly ...


0

You can do this using an UPDATE: UPDATE inventory i SET quantity = ( SELECT s.quantity+i.quantity FROM shipment s WHERE s.itemname = i.itemname ); or a MERGE: MERGE INTO inventory USING ( SELECT itemname, quantity FROM shipment ) ship ON (ship.itemname = inventory.itemname) WHEN MATCHED THEN UPDATE ...


0

No need to refactor the single query into two separate ones. SQL Server should be able to figure out the most optimal way to execute the query on your behalf. Make sure the Culture table has a unique index on CultureTag and, in the case of a non-clustered index, add all of the columns needed by the query as INCLUDED columns.


0

I cross-posted this to facebook and a friend commented with this solution. He doesn't have a SE account though. I'm trying to encourage him to make one, but until then, I'll go ahead and provide this. It might be the answer we've been looking for. SELECT SIK.Name AS [QuestionKey], SIV.JsonValue AS [ResponseValue], COUNT(SIV.Id) AS [Frequency] FROM ...


0

Action Item needs to have a foreign key. A column called SuperTask_ID perhaps. http://stackoverflow.com/questions/48772/how-do-i-create-a-foreign-key-in-sql-server You probably want cascade as well: http://www.codeproject.com/Articles/620086/CASCADE-in-SQL-Server-with-example


1

Lennart's answer explains why your query fails and how to fix it so it is syntactically correct. About the additional problem that you "want to display the output regardless if one of the joined sub queries doesn't have any rows", you could rewrite the query and simplify it: SELECT AVG(CASE WHEN es.DateID = 'Jan2015' THEN sf.score ELSE NULL END) AS ...


1

Below is what you need CREATE TABLE customers ([id 1] int, [status] varchar(4), [id 2] int, [name] varchar(4), [category] varchar(6)) ; INSERT INTO customers ([id 1], [status], [id 2], [name], [category]) VALUES (001, 'open', 011, 'john', 'person'), (001, 'open', 011, 'john', 'male') ; select * from dbo.customers ---- You have to use ...


2

You need to specify whether you want an left, right or full outer join. A left outer join is the same as left join. Assuming this is what you want: [...] FROM (SELECT subfactor.score, dateID, EmployeeID FROM employeescore LEFT JOIN subfactor ON employeescore.SubFactorID = subfactor.SubFactorID LEFT JOIN factor ON ...


-4

Remove the keyword OUTER. MySQL uses the ON/WHERE clause to decide between INNER and OUTER functionality. There is another problem... If Employee#1 was not there one of the months, the result will be the empty. I suggest that you use a 4th subquery (first) to find all employes, then LEFT JOIN to each of the existing subqueries. But then you may have ...


0

I could achieve your query in three steps. Assuming your table values like below Step1 : In SQL View paste this query and save it as part1, This is for package_id transpose TRANSFORM First([packing].package_id) SELECT [packing].[product_id] FROM packing GROUP BY [packing].[product_id] PIVOT [packing].package_id; Step1 : In SQL View paste this query and ...


3

I have an EAV for tracking server / database configuration. It's great for getting data in. We can throw any data at it and the loader ensures the "E" and the "A" reflect the data given. Once we got over a few hundred million rows in the Values table, however, getting data out became increasingly problematic. (I think having 400-column PIVOT queries ...


8

EAV is not evil; like any other tool, it can be implemented poorly and abused. You can find articles trash talking cursors, dynamic SQL, triggers, even SQL Server itself. That doesn't make them bad things. EAV can be an appropriate solution. Whether it's the right answer in your specific case is probably more opinion-based than anything; I'm answering more ...


0

You are missing the single quote closure after motherboard in both of your queries AND value != 'Motherboard AND AND a.value != 'Motherboard The first query can be more organized as SELECT id_product FROM Atr_basic WHERE attribute = 'Product Type' AND category = 140 AND value NOT IN ( 'Motherboard', 'Intel Motherboard')


0

The error is being raised because it doesn't know how you want to define the dummy_driver table. What fields should be part of the table? From the code you show I am assuming dummy_driver has the same fields as Person, given the code you've shared. If so, the code would look like this to create the table: CREATE TABLE dummy_driver ( first_name <insert ...


0

There's a few things wrong with your post. But cutting to the point: Judging by your code samples, the fastest/easiest way to create a table as a copy of another is to "select * into tableA from tableB" In your case, assuming you only have (or want) the 2 columns you have listed you could use the following: SELECT f_name, l_name INTO dummy_driver ...


0

You have to define the elements (columns) of a table. For example. This code creates a table named people with fields for first name and last name (defined as firstname and lastname respectively) which holds up to 30 characters for each name: create table people( firstname varchar(30), lastname varchar(30) ); Next, we can insert the person John ...


2

Just pull the DataTable object(s) and enumerate the DataColumnCollection. Here is some sample code to show how you can enumerate the collection and dump the column names: DataTable queryResults = // get your data foreach (DataColumn column in queryResults.Columns) Console.WriteLine("Column Name :: {0}", column.ColumnName); Of course this is an ...


1

I've seen it done both ways. If you want to bulk up your database size just start using BLOBS. Pro's for storing documents in your database you're already storing your data there so why not a little more? (Only it's a lot more) a certain simplicity of access. Any request to read only has to go one place to access the document or thing Cons for storing ...


1

If you want even more automation, then download SQLcl, then you can do fun things like ctas current_table new_table and it generates necessary code for you. e.g., SQL> ctas my_table new_table CREATE TABLE OWNER."NEW_TABLE" ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", ...


1

Instead of using simply text based replace, you can do this with the metadata API, which is a more robust solution. Lets say you have a table X.TABLE1: create table x.table1 (id number, name varchar2(100), primary key(id)); You want to recreate it as Y.TABLE2, you could do the following: set serveroutput on declare metadata_handle number; ...


0

Ok, After further analysis and following the recommendation of Aaron Bertrand and google "parameter sniffing" i found what is causing this its the SQL Server. This is the "normal" behavior of the SQL Server, and the queries must be analysed and changed individually to resolve its problems. Here is the link for the best description of what is happening ...


2

Yes you can. Or no you can't. It doesn't really matter. If you think about how DBMS1 is going to have to deal with a commit and/or rollback after it has, via your UDF function in which you have hidden an update to another, external, DBMS2, then you should realize that this is a very bad idea no matter how you toss or turn it, because somwewhere somehow it ...


0

IF((select REPLACE(name,'.','') from #tmpDots) = (select name from #tmpNoDots)) PRINT 'TRUE' ELSE PRINT 'FALSE' Replace the dots with the space of the characters that are worrying you using the Replace() function in SQL and this should do it. You can also embed this logic in some SQL function or in an SP. Let me know if more help needed.



Top 50 recent answers are included