How To Split And Get The Unique Ids
Nov 3, 2006
hai friends,
please help me.Here is my doubt
In a table I have fields like this TABLE NAME : table1
COLUMN NAME : ids
----------------
ids
-----------------
1,3,4
3,4
3
7,1
1
11
6
6,7
1
i want to get all the ids used i.e., 1,2,3,6,7,11
i want to update another table based on the id, which are not listed here.
I am doing like below
declare @IDs varchar(200)
select IDs=ids from table1
update table where(id not in(select ID from timeSplitter(@IDs)))
please help me.it is urgent
thanks in advance
bye
pavansagar
pavansagar
View 1 Replies
ADVERTISEMENT
Mar 14, 2006
SSIS 2005
Ok, I have a task in SSIS that does the following and works:
1) Brings in a txt file
2) Using a conditional component, checks for a value in the row.
3) Based on the value, splits the row into one of 3 tables (Header, Maintenance, or Payment)
Here is a print screen of what I have so far which splits Header rows into it's own table, Maintenance rows into its own table, and Payment Rows into its own table:
http://www.webfound.net/qst_how_to_add_header_PK_and_FKs.JPG
Here is a print screen of the conditional split:
http://www.webfound.net/conditional_split.jpg
Please take a look at the txt file here before it's processed:
http://www.webfound.net/split.txt
http://www.webfound.net/rows.jpg
Notice that the pattern is a header row, followed by it's corresponding detail rows. The detail rows are either Maintenance or Payment rows.
I need to somehow during the Script component or some other way, to assign a unique HeaderID (PK) to each of the header rows and add that ID to it's corresponding Maintenance and Payment detail rows in their corresponding tables as a PK. The problem is
1) I don't know how to do this in the flow of the components as I have it now
2) How do I tell it to create a new Header ID and Header FKs for the detail rows based off of each new Header row?
In the end (much later on in my entire package), the goal is to be able to run a stored proc to join and select the Header and Details rows back into a final table so I can then do more processing such as split each header and detail rows into their own txt files, etc....I don't need to go into details why but just know that this is the goal, therefore I need to relate each header row with their corresponding detail rows that are split off into a MaintenanceRow and PaymentRowTable
View 2 Replies
View Related
Oct 21, 2014
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
Unique Code Comma Separated Value
79585264 1
79585264 5
79585264 18
View 5 Replies
View Related
Jul 5, 2015
This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
View 6 Replies
View Related
Sep 22, 2004
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
View 8 Replies
View Related
Jul 20, 2005
HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies
View Related
Jun 24, 2006
What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
View 1 Replies
View Related
Mar 7, 2001
Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?
Which one do one use ?
thanks
sonali
View 4 Replies
View Related
Jan 20, 2006
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
View 2 Replies
View Related
Mar 26, 2008
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
View 12 Replies
View Related
Mar 12, 2008
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code
Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword
and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
Thank You
Dominic Mancl
View 1 Replies
View Related
Nov 13, 2007
What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies
View Related
May 1, 2008
Here is the table I created:
create table Test (
[recId] [int] identity(1, 1) not null,
[code] [varchar](50) not null,
[prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)),
constraint [PK_Test] primary key clustered
(
[recId]
) with fillfactor = 90 on [primary]
) on [primary]
go
insert into Test (code, prime) values ('AVA', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('CAR', cast(1 as bit))
insert into Test (code, prime) values ('CAR', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(1 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
select *
from Test
I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:
-- This should fail
insert into Test (code, prime) values ('RLW', cast(1 as bit))
Thanks for you help!
Regards,
Anand
View 11 Replies
View Related
Feb 16, 2005
Quick question.
I've got a CHAR (70) field called NAME that has a first and last name separated by a space. I want to split it into two fields FIRST and LAST -- with all the characters to the left of the space a first name and all the characters to the right of the space as last name. I couldn't find a string function that would let me do this simply (it may be right in front of me and I missed it).
Thanks in advance.
Ray
View 14 Replies
View Related
May 9, 2007
I have a database with a "large" table containing date based information Basically they're reservations. I've thought about creating a new table and adding any records from past years to this table. For the most part only current reservation need to be searchable, but in some circumstances it would be useful to be able to search through the archive too. so, my questions!!!
Is 8,000 or so rows of data "large" and unwieldly in SQL terms?
Would splitting this data into 2 tables - one small table for current and future reservations and one larger archive table then using a UNION SELECT query to make archive information seachable be a significant improvment on server resources/load or am I making the whole thing more complicated than it need be as 8,000 rows of data is nothing to worry about.............
What did they say about a little bit of knowledge being a dangerous thing?
Thanks in advance of any guidance to a neophyte!!?
View 6 Replies
View Related
Jul 14, 2007
Hi to all
I have one problem regarding sp and pass value in sp
I am gating a value like Abc,Def,Ghi,
Now I want to split the whole pass value by “,�
And fire one for loop to store value in database
This things is done in asp.net web form but I want to do all process in sp
So please guide me how I am write sp .
The purpose is pass value one time so connection time is decrees and give fast perforce
View 3 Replies
View Related
Jan 8, 2008
SQL UDF split()
The objective of this article is to help the SQL developers with an UDF that can be used within a stored procedures or Function to split a string (based on given delimiter) and extract the required portion of the string.
Scripting languages like VB script and Java script have in-built split() functions but there is no such function available in SQL server. In my experience this function is really handy when you’re working on an ASP application with SQL server as backend, whereby you’ll need to pass the ASP page submitted values to the SQL stored procedure.
To give a simple example, in a typical Monthly reporting ASP page – the users would select a range of months and extract the information pertaining to this date range. Classic implementation of this model is to have an ASP page to accept the input parameters and pass the values to the SQL stored procedure (SP). The SP would return a result set which is then formatted in the ASP page as results.
If the date range is continuous ie. JAN07 to MAR07 then the SP can typically accept a ‘From’ and ‘To’ range variables. But I’ve encountered situations whereby the users select 3 months from the current year and 2 months from previous year (non-continuous date ranges). In such scenario the SP cannot have a date range as input parameters.
Typically an ASP programmer would do is by having a single date input parameter in the SP and call the SP within a loop in the ASP page. This is an inefficient way of programming as contacting the database server within an ASP loop could cause performance overhead especially if the table being queried is an online transaction processing table.
Here is how I handled the above situation.
1.Declared one string input parameter of type varchar(8000) (if you’re using SQL 2005 then it is advisable to use Varchar(Max))
2.Pass the ASP submitted values as string, in this case the months selected by user would be supplied to the SP as a string
3.Within the Stored Procedure I’ll call the split() function to extract each month from the string and query the corresponding data
The basic structure of the stored procedure is as pasted below:-
CREATE PROCEDURE FETCH_SALES_DETAIL (
@MONTH VARCHAR(MAX)
)
AS
BEGIN
DECLARE @MONTH_CNT INT,@MTH DATETIME
SET @MONTH_CNT=1
WHILE DBO.SPLIT(@MONTH,',',@MONTH_CNT) <> ''
BEGIN
SET @MTH = CAST(DBO.SPLIT(@MONTH,',',@MONTH_CNT) AS DATETIME)
--<<Application specific T-SQLs>>-- (BEGIN)
SELECT [SALES_MONTH],[SALES_QTY],[PRODUCT_ID],[TRANSACTION_DATE]
FROM SALES (NLOCK)
WHERE [SALES_MONTH]= @MTH
--<<Application specific T-SQLs>>--(END)
SET @MONTH_CNT=@MONTH_CNT+1
END
END
Dbo.SPLIT() function takes 3 parameters
1)The main string with the values to be split
2)The delimiter
3)The Nth occurrence of the string to be returned
The functionality of the UDF is as explained STEP by STEP:
1.Function Declaration
CREATE FUNCTION [dbo].[SPLIT]
(
@nstring VARCHAR(MAX),
@deliminator nvarchar(10),
@index int
)
RETURNS VARCHAR(MAX)
Function is declared with 3 input parameters:-
@nstring of type VARCHAR(MAX) will hold the main string to be split
@deliminator of type NVARCHAR(10) will hold the delimiter
@index of type INT will hold the index of the string to be returned
2.Variable Declaration
DECLARE @position int
DECLARE @ustr VARCHAR(MAX)
DECLARE @pcnt int
Three variables are needed within the function. @position is an integer variable that will be used to traverse along the main string. @ustr will store the string to be returned and the @pcnt integer variable to check the index of the delimiter.
3.Variable initialization
SET @position = 1
SET @pcnt = 1
SELECT @ustr = ''
Initialize the variables
4.Main functionality
WHILE @position <= DATALENGTH(@nstring) and @pcnt <= @index
BEGIN
IF SUBSTRING(@nstring, @position, 1) <> @deliminator BEGIN
IF @pcnt = @index BEGIN
SET @ustr = @ustr + CAST(SUBSTRING(@nstring, @position, 1) AS nvarchar)
END
SET @position = @position + 1
END
ELSE BEGIN
SET @position = @position + 1
SET @pcnt = @pcnt + 1
END
END
4.1The main while loop is used to traverse through the main string until the word index is less than or equal to the index passed as input parameter.
4.2Within the while loop each character within the string is verified against the delimiter and if it does not match then local word count variable is checked against the input index parameter
4.3If the values are same ie., the input variable index and the word being processed in the while loop are the same then the word is stored in the @ustr variable. If the values does not match then the @position variable is incremented.
4.4If the character matches with the delimiter then the word count variable @pcnt is incremented along with the @position variable
5.Return the value
RETURN @ustr
I hope this article would benefit those who are looking for a handy function to deal with Strings.
Feel free to send your feedback at dearhari@gmail.com
View 1 Replies
View Related
Feb 4, 2008
I have 5 dynamic rows each row consisting of 5 checkboxes & 5 dropdowns.I am concatenating the values of each controls in a row using a wildcard charater "~" and each row i am concatenating using "|".The complete string is then assigned to one hidden field and passed as sql parameter to the backend.
Please help in writing the split function to get the values of each checkboxes and dropdowns from the string in order to save them in separate columns.
Thanks
View 3 Replies
View Related
Jan 28, 2004
Hi,
Is it possible to split the following value in sql server ?
I have the value like 25 Email Accounts,50 Email Accounts in my sqlserver database.
Here i need only the numeric value .ie 25,50.Is it possible? can any one give me the solution ..
I am using ASP.Net and C# backend is SQL Server 2000.
Thanks and Regards
Arul
View 1 Replies
View Related
Oct 17, 2005
CREATE PROCEDURE [dbo].[ShowComboLocation]@Keyword varchar(50) ASSELECT TOP 100 PERCENT PropertyAreaID, PropertyAreaFROM dbo.iViewAllWHERE (PropertyArea LIKE '%' + @Keyword + '%')GOQuestion 1 isIf Keyword ="London WestEnd Harrods", I know my query will end up like this (PropertyArea LIKE 'London WestEnd Harrods')But I want to to individually search for 3 or 1-nth words therefore my query should end up like this(PropertyArea LIKE 'London')OR (PropertyArea LIKE 'WestEnd')OR (PropertyArea LIKE 'Harrods')i WANT TO perform this on my SQL STored Procedure,Can anybody provide code or links pls
View 1 Replies
View Related
Mar 21, 2001
I have a procedure that is going to be called through asp pages. This procedure carries out instructions depending on whether customers wants to insert, update or delete their portfolios. Rules are as follows: 1. It should not allow duplicate portfolio name to insert. 2. If customer has reached their max limit of 20 portfolio they can't add. They may have to delete or update the existing portfolio first. 3. all the error handling is done and returned as output parameters.
Now coming to the question at present I have one procedure that does all these things. Should I split up the procedure and have three procedures handling the events seperately: 1 Insert 2 Delete 3 update The reason I am concerned is 1 procedure being hit so many times by concurrent users with varying events. I am concerned about performance issue and slowing down of the page. I do not have exact numbers of users at this point. But they would be in thousands or more. Thanks for any suggestions or advice you all might have to share. Hiku
View 3 Replies
View Related
Mar 22, 2001
I want to know how to parse a fullname into a fname and lname.
View 2 Replies
View Related
Sep 19, 2002
What I have is a table with a primary key. Then I have 5 other tables with a relating key. No problems there.
I need to create a relationship with the primary table (primary) key who's data field is 25 charachters. I need to parse that out and have 3 charachters go to one, 2 to the other and so on.
I don't know how to do that, can you help?
View 1 Replies
View Related
Nov 28, 2002
Hi I need a stored procedure in SQL that will split a comma separated variable passed to it
select a name for each value and return a recordset. Any pointers greatfully received.
First attempt is dreadfully slow as I am opening recordsets each time
Function func_getFood()
Dim rsfoodsql
Dim foodoutput
for x=1 to ubound(masterfoodarray)-2
set rsfoodsql= objconn.execute ("select foodname from tbl"&language&"food where foodID='"& masterfoodarray(x) &"'")
if not rsfoodsql.eof then
foodoutput=rsfoodsql("foodname")
if not foodoutput="" then
response.write foodoutput&"<BR>"
end if
end if
next
End Function
Hope someone can help,
cheers
View 2 Replies
View Related
Nov 4, 2004
Hi everybody
Does any body know how to split a field in a table into two fields
eg
usermaster(table)
userid(field)
usermaster has 40 users with user id 1 to 40
i want to get data as
userid userid
1 21
2 22
3 23
. .
. .
. .
20 40
Thanks you very much
View 3 Replies
View Related
Sep 27, 2005
I have a large table that I'm planning on splitting out into 5 smaller ones. What I need to do is maintain some central repository for auto-numbering new records to make sure that no 2 records in different tables have the same unique ID. Thanks in advance!
View 14 Replies
View Related
Oct 29, 2007
Hi
I have this string which might have a hyphen in it "-"
What I want to do is
if I get a hyphen then take all characters after hyphen
else take only all the characters starting from the 5th position of the string
How can this be achieved?
View 7 Replies
View Related
Mar 14, 2014
following is the base table
create table #sample (id int , value varchar(100))
insert into #sample values (1,'aaa,bbb')
insert into #sample values (2,'ccc,ddd')
insert into #sample values (3,'aab')
& i need the attached output
View 2 Replies
View Related
Dec 17, 2014
So I would like to split rows in my query based on if they match criteria and I really havent dealt with splitting in SQL only combining(which is usually what everybody does lol)
So for instance my query results is this
SOPNUMBER TAX TOTAL
14-123456 $500.00
View 1 Replies
View Related
Apr 7, 2008
Hi
I have tried like this. but I cant execute my SP it ended up with errors.
IF (@FirstLetters IS NOT NULL)
BEGIN
SET @FirstLetter = 'SELECT SplitValue FROM dbo.FnSplitString('+@FirstLetters+','',''))'
print @FirstLetter
END
SELECT
P.PUB_ID AS ''PubId'',
P.PUB_TITLE AS ''PubTitle''
FROM HDS_PUBLICATION P
INNER JOIN HN_IM_JOIN IM
ON IM.PUB_UNID = P.PUB_UNID,
HDS_CUSTOM C,
WD_PUBLIC_SHELF S,
HDS_TOPIC T
WHERE P.PUB_UNID = C.PUB_UNID AND C.CUSTOM3 = ''False''
AND P.PUB_ID = S.PUB_ID
AND P.PUB_UNID = T.PUB_UNID
AND S.Audience = ''Public''
' + @FilterByLang + '
ANDP.PUB_TITLE LIKE '+ @FirstLetter +'''%''' +'
EXEC SP
Any one please clear me
Thanks,
shakthiA
View 3 Replies
View Related
Jun 4, 2008
I am having City, State i need to split these two string with comma separated and to search based on the city and state how to write the select query
any one know help me
Regards,
Prabu R
View 1 Replies
View Related
Jun 4, 2008
I have the following:
iddocidgroupidOption
48986111235
353973334
2314820112346
would like to see this result:
iddocidgroupidOption
48986111
48986112
48986113
48986115
35397333
35397334
231482011
231482012
231482013
231482014
231482016
thanks you
View 5 Replies
View Related
Sep 7, 2013
A#B#C
A#B
A#B#C#D
How do I split above value in columns by "#"?
I need output:
Column1 Column2 Column3 Column4
A B C
A B
A B C D
View 4 Replies
View Related
