I am trying to figure out the best Transact-SQL to transform non-relational tables into tables that are more relational, and support foreign keys. Suppose I have a table FRUIT Id Name USState 1 Apple Washington 2 Pineapple Hawaii 3 Orange Florida 4 Peach Georgia etc I want the ...
Description: I have a column(EmailsAdress) on a table(BusinessUsers) on my databases that stores email address. Problem: Some of the rows of data have a dot at the beginning of this column for example [email protected] (The dot i want to get rid of is the dot just before the charater j in jane) Some of the rows of data have a...
Hi, I have SQL 2005 databases. I have deleted a row from one of them and want to get it back from another database that was a backup of the row. How do isnert it while preserving its id primary key identity field? Can you give TSQL to do this assume databases are called "tbrPdata" and "tbr0910" which is the backup? Malcolm ...
WITH LatestJob AS (SELECT * FROM(SELECT aId , Position , StartDate , Enddate ,SpecializationId ,ROW_NUMBER() OVER (PARTITION BY aId ORDER BY CASE WHEN Enddate IS NULL THEN 0 ELSE 1 END ASC, (CAST(Enddate as datetime)) DESC) AS RN FROM EmploymentDetails ed) E WHERE RN=1 ) ...
is there a way to make a query which DB the Stored Procedure is located? I got this bunch of Databases doing a lot of Cross DB query from each other, now I got lost with 1 stored procedure and I just want to look from which DB it is located. I want to make a script that will return the DB name of where that stored procedure is located. ...
Is there a quick way that I can find which stored procedures run to a particular table in my database? The database is very large with lots of tables and SPROCS.... ...
Currently I'm trying to automatically generate a create script for all my SQL jobs of a MS SQL2005 Server. One method I found was done manually http://msdn.microsoft.com/en-us/library/ms191450.aspx A second method I found could be done automatically but I don't have direct access to the SQL server. http://relatedterms.com/thread/19166...
Hi all, I have a table (AmenityData) of data and a column of this table contains postalsectors e.g. E14 7 I also have an Excel spreadsheet with a list of postal districts e.g. E14 I need to get all the data out of the AmenityData table where the postal districts are LIKE the postal sectors e.g. WHERE [PostalDistricts] + '%' LIKE [Post...
Hi, ne need to convert the following tsql function code into a pgsql function and I have absolutely no idea how: BEGIN DECLARE @StartDate DATETIME DECLARE @ResultDate DATETIME SET @StartDate = CONVERT(DATETIME, 0) SET @ResultDate = CASE @Type WHEN 0 THEN DATEADD(mi, FLOOR(DATEDIFF(mi, @StartDate, @Date) / CAST(@Interva...
I want to get list of all database users with specified role. Role is a parameter for stored procedure or function. Somethinf like a select statement with user name and its role. +============+========== | User name | Role | +============+========== MS SQL Server 2008 ...
I have two databases that are similar, but not the same. DB 1 is the old one and DB2 is the updated one with lots of new tables, columns, procs, constraint etc. I need to write an update script in order to update DB1 database. These databases have lots of tables and stored procedures. Is there any way to get the differences in two datab...
I am having issues executing a SQL statement from a stored proc having single quotes. Here is my query from a stored procedure that I am executing. EXEC('UPDATE myTABLE SET myCOLUMN = (SELECT Replace('OSINGLEQUOTEJOHN DOE','SINGLEQUOTE','''')') I am trying to update table "myTABLE" column "myCOLUMN" with a value "O'John Doe"...
I need to create a stored procedure one time and in one place only on my database server, but be able to run it from any database. However, I don't want to have to type database.schema.procedure_name every time I run it. I'd like to be able to use it like the built in procedures: sp_... is there any way to do this? here is the proced...
Im a little confused about whether should I use a nested Subquery Or JOINS with distinct !! which one of these will perform better and faster ? any suggestions to do this query without distinct ?!? SELECT distinct TOP(20) e.*, u1.UserName As Sender, u2.UserName As Receiver, u1.Avatar AS SenderPic FROM Friends f INNER JOIN Users u ON...
I'm writing stored procs that are being called by a legacy system. One of the constraints of the legacy system is that there must be at least one row in the single result set returned from the stored proc. The standard is to return a zero in the first column (yes, I know!). The obvious way to achieve this is create a temp table, put the...
I am migrating data from one table to a new table. The old table uses FLOAT, and in the new table I am using DECIMAL as the field attribute. I am using the following statement which worked fine: CAST(OLD_COLUMN_NAME as DECIMAL(9,2) AS 'NEW_COLUMN_NAME' that works like a charm until I hit the bump in the road. The old data is defined as...
Hi, I came into problem that I need to display the top 3 records for each aId in a comma separated string in one column (for eg. aId=151 ghghg,ghh, rgtg ) instead of the below result. Can anyone help me please? Expertise ghghg ghh rgtg rtrt ghgh tyuyu fgfg yuu dfdf gtyy dfdf df ssd dfd dfdf fd dfdf d...
Hi all... I want only Hr and Min in time format so i did select convert(varchar(20),GETDATE(),108) ===>> output is "12:57:54" but i want only "12:57" this much so how i will do that in sql server ...
Hi all.. I want to fetch some result from table depending on time range so i did SELECT * FROM TABLE where convert(varchar(10),CountryTime,108) between '15:00' and '16:00' CountryTime- is of varchar type.. but iam not getting corect output plz suggets.. ...
I have two tables, both with the same columns. The first table contains original source data, the second contains revisions to that data OR values inserted where no original data was present. The tables are to be used in a view where if the data has a revision that is shown - if not then the original source data is shown. Normally thi...