Post reply

error of sintax

  • April 6, 2009 at 3:10 am

    #73628

    Hi - I have a table.

    This table as two columns:

    (codctb INT

    NIFCTB varchar(20))

    i want to create a script (insert script) based on a select of this table:

    this select:

    generates an error: can someone please help?

    select 'insert into exportacao_diferenca values ('+codctb+','''+nifctb+''')' from TABELA_AUXILIAR_COMPARACAO

    error:

    "Syntax error converting the varchar value 'insert into exportacao_diferenca values (' to a column of data type int."

    tks,

    Pedro

  • April 6, 2009 at 3:22 am

    #972366

    select 'insert into exportacao_diferenca values ('+CAST(codctb AS VARCHAR(10))+','''+nifctb+''')' from TABELA_AUXILIAR_COMPARACAO

    May I ask what you're trying to achieve here? There are easier ways to insert the values from one table into another. Or is there some other reason for this?

    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

  • April 6, 2009 at 3:27 am

    #972369

    Hi,

    Yap, there's other reason.

    With the result of the select i will trie to produce a txt file (script) that as insert statements.

    This script will be sent to other database (on other country) to insert this rows that are on the Script.

    tks,

    Pedro

  • April 6, 2009 at 5:03 am

    #972417

    Hi

    Just for information, if possible you may consider to use BCP.

    Nevertheless. Which data type are your codctb and nifctb? It seems that your codctb is INT, so you have to use CONVERT.

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • April 6, 2009 at 5:08 am

    #972419

    pedro.ribeiro (4/6/2009)

    Hi,

    Yap, there's other reason.

    With the result of the select i will trie to produce a txt file (script) that as insert statements.

    This script will be sent to other database (on other country) to insert this rows that are on the Script.

    tks,

    Pedro

    Hi Pedro

    Would it be easier to distribute a table, with one instruction, rather than a file with one instruction per row?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • April 6, 2009 at 5:15 am

    #972423

    well the reason for the error is one of the columns in an integer; you have to explicitly convert an integer to varchar when you are creating a string like that:

    select 'insert into exportacao_diferenca values (' + CONVERT(VARCHAR,codctb) + ','''+nifctb+''')' from TABELA_AUXILIAR_COMPARACAO

    if you are building scripts like this for more tables, you might want to use Narayana Vyas Kondreddi's excellent procedure sp_generate_inserts from http://vyaskn.tripod.com

    it's full feature and is a great tool for this kind of stuff.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 6, 2009 at 5:29 am

    #972430

    thank you very much.

    I have converted to varchar and it worked.

    As to the app that you recomended, i will take a look.

    Once again thank you very much to you all.

    Pedro

  • April 6, 2009 at 5:34 am

    #972432

    ... or have a look to the script I populated here 😉

    Edited: Oops, sorry it's SQL2k Forum. So this script might not work. My apologize!

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • April 6, 2009 at 6:07 am

    #972442

    Lowell (4/6/2009)

    well the reason for the error is one of the columns in an integer; you have to explicitly convert an integer to varchar when you are creating a string like that:

    select 'insert into exportacao_diferenca values (' + CONVERT(VARCHAR,codctb) + ','''+nifctb+''')' from TABELA_AUXILIAR_COMPARACAO

    Be careful converting (or casting) to varchar without specifying the length. It's not an issue here, but can be in other cases. Do you know what the default length is for casting to a varchar?

    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

  • April 6, 2009 at 6:15 am

    #972447

    Goo point;

    i know the default length is varchar(30), whenever i convert something numeric to varchar, i've always left it in the simplified form and allowed the default conversion to work, since i don't typically work with 30 digit numbers, but it's certainly a better/best practice to explicitly set the length, thanks!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 6, 2009 at 6:17 am

    #972448

    Yap, it's 20.

    I will convert and add the lenght.

    Thank you,

    Pedro

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply