• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

amend sql server insert into query

I had this question after viewing SQL server express update query.


The expert gave an excellent response to id ID: 41716351, I ran the query but need to make it work with my current tables, I am having trouble with that.

My tables are:
TblWordTags
ClientCodeWordPosition nvarchar(50) (pk)
Word nvarchar(50)                                            'this is the StopWords
WordCategory nchar(20)
ClientName nvarchar(50)

TblWords
ClientCodeWordPosition nvarchar(50)            'this links to tblWordTags
ClientCode nvarchar(50)                                       'this is sentenceID
ClientName nvarchar(50)
Word nvarchar(50)
WordLen int
StrFull nvarchar(MAX)
WordPosition int
NoOfWords int
WordCategory nchar(10)
StrShort nvarchar(MAX)                                ' this is ConcatWordText
NoOfNonRelatedWords int
VehCategory nvarchar(50)


I would like to populate the StrShort field in TblWords, wordPosition gives the sequence of the words from the original string

Ive tried to convert this is what i have so far I get incorrect syntax error:

use Dictionary
INSERT  INTO TblWords ([StrShort])

WITH    Filtered
          AS (SELECT   W.Word ,
                        W.ClientCode
               FROM     TblWords W
                        LEFT JOIN TblWordTags SW ON W.Word = SW.ClientCode
               WHERE    SW.Word IS NULL
)

SELECT  O.ClientCode ,
            RTRIM((SELECT  I.Word + ' '
                    FROM    Filtered I
                    WHERE   I.ClientCode = O.ClientCode
FOR
                    XML PATH('')
                  )) AS StrShort
    FROM    Filtered O
    GROUP BY O.ClientCode;

Open in new window

ee.JPG
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 24
  • 12
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you want a insert or update?
not clear...
0
 
PeterBaileyUkAuthor Commented:
an update, good point
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go, without testing...
i notice sime strange join condition

use Dictionary
go
update w
  set strshort = rtrim(f.res)
from tblwords w
cross apply ( select i.word + ' '
         from tblwords i
         left join tblwordtags wt
           on wt.clientcode = i.word -- is this join really correct?
        where i.clientcode = w.clientcode
           and wt.clientcode is null
       for xml path('')
) f (res)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PeterBaileyUkAuthor Commented:
the join is on clientcodewordposition that appears as pk in tblwords and tblwordtags
clientCode is the groups of words
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so you can update the sql as needed, does it work?
difficult for me to test, being on mobile
0
 
PeterBaileyUkAuthor Commented:
giving it a go now.
0
 
PeterBaileyUkAuthor Commented:
its running, it may take a while, ive got this:

use Dictionary 
go
update w
  set strshort = rtrim(f.res)
from tblwords w
cross apply ( select i.word + ' ' 
         from tblwords i
         left join tblwordtags wt 
           on wt.clientcodeWordPosition = i.clientcodeWordPosition -- is this join really correct?
        where i.clientcode = w.clientcode
           and wt.clientcodeWordPosition is null
       for xml path('')
) f (res)

Open in new window

0
 
PeterBaileyUkAuthor Commented:
i only need to create the short string for codes that appear in the tblwordtags so i am guessing thats

 and wt.clientcodeWordPosition is not null
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
i am quite sure you also need to join there on clientname...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
based on your last comment you want then to join and not from left join...
0
 
PeterBaileyUkAuthor Commented:
so just remove the left word
0
 
PeterBaileyUkAuthor Commented:
its now this:

update w
  set strshort = rtrim(f.res)
from tblwords w
cross apply ( select i.word + ' '
         from tblwords i
        join tblwordtags wt
           on wt.clientcodeWordPosition = i.clientcodeWordPosition -- is this join really correct?
        where i.clientcode = w.clientcode
           and wt.clientcodeWordPosition is not null
       for xml path('')
) f (res)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update w
  set strshort = rtrim(f.res)
from tblwords w
cross apply ( select i.word + ' ' 
         from tblwords i
        join tblwordtags wt 
           on wt.clientcodeWordPosition = i.clientcodeWordPosition
  and i.cluentname = wt.clientname
        where i.clientcode = w.clientcode
       for xml path('')
) f (res)


i hope you have indexes on clientcode, clientname + clientcodewordposition
0
 
PeterBaileyUkAuthor Commented:
it didnt work

i just created this in access but only for demo it creates the list on the attached image where i manually filled in the trShort field. with the position you can see what its done. the sqlserver code just put the word BHP in the strshort field, that word is the only word in the tagged table.

The primary key on tblwords is the clientcodewordposition field and identicle in the tblwordtags.
does that create the index? I am very new to sql server.

SELECT TblWords.ClientCode, TblWords.ClientCodeWordPosition, TblWords.Word, TblWords.StrFull, TblWords.StrShort, TblWordTags.ClientCodeWordPosition, TblWordTags.Word, TblWords.WordPosition
FROM TblWords LEFT JOIN TblWordTags ON TblWords.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition
WHERE (((TblWords.ClientCode)="M38VL"))
ORDER BY TblWords.WordPosition;

Open in new window

ee3.JPG
0
 
PeterBaileyUkAuthor Commented:
dont upload on your phone i can wait whatever is more convenient.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
i am currently on the beach, and this for another 2 weeks...
not sure what the issue is. will check a bit later, hold on
0
 
PeterBaileyUkAuthor Commented:
somewhere nice i hope.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The primary key on tblwords is the clientcodewordposition field and identicle in the tblwordtags.
does that create the index?
yes, a primary key created automatically an index

so, what about my sql suggestion, does it work?
0
 
PeterBaileyUkAuthor Commented:
No the sql version did not work, I wrote a version in vb.net, sending values to a stored procedure but Its slower i think than sql server I suspect ive put the code here and the sp so you can see physically what the vb is doing

    Public Sub CreateShort()
        Dim dtCodes = New DataTable
        Dim dtCodeWords = New DataTable
        Dim dtCodeWordsT = New DataTable
        Dim cmd As New SqlCommand
        Dim CmdWords As New SqlCommand
        Dim StrClientCode As String
        Dim StrProcName As String
        Dim StrShortString As String
        Dim StrShortTaggedString As String
        Dim StrProcUpdate As String

        StrProcUpdate = "usp_UpdateShortStrings"
        StrProcName = "usp_getUnTaggedCodeWords"
        Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
            cnSql.Open()
            cmd.Connection = cnSql
            cmd.CommandText = "SELECT TblWords.ClientCode FROM TblWords LEFT JOIN TblWordTags ON TblWords.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition WHERE (((TblWordTags.ClientCodeWordPosition) Is Not Null)) GROUP BY clientcode;"

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            dtCodes.Load(reader)

            For Each drRecord As DataRow In dtCodes.Rows
                StrClientCode = drRecord("ClientCode")
                dtCodeWords = GetShortStringWordsData(StrClientCode, False)

                For Each drRecordW As DataRow In dtCodeWords.Rows
                    StrShortString = StrShortString & drRecordW("word") & Chr(32)
                Next
                StrShortString = RTrim(StrShortString)
                dtCodeWordsT = GetShortStringWordsData(StrClientCode, True)

                For Each drRecordWT As DataRow In dtCodeWordsT.Rows
                    StrShortTaggedString = StrShortTaggedString & drRecordWT("word") & Chr(32)
                Next
                StrShortTaggedString = RTrim(StrShortTaggedString)

                Using cmdStr As New SqlCommand(StrProcUpdate, cnSql)
                    cmdStr.CommandType = CommandType.StoredProcedure
                    cmdStr.Parameters.AddWithValue("@ClientCodeIn", StrClientCode)
                    cmdStr.Parameters.AddWithValue("@StrShortUnTagged", StrShortString)
                    cmdStr.Parameters.AddWithValue("@StrShortTagged", StrShortTaggedString)
                    cmdStr.BeginExecuteNonQuery()
                End Using

                StrShortString = ""
                StrShortTaggedString = ""
            Next

            cnSql.Close()
        End Using
        MessageBox.Show("Finished update of strings.")
    End Sub

Open in new window


sp:
USE [Dictionary]
GO
/****** Object:  StoredProcedure [dbo].[usp_UpdateShortStrings]    Script Date: 28/07/2016 16:09:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_UpdateShortStrings]

@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL

AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged, StrShortTag = @StrShortTagged
WHERE ClientCode = @ClientCodeIn
END

Open in new window


query
USE Dictionary
SELECT word, WordPosition, StrFull, StrShort, strshorttag
from [dbo].[TblWords]
where Strfull ='S70 20V SALOON XT'
group by word, WordPosition, StrFull, StrShort, strshorttag
order by WordPosition

Open in new window

ee.JPG
0
 
PeterBaileyUkAuthor Commented:
in the previous image the word 20V is in the tblWordTags using the pk , ive attached a newer image with more detail on one record.
the tagged word is not required in strshort so its rebuilt using the wordposition and a join to tblwordtags
that way i can build a strshort with no tagged words.
ee2.JPG
0
 
PeterBaileyUkAuthor Commented:
in the example of the volvo i will tag the word SALOON so it gets put in the tblwordtags along with the 20V, then the code will rebuild the short string to:
S70 XT
and the strshorttag becomes :
20V XT

the pk in tblwordtags in this case:
clientcodewordposition        word
15571C_20V_2                         20V
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
So you want the worttags.word ... and not words.word

update w
  set strshort = rtrim(f.res)
from tblwords w
cross apply ( select wt.word + ' ' 
         from tblwords i
        join tblwordtags wt 
           on wt.clientcodeWordPosition = i.clientcodeWordPosition
        where i.clientcode = w.clientcode
       for xml path('')
) f (res)

Open in new window

0
 
PeterBaileyUkAuthor Commented:
its almost there but not quite

The strfull is "S60 S BIFUEL 20V"
strshort = "20V BIFUEL"

StrShort should be "S60 S"

The suggestion you gave almost creates my StrShortTag field which in this case would become "BIFUEL 20V"

BUT I didnt ask for that in the question so irrelevant.

I am guessing its just a join issue now.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please set up a sql fiddle with the data "before" on the 2 tables, for a couple of records, so I could try to play with it?
0
 
PeterBaileyUkAuthor Commented:
what does this mean? can you please set up a sql fiddle with the data. I am assuming you want me to create a couple of tables but how do i get them to you, or do you login.
0
 
PeterBaileyUkAuthor Commented:
sorry ive never done this before, although i am liking sql server more and more i try to use it
0
 
PeterBaileyUkAuthor Commented:
I think you wanted something like this
CREATE TABLE [TblWordTags]
( 
  ClientCodeWordPosition nvarchar(50) [NOT NULL],
  Word nvarchar(50) [NULL],
  WordCategory nchar(20) [NULL],
  ClientName nvarchar(50) [NULL],
CONSTRAINT ClientCodeWordPosition PRIMARY KEY (ClientCodeWordPosition)
);


INSERT INTO TblWordTags (ClientCodeWordPosition, Word, WordCategory, ClientName)
Values('000239C_20v_2', '20v', NULL, 'Glass');
values('95052350_CO2_7','CO2', NULL, 'Abi');

CREATE TABLE [TblWords]
( 
  ClientCodeWordPosition nvarchar(50) [NOT NULL],
  ClientCode nvarchar(50) [NOT NULL],
  ClientName nvarchar(50) [NOT NULL],
  Word nvarchar(50) [NULL],
  WordLen int [NOT NULL],
  StrFull nvarchar(MAX) [NOT NULL],
  WordPosition int [NOT NULL],
  NoOfWords int [NOT NULL],
  WordCategory nchar(10) [NULL],
  StrShort nvarchar(MAX)[NULL],
  NoOfNonRelatedWords int [NULL],
  VehCategory nvarchar(50) [NULL],
  StrShortTag nvarchar(MAX) [NULL],
  CONSTRAINT ClientCodeWordPosition PRIMARY KEY (ClientCodeWordPosition)
);




INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords, WordCategory, StrShort, NoOfNonRelatedWords, VehCategory, StrShortTag)
Values('000239C_20v_2',	'000239C', 'Glass', '20v', '3',	'90 20v', '2', '2', NULL, '90',	NULL, NULL, '20v');
Values('95052350_CO2_7', '95052350''Abi', 'CO2', '3', 'A1 14 TFSI S TRONIC 122G CO2', '7', '7',	NULL, NULL, NULL, NULL, NULL);

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
back in "trouble", I have now a SQL Server to play with.

this is the SQL update I come up with:

rtrim(f.res)
from tblwords w
cross apply ( select wt.word + ' ' 
         from  tblwordtags wt 
           where wt.clientcodeWordPosition = i.clientcodeWordPosition
       for xml path('')
) f (res)

Open in new window


or am I still not getting it?
do you want to "remove" all the words from wordtags from strFull to populate strShort?

then it would be:

rtrim(f.res)
from tblwords w
cross apply ( select replace(w.full, wt.word , ' '  )
         from  tblwordtags wt 
           where wt.clientcodeWordPosition = i.clientcodeWordPosition
       for xml path('')
) f (res)

Open in new window


please test and clarify
0
 
PeterBaileyUkAuthor Commented:
and success with the first solution in ID: 41748260
0
 
PeterBaileyUkAuthor Commented:
thank you so much. hope you had a great holiday.
0
 
PeterBaileyUkAuthor Commented:
PS

I realised I had made a small change:
use Dictionary
go



update w
  set strshort = rtrim(f.res)



from tblwords w
cross apply ( select wt.word + ' '
         from  tblwordtags wt
           where wt.clientcodeWordPosition = w.clientcodeWordPosition
       for xml path('')
) f (res)
0
 
PeterBaileyUkAuthor Commented:
Having had a break too ive realised it is not creating the strshort strshort is a concatenation of the words, its only adding the individual words into the strshort field when it should be both, i will try the second solution too.
0
 
PeterBaileyUkAuthor Commented:
To simplify add another another word into tblwordtags
INSERT INTO TblWordTags (ClientCodeWordPosition, Word, WordCategory, ClientName)
Values('95052350_TFSI_3', 'TFSI', NULL, 'Abi');
Values('95052350_TRONIC_5', 'TRONIC', NULL, 'Abi');
Values('95052350_122G_6', '122G', NULL, 'Abi');


INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords, WordCategory, StrShort, NoOfNonRelatedWords, VehCategory, StrShortTag)
Values('95052350_122G_6', '95052350''Abi', '122G', '4', 'A1 14 TFSI S TRONIC 122G CO2', '6', '7',	NULL, NULL, NULL, NULL, NULL);
Values('95052350_14_2', '95052350''Abi', '14', '3', 'A1 14 TFSI S TRONIC 122G CO2', '2', '7',	NULL, NULL, NULL, NULL, NULL);
Values('95052350_A1_1', '95052350''Abi', 'A1', '2', 'A1 14 TFSI S TRONIC 122G CO2', '1', '7',	NULL, NULL, NULL, NULL, NULL);
Values('95052350_S_4', '95052350''Abi', 'S', '1', 'A1 14 TFSI S TRONIC 122G CO2', '4', '7',	NULL, NULL, NULL, NULL, NULL);
Values('95052350_TFSI_3', '95052350''Abi', 'TFSI', '4', 'A1 14 TFSI S TRONIC 122G CO2', '3', '7',	NULL, NULL, NULL, NULL, NULL);
Values('95052350_TRONIC_5', '95052350''Abi', 'TRONIC', '6', 'A1 14 TFSI S TRONIC 122G CO2', '5', '7',	NULL, NULL, NULL, NULL, NULL);

Open in new window

the above gives all the words for code 95052350


use Dictionary
go

	SELECT   W.Word, W.clientcodeWordPosition
                    
               FROM     tblwords w
                        LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
               WHERE    wt.word IS NULL and w.ClientCodeWordPosition like '95052350%'
			   order by WordPosition;

			   select *
			   from TblWordTags
			   where ClientCodeWordPosition like '95052350%'

Open in new window


I think I have almost made it, it needs the word position to be taken into account

use Dictionary
go



WITH    Filtered
          AS ( SELECT   W.Word, w.WordPosition, w.clientcode
                    
               FROM     tblwords w
                        LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
               WHERE    wt.word IS NULL and w.ClientCodeWordPosition like '95052350%'
			   
             )
    SELECT  o.clientcode,
            RTRIM(( SELECT  I.Word + ' '
                    FROM    Filtered I
                    WHERE   I.clientcode = O.clientcode
				
                  FOR
                    XML PATH('')
                  )) AS ConcatWordText
    FROM    Filtered O;

Open in new window

ee1.JPG
0
 
PeterBaileyUkAuthor Commented:
this definitely works, I got the word position into account.

use Dictionary
go



WITH    Filtered
          AS ( SELECT   W.Word, w.WordPosition, w.clientcode
                   
               FROM     tblwords w
                        LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
               WHERE    wt.word IS NULL and w.ClientCodeWordPosition like '95052350%'
                    
             )
    SELECT  o.clientcode,
            RTRIM(( SELECT  I.Word + ' '
                    FROM    Filtered I
                    WHERE   I.clientcode = O.clientcode
                         order by i.WordPosition
                  FOR
                    XML PATH('')
                  )) AS ConcatWordText, o.WordPosition
    FROM    Filtered O
   order by o.WordPosition
0
 
PeterBaileyUkAuthor Commented:
Not sure how to get the word table strshort updated with the results from this but will post a new question for that.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see you get impatient, opening a new question ...

well, I also see my code was truncated in the middle somewhere

update w
  set strshort = rtrim(f.res)
from tblwords w
cross apply ( select wt.word + ' ' 
         from tblwordtags wt 
         where wt.clientcodeWordPosition = i.clientcodeWordPosition
        order by wt.WordPosition
       for xml path('')
) f (res)

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 24
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now