Start Free Trial

asked on

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

Guy Hengel [angelIII / a3]

do you want a insert or update?
not clear...

ASKER

an update, good point

Guy Hengel [angelIII / a3]

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)

ASKER

the join is on clientcodewordposition that appears as pk in tblwords and tblwordtags
clientCode is the groups of words

Guy Hengel [angelIII / a3]

so you can update the sql as needed, does it work?
difficult for me to test, being on mobile

ASKER

giving it a go now.

ASKER

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

ASKER

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

Guy Hengel [angelIII / a3]

i am quite sure you also need to join there on clientname...

Guy Hengel [angelIII / a3]

based on your last comment you want then to join and not from left join...

ASKER

so just remove the left word

ASKER

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)

Guy Hengel [angelIII / a3]

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

ASKER

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

ASKER

dont upload on your phone i can wait whatever is more convenient.

Guy Hengel [angelIII / a3]

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

ASKER

somewhere nice i hope.

Guy Hengel [angelIII / a3]

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

ASKER

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

ASKER

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

ASKER

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

Guy Hengel [angelIII / a3]

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

ASKER

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.

Guy Hengel [angelIII / a3]

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?

ASKER

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.

ASKER

sorry ive never done this before, although i am liking sql server more and more i try to use it

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

and success with the first solution in ID: 41748260

ASKER

thank you so much. hope you had a great holiday.

ASKER

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)

ASKER

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.

ASKER

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

ASKER

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

ASKER

Not sure how to get the word table strshort updated with the results from this but will post a new question for that.

Guy Hengel [angelIII / a3]

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