tsql

TSQL Merge statement hanging

I currently run the following statement and the query never completes. I am unsure why as I use the same query with other source tables and it completes very quickly, this source table is not bigger. merge into dbo.Tickets_Amends as target using dbo.Tickets_Healthchecks_parentlesstickets as source on (target.fault_ID = source.fa...

Tying user ids in a table to meaningful values in another table in SQL Server

I have the following table, let's call it People: id | customer | shipper | buyer | 1 | 1 | 2 | 3 | 2 | 4 | 5 | 6 | 3 | 7 | 8 | 9 | And this ties in to a User_ID table: id | Name | 1 | Stan | 2 | Brian | 3 | Amy | What's the best practice f...

How to convert query from phpMyAdmin SQL Dump to an sql server legible query

Hi all, I have undertaken a small project which already evolved a current database. The application was written in php and the database was mysql. I am rewriting the application, yet I still need to maintain the database's structure as well as data. I have received an sql dump file. When I try running it in sql server management studi...

sql where clause in select statement issue

Hello everyone, I am using SQL Server 2008 Enterprise with Windows Server 2008 Enterprise. I have a database table called "Book", which has many columns and three columns are important in this question, they are Author, varchar; Country, varchar; Domain, varchar. I want to write a store procedure with the following logics, but I do ...

TSQL - Max or Top Date on an unnormalized table

I have the following table: Table: UserName Userid User UserUpdate 1 Dan 1/1/2005 1 Dan 1/1/2007 1 Dan 1/1/2009 2 Pam 1/1/2005 2 Pam 1/1/2006 2 Pam 1/1/2008 3 Sam 1/1/2008 3 Sam 1/1/200...

TSQL Select Max

Userid FirstName LastName UserUpdate 1 Dan Kramer 1/1/2005 1 Dan Kramer 1/1/2007 1 Dan Kramer 1/1/2009 2 Pamella Slattery 1/1/2005 2 Pam Slattery 1/1/2006 2 Pam Slattery 1/1/2008 3 ...

TSQL Like regular expression

Hello I'm trying to select only entries in the following format from a varchar field. [any number of zeros][a hyphen or not][any number of numbers] some samples of what I want would be... 000000000007975 000000000-58628 123421423890347 But not 00000--18945489 00000000000012B SELECT Field FROM table WHERE Field LIKE ??? ...

Updating one table from another without a one to one relationship.

I am trying to populate a date table which contains every date for the next 35 years with information regarding each day. My ERP system has an accounting years table (GLRULE) which specifies the accounting periods for each specific year since many companies do not work on Calendar Months. The GLRule table contains one record for each...

IsDate in TSQL Issue

Hi there I have a bug on our app and it points that if there is a date format like this: SELECT IsDate('4:27:01') -- ok SELECT IsDate('25:01') ---> not ok SELECT IsDate('23:01') ---> ok In our data, sometimes the format only 5 characters which means it's only for minutes and seconds. What is best way to handle this? ...

inserting a node into SQL 2008 xml datatype... checking if it exists first

Hi, I am reading through a plethora of articles at the moment to try to assist me.. just seems so many options and cannot seem to find a clean solution.. it probably is very basic so apologies in advance! So I have an XML field in SQL 2008. It basically contains something like: <root><id>1</id><id>4</id></root> and so on... What I ...

SQL Query Costing, aggregating a view is faster?

I have a table, Sheet1$ that contains 616 records. I have another table, Rates$ that contains 47880 records. Rates contains a response rate for a given record in the sheet for 90 days from a mailing date. Within all 90 days of a records Rates relation the total response is ALWAYS 1 (100%) Example: Sheet1$: Record 1, 1000 QTY, 5% R...

tsql to get data files growth type in SQL 2000

Hi, I want to know the growth type (%age or MB) for database files on SQL server 2000. I Used sys.database_files files on SQl 2005 to get this information. I tried using sysfiles on SQl 2000 for this, but it wasn't good enough. Can anyone help please? Regards Manjot ...

sql set operation and where clause

Hi all Is it possible to have a query like this (I'm getting error) : SELECT ColumnA FROM Table1 EXCEPT SELECT ColumnB FROM Table2 WHERE Table1.ColumnC = Table2.ColumnC SQL can't bind the Table1.colmnC in my where clause . is there any way to run this query ? or another way to get the same result ? I know that I can do this using tem...

Invalid column name error in WHERE clause, column selected with CASE

I have a (rather complicated) SQL statement where I select data from lots of different tables, and to cope with a bad legacy data structure, I have a couple of custom columns that get their values based on values from other columns. I have currently solved this with CASE statements: SELECT ..., CASE channel WHEN 1 T...

SQL: Normalizing Code / Export - Import

I've decided to rewrite a database I have that is poorly normalized. I've created the new database but now need to move data from the old database into the new one. I'm not sure exactly how to accomplish though. For example, in the old database I have a webDorms table that looks like this: id (PK, int) room_type (varchar) description (...

T-SQL for changing data from one table and insert into another table

My base table is like: ColumnA|ColumnB --------------- A | C1 A | C2 A | C3 B | C1 B | C3 C | C4 I want to read records from the base table and write it into the below table: ColumnA | C1 | C2 | C3 | C4 ---------------------------- A | Y | Y | Y | N B | Y | N | Y | N C | N ...

How to keep a rolling checksum in SQL?

I am trying to keep a rolling checksum to account for order, so take the previous 'checksum' and xor it with the current one and generate a new checksum. Name Checksum Rolling Checksum ------ ----------- ----------------- foo 11829231 11829231 bar 27380135 checksum(27380135 ^ 11829231) = 93291803 baz ...

Insert record only if record does not already exist in table

I'm wondering if there is a way to insert a record into a table only if the table does not already contain that record? Is there a query that will do this, or will I need a stored procedure? ...

T-SQL Outer Join doesn't work

I've got a query I wrote using a JOIN to display a summary using the part's Serial_Number. Obviously, it does not work! Using the direct search here: select Serial_Number, Op_ID, Date_Time as 'DecayDate', DECAY.System_ID AS 'DecayID', Test_Result as 'DecayResult' from Test_Results where serial_number='CP21295 1006 09' and system...

UPDATE records in a table except the TOP 1 record

Hi All, I have business scenario as We will get all the data to the database including the duplicates If we have any duplicated in a table take the most recent record from duplicates on perticular key by making all the remaining deplicate records flag to 'X' While processing to the next level filter the extraction by flag != 'X' so we...