error of sintax

Question

error of sintax

pedro.ribeiro

Hall of Fame

Points: 3201
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

pedro.ribeiro Hall of Fame Points: 3201 More actions · Answer 2

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

Lowell SSC Guru Points: 323518 More actions · Answer 5

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!

pedro.ribeiro Hall of Fame Points: 3201 More actions · Answer 6

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

... 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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

Lowell SSC Guru Points: 323518 More actions · Answer 9

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!

pedro.ribeiro Hall of Fame Points: 3201 More actions · Answer 10

Yap, it's 20.

I will convert and add the lenght.

Thank you,

Pedro