SQL 2005 Incorrect syntax near 0 1

[vba317]

I am using SQL 2005 and I am getting this error Incorrect syntax near 0. I am trying to do an insert statement and I am getting this error. I am adding new information into this table by copying existing information from the same table. I am using the replace function for the first time so that might be the issue. I am trying to change what is in the uci,clntid,csfile and arfile columns. For the csfile and arfile columns I am trying to change the first three characters with the letters FCN. Any help is appreciated.

Tom

INSERT INTO rptdata_monthly.dbo.rpt_FYInfo
(uci,clntid,rptpd,rptpddiff,fy,fyord,fydiff,imp,csfile,arfile,0,0,0,0,0,0,0,pfy,cy,qtr,fyoff,mon_shnm,monasdt,days,Last3mondys,Lastdayasdt,arch)
SELECT 'FCN',181,rptpd,rptpddiff,fy,fyord,fydiff,imp,REPLACE(csfile,3,'FCN'),REPLACE(arfile,3,'FCN'),begar,chgs,debits,adjcontr,adjoth,pmts,endar,pfy,cy,qtr,fyoff,mon_shnm,monasdt,days,Last3mondys,Lastdayasdt,arch
FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='FCW' and rptpd =373

 

[gmmastros]

Your problem is with the list of column names. The syntax you are trying to use is basically this...

Insert
Into    TableName([!]ColumnList[/!])
Select  ColumnList
From    OtherTable

The part above that is highlighted in red must be a list of columns that exist in the table you are inserting in to. In your code, you have a bunch of 0's in the list. Since you don't have a column in rptdata_monthly.dbo.rpt_FYInfo named "0", you are getting this error.

Now, for the rest of your question... are you trying to update the data in the table, or do you really want to insert data. Since your insert table is the same as your select table, your code would actually add additional columns to the table so that the original data would still be there unchanged, but you would end up with additional rows that would be changed.

Also... it's a little unclear what you are trying to do with the replace function. Are you simply adding 3 letters to the beginning of the data or are you trying to replace the first 3 characters that are already there with 3 different characters?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

After reviewing the code closer I saw the error of my ways and I fixed it.

Tom

INSERT INTO rptdata_monthly.dbo.rpt_FYInfo
(uci,clntid,rptpd,rptpddiff,fy,fyord,fydiff,imp,csfile,arfile,begar,chgs,debits,adjcontr,adjoth,pmts,endar,pfy,cy,qtr,fyoff,mon_shnm,mon_nm,monasdt,days,Last3mondys,Lastdayasdt,arch)
SELECT 'FCN',181,rptpd,rptpddiff,fy,fyord,fydiff,imp,REPLACE(csfile,'FCW','FCN'),REPLACE(arfile,'FCW','FCN'),0,0,0,0,0,0,0,pfy,cy,qtr,fyoff,mon_shnm,mon_nm,monasdt,days,Last3mondys,Lastdayasdt,arch
FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='FCW'

 

[vba317]

George to answer your questions, I wanted to insert new data into the table and I wanted to replace the first three characters.

Tom

 

[gmmastros]

Just so you know, the replace function will replace multiple instances. Ex:

Declare @Data VarChar(100)

Set @Data = 'FCW12345FCW'
Select REPLACE(@Data,'FCW','FCN')

If this is not a problem for you based on your data, then all is good. If you truly want to replace only the first 3 characters, there are other method for doing this (the stuff function).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

I didn't know this. Could you tell me the stuff method?

Tom

 

[gmmastros]

Here is an example:

Declare @Data VarChar(100)

Set @Data = 'FCW12345FCW'
Select Stuff(@Data, 1, 3, 'XYZ')

Note that the first parameter is the string to operate on.
The 2nd parameter is the starting point to operate on.
The 3rd parameter is the number of characters to delete.
The 4th parameter is the string to stuff.

In this case it will insert the string 'XYZ' in to @Data at position 1 after deleting 3 characters also starting at position 1.

so.... instead of:

REPLACE(csfile,'FCW','FCN')

You would want to use:

STUFF(csfile, 1, 3, 'FCN')



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

Thanks George I will use this method in the future.

Tom