Something like this?

name LIKE '[a-z][a-z][a-z]%'

Nevermind, I missed something.

Now I'm lost, I thought you just wanted to improve the query that ccame after "--Derive list of tables which contain all three column". Now I'm not sure what you're...

What about using INFORMATION_SCHEMA.COLUMNS view? it will give you the names of columns and tables.

Another option would be to use OBJECT_NAME() instead of querying sys.tables. 😉

steven.ensslen (11/6/2013)

---

If I had to guess, the problem is that a single DELETE is too large to fit in the transaction log. DELETE is the wrong tool for the...

Maybe something like this could perform better, based on the following article:

http://www.sqlservercentral.com/articles/T-SQL/91724/

CREATE FUNCTION [dbo].[DateFormattedByMask]

(

@InputDate DATETIME,

@Mask NVARCHAR(80)

)

RETURNS TABLE...

Check this article, as it might help you to improve performance.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

It doesn't matter where the DTS is saved, because the connections aren't changed to the prod server.

What are you afraid of?

CONVERT won't help with the string as is. You need to format it a little bit.

WITH SampleData( cid,eid,checktime,status,bid) AS(

SELECT 1,'25850','201310311133','01','2' UNION ALL SELECT

1,'17018','201310311135','01','2' UNION ALL SELECT

1,'25850','201310311137','01','2')

SELECT cid,

eid,

CONVERT( datetime, STUFF( STUFF(checktime,...

Tej_7342 (11/5/2013)

---

There are 2.2 billion records in the table and we want to only keep 2 weeks data, as of now we are lagging behind so we want to delete...

Please don't send private messages, you limit the help you can get.

Tej_7342 (11/5/2013)

---

It looks good, but can this be modified to be generic instead of hard-coded. Also the problem is...

What's the output you're getting? There are millions of reasons, but the error messages can help us to identify what's happening.

Something like this might work, but you should test the correct numbers depending on your server.

DECLARE @i int = 1

WHILE @i > 0 ...

Be careful on using recursive CTEs to count as you did. Those won't perform fine and might be worse than a cursor as stated on this article: http://www.sqlservercentral.com/articles/T-SQL/74118/

Another point is...

Yes, there's a difference and you're duplicating rows on the second statement due to the join.

I suggest you to change your joins to ANSI-92 standard (using JOIN keywords).

Why do you want a loop if you can do a very easy update without even needing a CASE?

UPDATE tmp_MT_SE_Upload

SET page21.serial = T.SERIAL + (Row_ID - 1)

FROM (SELECT TOP 1...