Tag Info

New answers tagged

2

Since you can reset the IDENTITY by issuing a simple TRUNCATE: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' + CHAR(13) + CHAR(10) FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] INNER JOIN sys.identity_columns AS ic ON t.[object_id] = ...


0

You can write a script to call DBCC CHECKIDENT ([TABLENAME], RESEED, 0) over the tables resulting from this query (which will inform of all the tables with an identity field. SELECT t.TABLE_NAME ,c.COLUMN_NAME ,c.TABLE_CATALOG ,c.TABLE_SCHEMA FROM ...


0

You could try this scripts to find and replace for ALTER TABLE. How to find column names for all tables in all databases in SQL Server Search for a string in all tables, rows and columns of a DB


0

I have the following solution, which gives the desired result, except the values for the days that there is no entries for: set @total=0; set @sku_name=''; SELECT date, sku, total, CATEGORY FROM ( SELECT date, sku, @total:=IF(@sku_name=sku, @total, 0)+qty AS total, @sku_name:=sku FROM ( SELECT date, sku, SUM(qty) qty FROM ...


-3

log into sqlplus as username:SYSTEM give previous password sql>password old_password:<oldpassword> new_password:<new password> retype new password:<new password> password is set


0

You should be able to enable logging for debugging purposes, however the question of whether to log every SELECT depends on how much this data is worth to you - are you prepared to spend the money on a server powerful enough to log this data and the price of a backup strategy for this data. Furthermore, there's little point in logging everything if you ...


0

If your system gets pounded with hundreds of queries every second and you have a storage constraint, it does not make sense to log every query.Assume the maximum allowed response time at user-end is x seconds. Other tiers plus network latency may take upto y seconds. So the maximum permissible time at db tier is x-y seconds. Any query that takes more than ...


3

You can use the following approach in SQL Server to pivot the data into the format you are looking for. You may also be able to use the PIVOT operator, but I tend to prefer to write out the CASE statements since I find that syntax more clear (they have always resolved to the same query plan in all cases that I have checked). Updated answer based on Aaron ...


3

Here's one way. Assuming this table and sample data: USE tempdb; GO CREATE TABLE dbo.splunge(UserID CHAR(5), Name VARCHAR(32), Date DATE); INSERT dbo.splunge(UserID, Name, Date) VALUES('t0001','Tod','20150629'), ('t0001','Tod','20150629'),('t0001','Tod','20150629'),('t0001','Tod','20150629'), ...


1

I don't know if is equal on SQL Server, I did this in MySQL using the WEEKDAY function. Table UserRecords: Query: SELECT user, count(case when WEEKDAY(qDate)=6 THEN qDate END) as S, count(case when WEEKDAY(qDate)=0 THEN qDate END) as M, count(case when WEEKDAY(qDate)=1 THEN qDate END) as T, count(case when ...


2

If (and it's a big if) the format remains constant with a number and a unit, you should be able to create a case statement that will get the total of the smallest unit. In your example, let's use days as the smallest unit: CASE WHEN timeToMaturity LIKE '%Days' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 1 WHEN ...


2

I've made a function where you just put the string and the output will be in Capital Letters: DELIMITER // CREATE DEFINER=`root`@`%` FUNCTION `LCAPITAL`(eCADENA VARCHAR(150)) RETURNS varchar(150) CHARSET latin1 DETERMINISTIC BEGIN DECLARE vPOSICION INT DEFAULT 0; DECLARE vTMP VARCHAR(150) DEFAULT ''; DECLARE vRESULTADO ...


0

This answer presumes there is no many to many table with BidId related to ContractorId which should probably exist. Examples of the tables from which you are querying are as helpful as seeing the example of expected result. DECLARE @BidId int = 1 SELECT BidID, ContractorName, ContractorEmail FROM tblBids B JOIN tblContractors C ON ...


1

maybe I'm a bit too "old school" but I've used a query and script for about 10 years now since the SQL Server 2005 days: SELECT LEFT(d.name,20) AS database_name, CONVERT(VARCHAR(16), MAX(CASE b.[type] WHEN 'D' THEN b.backup_finish_date END), 120) AS LastFullBackup, CONVERT(VARCHAR(16), MAX(CASE b.[type] WHEN 'I' THEN b.backup_finish_date END), 120) ...


1

The sub-select: SELECT role_id from role WHERE role_name = 'tech' returns exactly one row, so your query can be transformed to: SELECT emp.name, role.role_name, emp.emp_id FROM emp JOIN role ON emp.role_id = 1 This can be transformed to: SELECT emp.name, role.role_name, emp.emp_id FROM emp CROSS JOIN role WHERE emp.role_id = 1 Therefor the ...


4

Your query, given your data, is equivalent to this one: SELECT emp.name, role.role_name, emp.emp_id FROM emp INNER JOIN role ON emp.role_id = 1; -- because this is the role_id for 'tech' This means that you don't define a condition about how to join the two tables. In turn, this results in a Cartesian product of role (without any restriction about ...


1

CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1)) You missed one more closed parenthesis in your statement CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1))) 2.The query you wrote for concatenation returns only first letter in capital letters of both fist and last names and it doesn't print space between them. example :First ...


4

Insert query: INSERT INTO pdone.reps (veeva_rep_id,display_name,username,first,last,email,avatar_url,rep_type) SELECT Id, CONCAT(UCASE(MID(firstname,1,1)),LCASE(MID(firstname,2)),' ',UCASE(MID(lastname,1,1)),LCASE(MID(lastname,2))), username, firstname, lastname, email, 'www.some_static_url.com', '1' FROM veeva.user ...


3

MySQL, when executing an update against a single table, does -- wrongly -- treat updates as "ordered," and update the columns in the order they're written in the query. The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This ...


0

What is the error message ? Have you checked your backup? Run below syntax to check your backup: RESTORE VERIFYONLY FROM DISK = 'D\backup\databaseName.Bak' GO RESTORE VERIFYONLY has been enhanced to do additional checking on the data to increase the probability of detecting errors. If there is any errors or corruption then please continue restore with ...


2

I think you're running the restore with the NO RECOVERY option. This will restore your database, but will leave it in the RECOVERY state. You can easily check this by taking a look at it in the SSMS. It should say something like: yourDatabaseName (Recovering...) If you haven't the option to take a look in your SSMS, you can simply querying your master ...


2

I assume the * is only for demonstration purposes. You can use GROUP_CONCAT to aggregate strings from several rows into one string. In the example below I only include carname, you probably want some more info: SELECT c.carname , GROUP_CONCAT(p.partname) FROM carparts as cp JOIN car as c ON cp.carid = c.id JOIN parts as p ON p.partid = ...


2

you structure looks good . except that you could merge the amount to the part table . as every part is strictly associates with price. also, only reason you will have duplicate rows is if any of your dimensions tables do not follow strict primary constraints. Car - id (pk) - carname - image - category - status Parts - partid (pk) - partname - ...


1

Not knowing what the full relationships are or sample data, try this FK correction first to see if it fixes the problem: SELECT * FROM carparts INNER JOIN car on carparts.carid = car.id INNER JOIN parts on parts.partid = carparts.carpartid INNER JOIN amount on amount.amountid = carparts.amountid WHERE status = 1


2

I found this: SOURCE There is great reason why you should never call stored procedures from within triggers. Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive ...


0

It is not very clear from your description a) what DB you are using? b) do you need help in the design of tables? c) or you need help in SELECT query only? But then please show your tables. So far it is clear only that you have 2 entities Contacts and Helpers, linked as M:M, so Renzo is correct suggesting a third link-table. Let you want to start dance ...


0

There are a few different strategies you could take, where on one end you pursue aggressive normalization to, on the other end, full denormalization. The full denormalization would be equivalent to your second example where all relevant info simply ends up in the transaction table without references to other tables. Full Normalization So, to completely ...


0

This is a typical situation in which you have to create three tables: Helpers, Contacts, and Help_Contact, using the third table to represent associations between the elements of the first two, by storing the foreign keys for the first two, without any multivalued field in those. That is, you should have something like: Helpers(h_primary_key, ...


6

As usual, it depends. Let's first consider what a key is, it is something that uniquely identifies something in your business. Even if you choose to use a surrogate key for some reason you should still identify and implement the natural candidate key's. A key ideally has a number of properties unique stable -- should not change frequently, not ...


2

Joe Celko thinks very strongly that you should use the "real world" data such as a Name rather than using an artificial value such as an ID. However, many people here on DBA.SE prefer to use an ID value as the Primary Key. But it is a to some degree a philosophical question, or personal opinion. An aside: You did not say which brand of SQL (MS SQL ...


2

I'd use a surrogate product id. This avoids the need to store potentially lengthy strings multiple times across every order they are used in. Additionally if the product name is ever updated you only need to do this in one place. It is also more flexible as it allows you to have multiple different products (perhaps over time) with the same product name. ...


-1

I solved with: INSERT INTO foo(Val,i,j) SELECT myArr[i][j] ,i,j FROM Generate_Series(1,lbound) i JOIN Generate_Series(1,ubound) j ON true


1

Try this in just 1 line of code: SELECT articleId, max(version) as currentVersion, CAST(GROUP_CONCAT(version SEPARATOR " | ") as CHAR) as verification FROM journalarticle GROUP BY articleId; PD: I added CAST(GROUP_CONCAT(version SEPARATOR " | ") as CHAR) as verification so you can see that is taking the last version ...


-2

I found that solution: SELECT id_, articleId, version FROM ( SELECT id_, articleId, version FROM mf3_liferay.journalarticle ORDER BY version DESC ) as sub GROUP BY articleId ORDER BY ArticleId DESC But I am not sur regarding performances issues...


0

Try SELECT a.* FROM journalarticle a INNER JOIN ( SELECT articleId, MAX(version) as v FROM mf3_liferay.journalarticle GROUP BY articleId ) b ON (b.articleId = a.articleId AND a.version = b.v) Update: as ypercube pointed out, version is double which is approximate type, so conversion to exact type is needed : SELECT a.* FROM ...


0

To retrieve the ArticleID and its latest version, you can use this query: SELECT articleId, MAX(version) FROM journalarticle GROUP BY articleId; If you need to get all field from the table, you can use this query: SELECT * FROM journalarticle WHERE CONCAT(articleId, "##", version) IN (SELECT CONCAT(articleId, "##", MAX(version)) FROM ...


1

USE mydb SET NOCOUNT on BEGIN TRY DROP table #S END TRY BEGIN CATCH END CATCH BEGIN TRY DROP TABLE #Mytable END TRY BEGIN CATCH END CATCH BEGIN TRY DROP TABLE #DBInfoResults END TRY BEGIN CATCH END CATCH go CREATE table #S ( server_name sysname, purpose VARCHAR(255), servertype VARCHAR(30) ) insert into #s VALUES ('myserver', 'Test', ...


1

You're almost there. SELECT DISTINCT returns that group of columns, as you've found out. If you use DISTINCT ON instead though, you'll get the rows you're looking for. INSERT INTO Table_A (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ) SELECT DISTINCT ON (colA, colB, colC, columnD, colE) colA, colB, colC, columnD, colE, columnF, ...


0

Window Functions, or the OVER() Clause It appears that what you are looking for is a window function or an OVER() clause. In your original example, you are trying to use two max() conditions, which doesn't work, because when you try to then apply GROUP BY, you can have a condition where the max of the first column ID and the second column startdate aren't ...


5

TL;DR The query Visual Studio is executing is being blocked and the query times out. Turns out I've managed to work this out for our situation: timing out due to blocking! I ran a profiler trace with a filter on application name of Microsoft SQL Server Data Tools, Schema Compare to capture the Visual Studio schema compare events. It would reach a ...


0

First thing you need to check is that: Has the SQL Server service account been added as a member of any local groups on EACH server? Does the group in which it resides have read-write permissions? So please check if you're NW service account is admin on the box, along with R/W permissions for it to access the folder for backups. Also, in case you are on ...


0

UPDATE ykz2d_maps_integration , ykz2d_crmery_deals SET catid = '584' where ykz2d_crmery_deals.company_id = ykz2d_maps_integration.company_id ; The table must be listed in the update clause to avoi the error message


0

This should work better and faster: UPDATE ykz2d_maps_integration as ymi join ykz2d_crmery_deals as ycd on (ycd.company_id=ymi.company_id) SET ymi.catid='584'; It sets ykz2d_maps_integration.catid='584' without any subquery syntax. Hope this helps.


0

I messed around and eventually this worked: UPDATE ykz2d_maps_integration SET catid='584' WHERE EXISTS(SELECT company_id FROM ykz2d_crmery_deals WHERE ykz2d_maps_integration.company_id = ykz2d_crmery_deals.company_id);


0

http://sqlfiddle.com/#!9/c88a4/8 select t1.id, t1.t, time_to_sec(t1.t) s from times t1 left join times t2 on t1.id = t2.id and time_to_sec(t1.t) > time_to_sec(t2.t) and abs(time_to_sec(t1.t) - time_to_sec(t2.t)) <= 20 where t2.id is null group by t1.t, t1.id uses "groupwise-max" method with dynamicaly defined group. Seems to work on your testing ...


1

You need 2 joins to itemGenres, separating the "check" logic (WHERE genres.id IN (1)) and the "gathering of all related genres": SELECT items.*, GROUP_CONCAT(genres.id ORDER BY genres.id) AS genreIds, GROUP_CONCAT(genres.genre ORDER BY genres.id) AS genres FROM items LEFT JOIN itemGenres ON items.id = itemGenres.itemId LEFT JOIN genres ON ...


0

I received this error because of another reason which I didn't see online, so I'm posting the problem and solution here. This can happen when trying to modify an xml column by inserting a very large text. For example... update MyTable set XmlColumn.modify(' insert Very large text here... after (/RootNode/Node)[1]') where Id = 1 To fix it, you can use ...


1

If you want to do it in pure MySQL SP, you could use this: CREATE TABLE `temp_details` ( `t_schema` varchar(45) NOT NULL, `t_table` varchar(45) NOT NULL, `t_field` varchar(45) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ## Procedure for search in all fields of all databases DELIMITER $$ #Script to loop through all ...


1

This is a grueling problem and I have grueling suggestions SUGGESTION #1: Use information_schema See my old posts on how to go about doing this Jul 19, 2012 : Query to find and replace text in all tables and fields of a mysql db Mar 19, 2013 : How to search whole MySQL database for a particular string SUGGESTION #2: Use mysqldump You should be able to ...



Top 50 recent answers are included