Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by PerlyGates

  1. PerlyGates

    Filling a column with random dates ?

    Thanks folks. What I didn't reveal (because I didn't think it relevant) was that I was running that code on SQL SERVER 2005 Express Edition SP2 (9.00.1399.06). So if I run the following SELECT on SQL SERVER Express ... SELECT TOP 5 CASE WHEN RAND() < .5 THEN RAND() - 1...
  2. PerlyGates

    Filling a column with random dates ?

    No - thankyou.
  3. PerlyGates

    Filling a column with random dates ?

    Hi George >>When I run your code, all the dates are the same. Is that on 2000?? I've just ran it on 2000 and all the dates are indeed the same. But on 2005 they are all different - I didn't know or would have thought there would have been a difference. Or is it to do with default settings...
  4. PerlyGates

    Filling a column with random dates ?

    The following will give you a range between 0.0 and 1.0 SELECT RAND() The following will give you a range between 0.0 and 10000 SELECT RAND() * 10000 The following will add up to 10000 days (~27 years) onto the current date SELECT GETDATE() + (RAND() * 10000) To subtract dates you...
  5. PerlyGates

    Trouble with a SELECT with DISTINCT requirement

    Is this what you want? DECLARE @tv1 TABLE ( col_A varchar(10), col_B varchar(10), col_C int ) INSERT INTO @tv1 SELECT '123' ,'RED', 4 UNION ALL SELECT '234' ,'BLUE', 2 UNION ALL SELECT '123' ,'RED', 7 UNION ALL SELECT '345' ,'GREEN', 1 UNION ALL SELECT...
  6. PerlyGates

    Update Memo field with many records

    Hi Mike As kaht has so kindly supplied a ready-made solution I'd go with that (assuming SQLSister's suggestion is a non-runner). Not only is kaht's iterative solution easier to read and understand it will be "blisteringingly" faster than my donkey code. Apologies for leading you down a bit of...
  7. PerlyGates

    Update Memo field with many records

    ... yeah I was afraid of that - whats the largest number of occurrences of any one ActBrch? How many rows are in the tables (as they seem pretty big - banking data and all that)? As I outlined at the outset it is quite probable that this query will just be too slow to make it anyway feasible...
  8. PerlyGates

    Update Memo field with many records

    Try this ... WITH t2( ActBrch, myCount,[SVCID], myLength ) -- Populate you're CTE t2 AS ( SELECT ActBrch ,COUNT(*) OVER (PARTITION BY ActBrch) ,CAST( [SVCID] AS VARCHAR(100)) ,1 FROM [400_Metafile].[dbo].[yad_srvmast] UNION ALL SELECT...
  9. PerlyGates

    Update Memo field with many records

    Hi Mike - if you want to post up all field names/table names etc. (or abbreviations/aliases of in the interests of privacy) together with their datatype definitions e.g. char,varchar,int etc I'll plug them into my code for you and re-post.
  10. PerlyGates

    Update Memo field with many records

    Hi Mike The following will work using recursion - however BE WARNED - it will be VERY slow (even the initial population of the CTE - common table expression - not to mention the actual UPDATE ) and its not fully tested. Bare in mind that it's not as complicated as it looks (famous last words)...
  11. PerlyGates

    SELECT INTO cross server

    nickdel - yes you do have to set up the link to the other server - sorry my fault but I thought that was obvious. And yes you'll need permissions. TysonLPrice - you're query is the wrong way around it should be (assuming you have the link to the other server set up): SELECT * INTO newTABLE...
  12. PerlyGates

    SELECT INTO cross server

    Yes you can. Qualify your server name with square brackets e.g. SELECT * INTO newTable FROM [Server].database.owener.table
  13. PerlyGates

    searching column name in databasebase objects

    This should get you started ... DECLARE @myColumn varchar(50) SET @myColumn = 'id' SELECT OBJECT_NAME(c.id) AS ObjectName ,CASE type WHEN 'P' THEN 'Stored Proc' WHEN 'RF' THEN 'Replication Filter Stored Proc' WHEN 'X' THEN 'Extended Stored Proc'...
  14. PerlyGates

    Update Memo field with many records

    Hi Mike - are you using 2000 or 2005? -PG
  15. PerlyGates

    Date selecting with date from 1. jan 1900

    Running the following: INSERT INTO @WM_MultiCenterIntegration SELECT 'MC00068668', 1093967711 UNION ALL SELECT 'MC00069194', 1094047234 UNION ALL SELECT 'MC00069227', 1094067911 UNION ALL SELECT 'MC00069300', 1094103666 UNION ALL SELECT 'MC00069384', 1094128248 select alarm_ID...
See more

Part and Inventory Search

Back
Top