Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

sql server insert query

Ive created a query but have a syntax error with the underscores.

I wonder if someone could assist, I dont think I am far out.

use dictionary


insert into dbo.TblCurrentWordCounts (WordStat_ID,  Word, NoOfWords, WordLength, WordPosition, ClientCode, MyDateTimeCol,ClientCodeWordPosition, ClientCodeWordWordCountWordLenPosition)

select Word + CONVERT(CHAR(17),GETDATE(),120) AS CurrentDateTime, GETDATE() AS MyDateTimeColWord, Word, NoOfWords, wordlen, WordPosition, ClientCode, ClientCode & '_'& word &'_'& WordPosition,  ClientCode & '_ '& word &'_ '& NoOfWords &'_ '& WordLen &'_ '& WordPosition
from TblWords
where word <>'' and clientcode='210904C'
--where word <>''
group by word, NoOfWords, ClientCode, wordlen, WordPosition
order by clientcode

Open in new window

ee2.JPG
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

String concatenation in SQL Server uses "+" not "&".  & is only for bit-related operations.  For example:

...ClientCode + '_'+ word +'_'+ WordPosition,...
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

i tried that first and it failed with
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '_ ' to data type int.

use dictionary


insert into dbo.TblCurrentWordCounts (WordStat_ID,  Word, NoOfWords, WordLength, WordPosition, ClientCode, MyDateTimeCol,ClientCodeWordPosition, ClientCodeWordWordCountWordLenPosition)

select Word + CONVERT(CHAR(17),GETDATE(),120) AS CurrentDateTime, GETDATE() AS MyDateTimeColWord, Word, NoOfWords, wordlen, WordPosition, ClientCode, ClientCode + '_'+ word +'_'+ WordPosition,  ClientCode + '_ '+ word +'_ '+ NoOfWords +'_ '+ WordLen +'_ '+ WordPosition
from TblWords
where word <>'' and clientcode='210904C'
--where word <>''
group by word, NoOfWords, ClientCode, wordlen, WordPosition
order by clientcode

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial