Are the posted questions getting worse?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 12, 2017 at 12:48 pm

  #1942389

  jonathan.crawford - Friday, May 12, 2017 12:34 PM

  ...and now I'm wondering if I can't buy a Leap controller and a wand and program some shortcuts so I can code like a wizard....already use Dasher (http://www.inference.phy.cam.ac.uk/dasher/) to save me keystrokes, wand would work like a charm as a controller for the mouse. Audio input added would really make it snap....Expelliarmus! (all users lose privileges)

  I know that wii controllers were able to be used as mouse pointers. Or you could go straight for the wand. https://www.amazon.com/HARRY-POTTER-Remote-Control-Wand/dp/B00FXMDRZK

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• WayneS

  SSC Guru

  Points: 95460

  More actions

  May 12, 2017 at 4:56 pm

  #1942411

  Luis Cazares - Friday, May 12, 2017 12:35 PM

  jonathan.crawford - Friday, May 12, 2017 12:30 PM

  My code from today included the following comments:
  
  

  --copy table values into new table, just to get columns and datatypes matching

  SELECT  *,
          CONVERT(VARCHAR(60), '') AS FileName,
          GETDATE() AS FileDate
  INTO    dbo.myTable
  

  FROM dbo.otherTable
  WHERE 1=2

  --Avada Ke-data --get rid of the one row and get the data we actually want

  --TRUNCATE TABLE dbo.myTable

  --hominem revelio

  --ALTER TABLE dbo.myTable ADD

  --[FileName] varchar(60),[FileDate] datetime;

  --presto populato

  INSERT INTO dbo.myTable

  <some stuff>


  I amuse myself.

  There, fixed that for you.

  There, fixed that for you.
  But why not just change that last insert into a SELECT INTO and avoid all of this in the first place?

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• jonathan.crawford

  SSCertifiable

  Points: 6613

  More actions

  May 12, 2017 at 9:49 pm

  #1942419

  running it across servers and need to use OPENQUERY() to make it work in a reasonable timeframe. so I'm cheating, pulling one record over to create a local mirrored table, then going to get what I need and inserting it.

  -------------------------------------------------------------------------------------------------------------------------------------
  Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 13, 2017 at 11:44 am

  #1942435

  jonathan.crawford - Friday, May 12, 2017 12:34 PM

  ...and now I'm wondering if I can't buy a Leap controller and a wand and program some shortcuts so I can code like a wizard....already use Dasher (http://www.inference.phy.cam.ac.uk/dasher/) to save me keystrokes, wand would work like a charm as a controller for the mouse. Audio input added would really make it snap....Expelliarmus! (all users lose privileges)

  I persuaded one of the managers to buy this for me to play with. 🙂
  https://www.emotiv.com/insight/

  I love my company.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• drew.allen

  SSC Guru

  Points: 76997

  More actions

  May 13, 2017 at 1:00 pm

  #1942440

  jonathan.crawford - Friday, May 12, 2017 12:30 PM

  My code from today included the following comments:
  
  

  --copy table values into new table, just to get columns and datatypes matching

  SELECT TOP 1 * INTO dbo.myTable

  FROM dbo.otherTable

  --Avada Ke-data --get rid of the one row and get the data we actually want

  TRUNCATE TABLE dbo.myTable

  --hominem revelio

  ALTER TABLE dbo.myTable ADD

  [FileName] varchar(60),[FileDate] datetime;

  --presto populato

  INSERT INTO dbo.myTable

  <some stuff>


  I amuse myself.

  It's also inefficient.  Try the following instead of inserting and then truncating
  --copy table values into new table, just to get columns and datatypes matching
SELECT TOP 0 * INTO dbo.myTable
FROM dbo.otherTable


  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 13, 2017 at 5:01 pm

  #1942447

  drew.allen - Saturday, May 13, 2017 1:00 PM

  jonathan.crawford - Friday, May 12, 2017 12:30 PM

  My code from today included the following comments:
  
  

  --copy table values into new table, just to get columns and datatypes matching

  SELECT TOP 1 * INTO dbo.myTable

  FROM dbo.otherTable

  --Avada Ke-data --get rid of the one row and get the data we actually want

  TRUNCATE TABLE dbo.myTable

  --hominem revelio

  ALTER TABLE dbo.myTable ADD

  [FileName] varchar(60),[FileDate] datetime;

  --presto populato

  INSERT INTO dbo.myTable

  <some stuff>


  I amuse myself.

  It's also inefficient.  Try the following instead of inserting and then truncating
  --copy table values into new table, just to get columns and datatypes matching
SELECT TOP 0 * INTO dbo.myTable
FROM dbo.otherTable


  Or

  SELECT * INTO dbo.myTable
FROM dbo.otherTable
WHERE 1=0;

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  May 14, 2017 at 6:40 pm

  #1942468

  GilaMonster - Saturday, May 13, 2017 5:01 PM

  drew.allen - Saturday, May 13, 2017 1:00 PM

  jonathan.crawford - Friday, May 12, 2017 12:30 PM

  My code from today included the following comments:
  
  

  --copy table values into new table, just to get columns and datatypes matching

  SELECT TOP 1 * INTO dbo.myTable

  FROM dbo.otherTable

  --Avada Ke-data --get rid of the one row and get the data we actually want

  TRUNCATE TABLE dbo.myTable

  --hominem revelio

  ALTER TABLE dbo.myTable ADD

  [FileName] varchar(60),[FileDate] datetime;

  --presto populato

  INSERT INTO dbo.myTable

  <some stuff>


  I amuse myself.

  It's also inefficient.  Try the following instead of inserting and then truncating
  --copy table values into new table, just to get columns and datatypes matching
SELECT TOP 0 * INTO dbo.myTable
FROM dbo.otherTable


  Or

  SELECT * INTO dbo.myTable
FROM dbo.otherTable
WHERE 1=0;

  Using WHERE 0 = 1 is the one I've always used.  I've never thought of TOP 0 before.  Nice.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• jasona.work

  SSC Guru

  Points: 50118

  More actions

  May 15, 2017 at 6:08 am

  #1942515

  Ed Wagner - Sunday, May 14, 2017 6:40 PM

  GilaMonster - Saturday, May 13, 2017 5:01 PM

  drew.allen - Saturday, May 13, 2017 1:00 PM

  jonathan.crawford - Friday, May 12, 2017 12:30 PM

  My code from today included the following comments:
  
  

  --copy table values into new table, just to get columns and datatypes matching

  SELECT TOP 1 * INTO dbo.myTable

  FROM dbo.otherTable

  --Avada Ke-data --get rid of the one row and get the data we actually want

  TRUNCATE TABLE dbo.myTable

  --hominem revelio

  ALTER TABLE dbo.myTable ADD

  [FileName] varchar(60),[FileDate] datetime;

  --presto populato

  INSERT INTO dbo.myTable

  <some stuff>


  I amuse myself.

  It's also inefficient.  Try the following instead of inserting and then truncating
  --copy table values into new table, just to get columns and datatypes matching
SELECT TOP 0 * INTO dbo.myTable
FROM dbo.otherTable


  Or

  SELECT * INTO dbo.myTable
FROM dbo.otherTable
WHERE 1=0;

  Using WHERE 0 = 1 is the one I've always used.  I've never thought of TOP 0 before.  Nice.

  Hmm, I'm thinking we need an article on the performance differences of the four methods...
  Select top 1 into / truncate
  Select top 0 into
  select * into where 1 = 0
  select * into where 0 = 1
  :hehe:

• Jeff Moden

  SSC Guru

  Points: 1004414

  More actions

  May 15, 2017 at 6:42 am

  #1942524

  jasona.work - Monday, May 15, 2017 6:08 AM

  Ed Wagner - Sunday, May 14, 2017 6:40 PM

  GilaMonster - Saturday, May 13, 2017 5:01 PM

  drew.allen - Saturday, May 13, 2017 1:00 PM

  jonathan.crawford - Friday, May 12, 2017 12:30 PM

  My code from today included the following comments:
  
  

  --copy table values into new table, just to get columns and datatypes matching

  SELECT TOP 1 * INTO dbo.myTable

  FROM dbo.otherTable

  --Avada Ke-data --get rid of the one row and get the data we actually want

  TRUNCATE TABLE dbo.myTable

  --hominem revelio

  ALTER TABLE dbo.myTable ADD

  [FileName] varchar(60),[FileDate] datetime;

  --presto populato

  INSERT INTO dbo.myTable

  <some stuff>


  I amuse myself.

  It's also inefficient.  Try the following instead of inserting and then truncating
  --copy table values into new table, just to get columns and datatypes matching
SELECT TOP 0 * INTO dbo.myTable
FROM dbo.otherTable


  Or

  SELECT * INTO dbo.myTable
FROM dbo.otherTable
WHERE 1=0;

  Using WHERE 0 = 1 is the one I've always used.  I've never thought of TOP 0 before.  Nice.

  Hmm, I'm thinking we need an article on the performance differences of the four methods...
  Select top 1 into / truncate
  Select top 0 into
  select * into where 1 = 0
  select * into where 0 = 1
  :hehe:

  BWAAA-HAAAA!!! Add a FROM clause to those and you just wrote the article! 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 15, 2017 at 6:56 am

  #1942530

  jasona.work - Monday, May 15, 2017 6:08 AM

  Hmm, I'm thinking we need an article on the performance differences of the four methods...
  Select top 1 into / truncate
  Select top 0 into
  select * into where 1 = 0
  select * into where 0 = 1
  :hehe:

  My guess is that the performance difference would be in the insert if minimal logging is available.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Louis Hillebrand

  SSCarpal Tunnel

  Points: 4982

  More actions

  May 15, 2017 at 7:09 am

  #1942537

  Instead of adding the extra columns later :
  
SELECT TOP (0) 
       *,
       [FileName] = CAST(null AS varchar(60) ),
       [FileDate] = CAST(null AS datetime )
INTO dbo.myTable
FROM dbo.otherTable  

  

• David Burrows

  SSC Guru

  Points: 65143

  More actions

  May 15, 2017 at 8:24 am

  #1942566

  Louis Hillebrand - Monday, May 15, 2017 7:09 AM

  Instead of adding the extra columns later :
  
SELECT TOP (0) 
       *,
       [FileName] = CAST(null AS varchar(60) ),
       [FileDate] = CAST(null AS datetime )
INTO dbo.myTable
FROM dbo.otherTable  

  

  A bit limiting for filename, use varchar(max) instead and then you don't have to worry about it 😉

  Far away is close at hand in the images of elsewhere.
  Anon.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736494

  More actions

  May 15, 2017 at 8:30 am

  #1942570

  John Mitchell-245523 - Thursday, May 11, 2017 4:36 AM

  Did anybody else who doesn't live in "Washington D.C., the fifty (50) United States of America, and the Commonwealth of Puerto Rico" win a $10 Starbucks voucher in last month's SQL Clone Trivia Quiz?  Any recommendations on where I should come to redeem it, or indeed how I can get a flight cheap enough to make it worth my while?

  John

  Do the cards not work outside the US? I've bought Starbucks cards in the US and had them work in the UK and Germany.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 15, 2017 at 8:44 am

  #1942575

  Thanks Steve - I'll give it a try, in that case.  I assume they must therefore convert dollars to pounds at point of sale.  Having said that, it's not an actual card - it's an e-mail with a barcode on it.

  Thanks also to everybody else for your ideas!

  John

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736494

  More actions

  May 15, 2017 at 8:47 am

  #1942577

  John Mitchell-245523 - Monday, May 15, 2017 8:44 AM

  Thanks Steve - I'll give it a try, in that case.  I assume they must therefore convert dollars to pounds at point of sale.  Having said that, it's not an actual card - it's an e-mail with a barcode on it.

  Thanks also to everybody else for your ideas!

  John

  Give it a go. Starbucks is pretty on top of IT. I was amazed when it worked, and they convert the rate that day, so works fine.