syntax error???

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  January 11, 2012 at 11:55 am

  #266415

  Hi,

  Getting a syntax error on the following..

  INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)

  SELECT TAG_NAME = 'CUST_AGE', TAG_DATA = (SELECT FLOOR(DATEDIFF(day, BDate, CURRENT_TIMESTAMP) / 365.25))FROM client

  WHERE Client.OID =@CLIENT_OID);

  any help would be great..

  Thanks

  Joe

• stephen99999

  Ten Centuries

  Points: 1197

  More actions

  January 11, 2012 at 12:04 pm

  #1432035

  jbalbo (1/11/2012)

  ---

  Hi,

  Getting a syntax error on the following..

  INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)

  SELECT TAG_NAME = 'CUST_AGE', TAG_DATA = (SELECT FLOOR(DATEDIFF(day, BDate, CURRENT_TIMESTAMP) / 365.25))FROM client

  WHERE Client.OID =@CLIENT_OID);

  any help would be great..

  Thanks

  Joe

  Not really a need to do a sub-select query:

  INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)

  SELECT 'CUST_AGE', FLOOR(DATEDIFF(day, BDate, GETDATE()) / 365.25)

  FROM client

  WHERE OID =@CLIENT_OID;

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  January 11, 2012 at 12:14 pm

  #1432041

  thanks that works great...

  hate to be a pain I really am new, can u explain why that is, just so I know?

  Thanks

• stephen99999

  Ten Centuries

  Points: 1197

  More actions

  January 11, 2012 at 12:23 pm

  #1432048

  jbalbo (1/11/2012)

  ---

  thanks that works great...

  hate to be a pain I really am new, can u explain why that is, just so I know?

  Thanks

  lol I am no expert either, any even the best of the best will never claim to be. Let me try, but if it is not a good description hopefully one of the better posters will join in and give me a hand

  --The first thing is that you already declared the order in which data will be inserted into the table.

  --So no need to declaratively state what the value is for each column.

  --(originally you had TAG_NAME='CUST_AGE', etc, which isn't necessary)

  INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)

  --Next, when inserting data you can INSERT specific values or from tables like a regular SELECT statement

  SELECT 'CUST_AGE', FLOOR(DATEDIFF(day, BDate, GETDATE()) / 365.25)

  FROM client

  --To see how this works remove your where clause

  --If you did not have the where clause below, it would insert a row into @TEMPTABLE

  --for every BDate value in client table

  WHERE OID =@CLIENT_OID;

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  January 11, 2012 at 12:40 pm

  #1432061

  thats a good explaination..

  Im putting it on my Fridge!!

• Brandie Tarvin

  SSC Guru

  Points: 173061

  More actions

  January 13, 2012 at 6:10 am

  #1433177

  jbalbo (1/11/2012)

  ---

  Hi,

  Getting a syntax error on the following..

  INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)

  SELECT TAG_NAME = 'CUST_AGE',

  TAG_DATA = (SELECT FLOOR(DATEDIFF(day, BDate, CURRENT_TIMESTAMP) / 365.25))

  FROM client

  WHERE Client.OID =@CLIENT_OID);

  To answer your original question as to why you got the Syntax error to begin with.. Count your parenthesis. You should have one left for each right. At the very end of your code, you have an extra right.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  January 13, 2012 at 8:37 am

  #1433296

  WOW i NEVER PICKED THAT UP!!

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  January 13, 2012 at 8:37 am

  #1433298

  Wow never picked it up!!

• Brandie Tarvin

  SSC Guru

  Points: 173061

  More actions

  January 13, 2012 at 9:14 am

  #1433338

  jbalbo (1/13/2012)

  ---

  Wow never picked it up!!

  It's probably the most common sql server mistake ever made and everyone, even the experts, make it constantly. Just remember: If everything else looks correct, count the parens.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  January 13, 2012 at 9:32 am

  #1433377

  thanks..

  I got a new one Im tottaly confused about stay tuned!!! lol