Solved

amend sql server insert into query

Posted on 2016-07-25
36
42 Views
Last Modified: 2016-08-09
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
Comment
Question by:PeterBaileyUk
  • 24
  • 12
36 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41727899
do you want a insert or update?
not clear...
0
 

Author Comment

by:PeterBaileyUk
ID: 41727907
an update, good point
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41727938
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
 

Author Comment

by:PeterBaileyUk
ID: 41727945
the join is on clientcodewordposition that appears as pk in tblwords and tblwordtags
clientCode is the groups of words
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41727954
so you can update the sql as needed, does it work?
difficult for me to test, being on mobile
0
 

Author Comment

by:PeterBaileyUk
ID: 41727995
giving it a go now.
0
 

Author Comment

by:PeterBaileyUk
ID: 41728010
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
 

Author Comment

by:PeterBaileyUk
ID: 41728013
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41728014
i am quite sure you also need to join there on clientname...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41728015
based on your last comment you want then to join and not from left join...
0
 

Author Comment

by:PeterBaileyUk
ID: 41728020
so just remove the left word
0
 

Author Comment

by:PeterBaileyUk
ID: 41728021
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41728027
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
 

Author Comment

by:PeterBaileyUk
ID: 41728067
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
 

Author Comment

by:PeterBaileyUk
ID: 41728068
dont upload on your phone i can wait whatever is more convenient.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41728124
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
 

Author Comment

by:PeterBaileyUk
ID: 41728177
somewhere nice i hope.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41732917
>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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:PeterBaileyUk
ID: 41733098
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
 

Author Comment

by:PeterBaileyUk
ID: 41733112
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
 

Author Comment

by:PeterBaileyUk
ID: 41733126
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41735245
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
 

Author Comment

by:PeterBaileyUk
ID: 41735689
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41737008
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
 

Author Comment

by:PeterBaileyUk
ID: 41738558
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
 

Author Comment

by:PeterBaileyUk
ID: 41738559
sorry ive never done this before, although i am liking sql server more and more i try to use it
0
 

Author Comment

by:PeterBaileyUk
ID: 41740220
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41748260
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
 

Author Comment

by:PeterBaileyUk
ID: 41748315
and success with the first solution in ID: 41748260
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41748316
thank you so much. hope you had a great holiday.
0
 

Author Comment

by:PeterBaileyUk
ID: 41748342
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
 

Author Comment

by:PeterBaileyUk
ID: 41748381
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
 

Author Comment

by:PeterBaileyUk
ID: 41748517
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
 

Author Comment

by:PeterBaileyUk
ID: 41748569
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
 

Author Comment

by:PeterBaileyUk
ID: 41748577
Not sure how to get the word table strshort updated with the results from this but will post a new question for that.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41748593
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Getting certain data from a string 1 22
SQL Server 2012 Row Selection 2 29
Sql query 34 17
SQL 2016 Setup - Connectivity Issues 4 12
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now