Comparing A Column List Split To A Table.
Let me see if I can explain my situation clearly.
I have a table with the columns:
answer_id, question_id, member_id, answer
- answer_id is the primary key for the table.
- question_id relates to another table with questions for a user. The
table holds the question and the possible choices in a varchar field
separated by a delimiter.
- member_id is self-explanatory
- answer is a varchar field of all the choices the user selected,
separated by a delimiter.
Here is my problem.
I am trying to search all members that have answered, say, question_id
= 2 where they selected 'brown' as one of their choices.
i can do this if they selected ONLY that item, but not multiple items.
The problem is this portion
answer in
(select valu from dbo.iter_intlist.....
I need this to be something like....
function_to_return_all_separated_answers(answer) in
(select valu from dbo.iter_intlist
The current way, it is only returning members that have an answer
'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,
what I need to do is separate the list of answers and say :
select member_id from profile_answers where
ANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (select
valu from dbo.iter_intlist...
It seems I might have to join or something, I am just a little lost
right now.
Here is my proc.
ALTER procedure search_detailed_get_ids
@question_id as integer,
@answers as varchar(8000),
@member_ids ntext
as
declare @v as varchar(8000)
--get the delimited string of all possible answers
set @v = (select bind_data from profiles_questions where question_id =
@question_id)
--prepare it for the function only accepting 1 char
set @v = replace(@v, '||', '|')
--gimme all members that match
select member_id from profiles_answers where question_id = @question_id
and answer in
(select valu from dbo.iter_intlist_to_table(@v, '|') where listpos in
(select valu from dbo.iter_intlist_to_table(@answers, ',')))
and member_id in (select valu from dbo.iter_intlist_to_table
(@member_ids, ','))
return
go
View Complete Forum Thread with Replies
Related Forum Messages:
Comparing List To A Table
Hi guys, i'm sort of stuck. I have a list of names, all i have to do is check to see if any of them are in a table, if they are they get deleted. The unique field of the table is title and it is what i have. I don't really know how i am meant to do this. I thought of creating a new table, placing my values into it and comparing to the already existing table, but surely their is a simple way?
View Replies !
Problem With Comparing A Table View Column With The Table Column?
I have a CVdetails table which all the personal and technical details along with the path called CV_Path which stores the path of CV word doc. Using Index Server Query i got the paths in my SQL server using Linked Server. I used this Query SELECT Path FROM OPENQUERY (FileSystem,'SELECT Directory, FileName, DocAuthor, Size, Create, Write, Path FROM SCOPE ('' "c:myCatalogFolder" '') WHERE FREETEXT (‘’reference’’)’) Which gave a result as Path c:mycatalogfolderajaxguide.doc c:mycatalogfolderquickguide.txt Now, I want to compare these paths with the ones in the CVdetails table which are same and drag out the relevant CVdetails of that particular path. My code is shown below but its not working out, its not giving any results though the paths are same. I hope I made some sense till now Thanks in Advance IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'FileSearchResults') DROP VIEW FileSearchResults GO CREATE VIEW FileSearchResults AS SELECT * FROM OPENQUERY (FileSystem,'SELECT Directory, FileName, DocAuthor, Size, Create, Write, Path FROM SCOPE ('' "c:myCatalogFolder" '') WHERE FREETEXT (''reference'')') Go SELECT * FROM FileSearchResults F, CVdetails C WHERE C.CV_Path = F.PATH Go
View Replies !
Split Data Into Two Column Table
Hello all, Little layout question. Assume my dataset returns the following data: A B C D E How can I present this data in a table (or list, or matrix) splitted into two columns: A B C D E Any idea will be very appreciated! Thanks a lot! TG
View Replies !
Need To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables
Hi , I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table, e.g. ID Email -- ---------- 1 AS1 AS11 2 AS2 AS3 AS4 AS5 3 AS6 AS7 The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g. ID EmailAddress --- ------------------ AS1 abcstu@emc.com AS2 abcstu2@emc.com AS3 abcstu3@emc.com AS4 abcstu4@em.com AS5 abcstu5@emc.com AS6 abcstu6@emc.com AS7 abcstu7@emc.com AS11 abcstu8@emc.com I need to create a stored procedure or function that: 1. Selects an Email from the first table, based on a valid ID, 2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then, 3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors? Many Thanks, probetatester@yahoo.com
View Replies !
IDIOT NEEDS HELP Comparing A Variable Against A List
I have a variable called @ORComm which has been selected using a cursor from each line on an order. DECLARE TC2 CURSOR FOR SELECT [Commodity],[Total] FROM [CSITSS].[dbo].[Ordrate] WHERE [OrderNumber]= @OrdNum AND [Companydiv] = 'GLPC-TRANS' OPEN TC2 FETCH NEXT FROM TC2 INTO @ORComm, @ORTotal I need to compare the resulting @ORComm against a list of valid commodity types selectable by SELECT [CommodityClass] FROM [CSITSS].[dbo].[Comclass] WHERE [CompanyDiv] = 'GLPC-TRANS' AND [DELETED] = 0 What's the easiest way to do this?
View Replies !
Include Column Description With List Of Table Properties
Hello --I am not yet an advanced SQL code writer.Can someone help me add the extended property 'Description' to a list ofcolumns resulting from this code?SELECTSUBSTRING(table_name,1,20) AS "Table",SUBSTRING(column_name,1,30) AS "Column",SUBSTRING(data_type,1,13) AS "DataType",character_maximum_length AS "Length",numeric_precision AS "Precision",numeric_scale AS "Scale"FROM information_schema.columnsWHEREtable_schema = 'DBO'ORDER BY table_name, ordinal_positionThe following codeSELECT objname AS ColName,value AS DescriptionFROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table','tablename', 'column', null)results in:ColName Description....name of column description of columnfor one table at a timeI want to include the value of 'Description' to the first set of code.Would it be simpler to use sysobjects and sysproperties for the Description?If so, can someone suggest how?This code gives me the table and comment for a column, but I can't figureout how to add the column to which the comment belongs.SELECT obj.name AS [Table], prop.value AS [Description]FROM sysobjects obj JOIN sysproperties prop ON obj.id = prop.idThanks for any help.Larry Mehl---Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.806 / Virus Database: 548 - Release Date: 12/5/2004
View Replies !
UDF To Split A Comma Separated List
Hello. I need to write a UDF that would split a comma separated list and return 4 values. I need to return the first 4 values and ignore the commas after that. If there are no commas in the string that's passed then just return the table with empty strings. The UDF should accept 2 inputs. The ntext and a position and return a value based on the position.For example: 1,2,3,textshould createPosition | Value-------------------------1|12|23|34|textand return a value based on the position. If there are more than 3 commas for example1,2,3,This string, though short, contains a commashould createPosition | Value-------------------------1|12|23|34|This string, though short, contains a commaand return a value based on the position. And if there are are less than 3 commas in the string passedFor example: 1,2 or NULL or 2:3.5 or This is a string with no commasshould createPosition | Value ------------------------- 1| (empty string) 2| (empty string) 3| (empty string) 4| (empty string)and return a value based on the position.This is what I wrote so far. CREATE function GetValueFromPosition (@Input nvarchar(4000), @position int)Returns nvarchar(4000)AsBegin -- Declare the return Variable Declare @ReturnValue nvarchar(4000) Select @ReturnValue = LTRIM(RTRIM(member_id)) From dbo.SplitString(@Input, ',') Where position = @position Return @ReturnValueEnd CREATE Function SplitString(@text varchar(8000), @delimiter varchar(1) = ',')-- This function splits a string of CSV values and creates a table variable with the values.-- Returns the table variable that it createsRETURNS @Strings TABLE( position int IDENTITY PRIMARY KEY, member_id varchar(8000))ASBEGIN Declare @index int Set @index = -1 WHILE (LEN(@text) > 0) BEGIN SET @index = CHARINDEX(@delimiter , @text) IF (@index = 0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index > 1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) SET @text = RIGHT(@text, (LEN(@text) - @index)) END ELSE SET @text = RIGHT(@text, (LEN(@text) - @index)) END RETURNEND I am trying to modify these according to what I need but its not working. Please help. Thank you.
View Replies !
Function To Create Comma Separated List From Any Given Column/table.
Hi,I'm sure this is a common problem.. to create a single field from awhole column, where each row would be separated by a comma.I can do this for a specified table, and column.. and I've created afunction using VBA to achieve a more dynamic (and very slow) solution..so I would like to implement it using a user defined function in sql server.The problems I'm facing are, that I can't use dynamic sql in afunction.. and I also can't use temporary tables which could build up a'standard' table from parameters given to then perform the function on.So, with these limitations, what other options do I have?Cheers,Chris
View Replies !
Insert Value List Doest Not Match Column List
HI... I need to do a simple task but it's difficult to a newbie on ssis.. i have two tables... first one has an identity column and the second has fk to the first... to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !! i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list" here is the script INSERT INTO Address( CepID, Street, Number, Location, Complement, Reference)Values ( ?, ?, ?, ?, ?, ? ) INSERT INTO CustomerAddress( AddressID, CustomerID, AddressTypeID, TypeDescription) VALUES( @@Identity, ?, ?, ? ) what's the problem ??
View Replies !
Split A Column
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 Replies !
Comparing Nullable Column With Int
Hi, I have a table name bla. PKEY id, int, NOT NULL group, int name, string, NOT NULL how do I compare them with int? for example the following data. 1, NULL, 'freelance' 2, 1, 'group1' 3, 2, 'group2' select * from bla where group<>1 <-- this fails? What is the proper SQL Statement for this? Regards, Max
View Replies !
Split A Column Into 2 Columns
Hi everyoneI guess this should be a simple question for the gurusI have a Data in a column which is to be places in 2 columns instead ofone. How do i go about doing it in MS SQL server? Could someone pleasehelp me. I could do it in access with an update query but things are alittle different in SQL server so I am a little lost.Eg.NameJohn?Doeto be split intoName LastNameJohn DoeThanks in advance.Prit
View Replies !
How To Split Three-value Column Into The Same Target?
Hi everyone, We've got a source file which owns three different values: 'A','B','M'. Where 'A' stands for "New Rows" and 'B' for "Delete rows" and 'M' for 'Update rows' Using Conditional Split task we can redirect each subset into a OLEDB Destination but we are wondering how can we do the same using only one OLEDB? We've got only one table. Thanks for your input and time,
View Replies !
Split Values From Within Column
I have been trying to separate firstname,last name,middle from name column Existing Format Column Name =FIRST,LAST M Desired First Last M I would llike to divide one column into three columns...How can i achieve it.. Please let me know
View Replies !
Split Column Into Severl Ones
Hello, I have a table which contains a column like that: Comment ----------------------------------------------------------------------- User: Toto Password: Toto-Toto I'd like to have in the same table: Comment User Password -------------------------------------------------------------------------------------------------------- User: Toto Password: Toto-Toto Toto Toto-Toto Do you have an idea of how to do it in SSIS? Thanks a lot for your help.
View Replies !
How To Split A Database Column ?
Hi, I have a column, for example Prod_ID count is 100 (contains Raw Matl & Finished Matl). I want to split this 2 columns as Raw Matl Finished Matl 60 40 Can anyone please help me how to do this in SQL Server. Thanks in Advance Rajesh
View Replies !
Split Data In Column
hai all, This is my first question to this forum. here is my situtation: I am into report testing I need to test a report for which i have write a query,iam using qery analyser for runing query Database : sql server tabel name :job_allocations column naME :technicain code Based on techincain code in joballocation tablei need to get technician cost from other table for the particular technician. Based on the technician code user chooses column will be updated if single data will be TC01 if more than one then data will be TC01:TC02:TC03 user can choose any number of techincian for a job MY problem is :How to split tha when there is multiple technician and calculate cost for the job Ineed it in single excecution query Table structure job_allocation table jobcardn_fk Technician_code jc01 TC01 jc02 Tco1:Tco2:Tc03...... I need it in jobcardno_fk TEchnician_code jco1 Tc01 jco2 Tc01 jco2 TC02 jc02 Tc03 TKs ands Regards Diwakar.R
View Replies !
Split One Column Into Multiple Columns
Hi all, I have a requirement like this , I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469 I have to split this into 3 more columns like(Address1,name,phoneno)-- Means i have 4 columns including Address Column.(Address,Address1,name,phoneno) Example: Address:Rajkot Address1:Univercity Road Name:Mr. K KK Tank Guntur Jal Bhavan PhoneNO:9843563469 How can i acheive this one with out data lose in Address Column. Thanks in advance.
View Replies !
Query Split Column In 2 Columns In SQL
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that. I have a table like this: Select Name from Cars; Result: Col1 BMWMercedesFordAudi But i like to make a query so it is displayed like this: Col1 Col2 BMW FordMercedes Audi So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
View Replies !
Query To Split A Database Column ?
How can i write a query to split a database column and shows 2 new columns. In my database column I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter and run another query. For example a database column with average number and range number. Thanks Daniel
View Replies !
Comparing Nvarchar(max) Column Using Like To Non-ASCII Range
Our database defines the long_value column as nvarchar(max). I want to find out which rows actually contain non-ASCII characters in that column, but this clause also returns rows with only ASCII characters:where long_value like (N'%[' + nchar(128) + N'-' + nchar(65535) + N']%') What am I doing wrong?
View Replies !
Help Split List Of Email Add Comma For Evry Email
need help split list of email add comma for evry email i have tabe "tblLogin" and in this table i have field emall like this emall ----------------------------------------- aaa@hhhh.mm nnn@hhhh.mm mmm@hhhh.mm need to do ilke this Code Snippet @list_email = (SELECT emall FROM tblLogin) --------------------------i get this -----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm @recipients = @list_email Code Snippet IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] ) BEGIN DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX) SET @xml =CAST(( SELECT FirstName AS 'td','', LastName AS 'td','' , Date_born AS 'td','' , Age AS 'td','' , BirthdayToday AS 'td','' , BirthdayThisWeek AS 'td' FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl> <tr> <td>name</td> <td>fname</td> <td>date</td> <td>age</td> <td>aaaaaaaaa</td> <td>bbbbbbbbbbbbbbb</td> </tr>' SET @body = @body + @xml +'</table></body></html>' EXEC msdb.dbo.sp_send_dbmail @recipients =N'rrr@iec.co.il', @copy_recipients='rrrrr@iec.co.il', @body = @body, @body_format ='HTML', @subject ='ggggggggggggggggggggg', @profile_name ='ilan' END ELSE print 'no email today'
View Replies !
How Do I Split A Column Result By A Nonalphanumeric Character?
I have a column that returns client numbers. The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization. In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number. EG: client #4334.1 would become 4334 for client # and 1 for categorization number or Client #424561.3 would become 424561 for Client # and 3 for categorization number. I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns. Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second. Is it possible to do this? I really need help on this one. Thank you :)
View Replies !
Split A Single Column Data In To 2 Columns
Hi This is probably a very basic question for most people in this group. How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure. Here is an example of what i want to acheive FName John?Doe FName LName John Doe thanks for the help prit
View Replies !
Split Column Data Into Multiple Lines
Hi, I have a scenario, where I have a string column from database with value as "FTW*Christopher,Lawson|FTW*Bradley,James". In my report, I need to split this column at each " | " symbol and place each substring one below the other in one row of a report as shown below . "FTW*Christopher,Lawson FTW*Bradley,James" Please let me know how can I acheive this?
View Replies !
How To Split A Delimited Column Into Mulitple Rows In The Dataflow?
I'm sure there is probably a very easy solution that I am just not seeing or can't Google... I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas? TIA, Matthew
View Replies !
How Can I Split Fields And Depending One Column Decide The Foreing Key
I´m wondering how to solve the following scenario with SSIS I have a CITY table and a STATE table, I have to load a file with the information regarding to the CITY: the state table is like this: StateCode(PK) stateLegalCode stateName ============= ============== ========= 1 01 Florida the city table is like this: citycode(PK) cityLegalCode cityname StateCode(FK) ============ ============= ======== ============= 1 1001 Quakertown 1 the file has the following information cityLegalCode cityName ============= ======== 01-1001 Quakertown ... how can I load the file into CITY table: 1-) with the file's cityLegalCode I have to split the string and if the two initial digits are 01 the registry must have 1 in the StateCode(FK). how can I do something like that using SSIS??? thanks
View Replies !
Could You Tell What's Wrong When I Split Table To The Target Partition Table?
Could you tell what's wrong when I split table to the target partition table?USE TEST--ADD FILEGROUP---------------------------------------------------------------------ALTER DATABASE TEST ADD FILEGROUP FG_01ALTER DATABASE TEST ADD FILEGROUP FG_02ALTER DATABASE TEST ADD FILEGROUP FG_03--ADD FILE--------------------------------------------------------------------------ALTER DATABASE TEST ADD FILE (NAME = DF_01,FILENAME = 'D:TESTDF_01.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_01ALTER DATABASE TEST ADD FILE (NAME = DF_02,FILENAME = 'D:TESTDF_02.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_02ALTER DATABASE TEST ADD FILE (NAME = DF_03,FILENAME = 'D:TESTDF_03.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_03--CREATE PARTITION FUNCTION---------------------------------------------------------CREATE PARTITION FUNCTION PF_HIS_HTTP_LOG(datetime)AS RANGE LEFT FOR VALUES ('20070101 23:59:59.997','20070102 23:59:59.997')--CREATE PARTITION SCHEME-----------------------------------------------------------CREATE PARTITION SCHEME PS_HIS_HTTP_LOGAS PARTITION PF_HIS_HTTP_LOG TO ( FG_01, FG_02, [PRIMARY])--CREATE PARTITION TABLE -----------------------------------------------------------CREATE TABLE HIS_HTTP_LOG ( USERID varchar(32) , USERIP varchar(15) ,USERPORT numeric(5,0) , OBJECTIP varchar(15) , OBJECTPORT numeric(5,0) , URL varchar(256) , HOST varchar(64) , DN varchar(64) , VISITIME numeric(5,0) , STARTIME datetime , ENDTIME datetime ) ON PS_HIS_HTTP_LOG(STARTIME)--INSERT DATA，PARTITION 1 20070101-------------------------------------------------DECLARE @i intSET @i = 1WHILE @i <= 100BEGININSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070101 13:25:26.100',121),GETDATE())SET @i = @i +1END--INSERT DATA ,PARTITION 2 20070102-------------------------------------------------SET @i = 1WHILE @i <= 200BEGININSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070102 11:25:26.100',121),GETDATE())SET @i = @i +1END--CREATE A TABLE -------------------------------------------------------------------CREATE TABLE TMP_HTTP_LOG( USERID varchar(32) , USERIP varchar(15) ,USERPORT numeric(5,0) , OBJECTIP varchar(15) , OBJECTPORT numeric(5,0) , URL varchar(256) , HOST varchar(64) , DN varchar(64) , VISITIME numeric(5,0) , STARTIME datetime , ENDTIME datetime ) ON FG_03--INSERT DATA TO TMP_HTTP_LOG 20070103-----------------------------------------------DECLARE @i intSET @i = 1WHILE @i <= 400BEGININSERT INTO TMP_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57', 6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070103 09:25:26.100',121),GETDATE())SET @i = @i +1END--ADD CONSTRAINT--------------------------------------------------------------------ALTER TABLE TMP_HTTP_LOGWITH CHECKADD CONSTRAINT CK001CHECK (STARTIME >= '20070103 00:00:00.000' AND STARTIME <= '20070103 23:59:59.997')--SPLIT RANGE ,SWITCH DATA----------------------------------------------------------ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED FG_03ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070103 23:59:59.997')ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3--==========================================�======================================Why is the error in step of“ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3�error infomation：message_id 4972，level 16，severity 1ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TEST.dbo.TMP_HTTP_LOG' allows values that are not allowed by check constraints or partition function on target table 'TEST.dbo.HIS_HTTP_LOG'.Please tell me why ? check constraints ?Thank you very much !
View Replies !
Comparing Data In Table With Validation Table
Hi all I have a customer table with a postcode and a suburb fields and cutomer info which is manually entered by data entry people... I am trying to compare the entries against a postcode table with the correct postcodes which have fields postcode and suburb and based on the postcode entered in the customer table it should be the same as the suburb in the postcode table, if they are not the same output them to a table for manual checking..How would I go about this thanks
View Replies !
Smarter Table Build - Might Split The Table
Hello,I am using SQL 2005 and Cognos' Data Manager. It is an ETL tool fordata warehousing.I have a problem with time it takes to load new changes, and I amseeking advice on a better way to manage the data.I have a table that tracks student attendance and it contains about 13million records. On a daily basis, there are 5,000 - 20,000 inserts and10,000 - 50,000 updates.The daily data comes for two different text files from my operationsystem; current and historical (CLSFIL and CLSHIS).The data is loaded into a staging area from the operational system,where data cleansing and other fields are added to the table.The final step is delivering the table to my target database, which isused for reporting.Heres the situation: I find it takes 45 minutes to do a relationalupdate, where only the records that changed in the last day will beloaded. However, if I choose the native API load instead of aRelational Load, it can load all 13M records in 7 minutes. The table isheavly indexedAt some point, the API load will take more time than the relationalload, (the changes and new records will remain a constant, but the filewill continue to grow).I'm seeking another solution is more efficient. I'm considering twotables for history and current and creating a view for reporting via aunion.This a good idea? How can I make the view effeicent to use the whereclause? Looking to bounce around ideas.Other Ideas?Thanks in AdvanceRob(I maintain the key relationships in the tool, not the tables. I knowI have lots to learn and improvments)CREATE TABLE "dbo"."F_BI_Class_Attendance_Detail"("CLASS_ATTENDANCE_ID" VARCHAR(50) NULL,"CLASSES_OFFERED_ID" VARCHAR(26) NULL,"CLASS_CAMPUS_ID" VARCHAR(10) NULL,"STUDENT_ID" CHAR(20) NULL,"FULL_CLASS_ID" CHAR(15) NOT NULL,"SESSION_ID" CHAR(10) NULL,"SECTION_ID" VARCHAR(5) NULL,"MEET_DT" DATETIME NULL,"MEETING" SMALLINT NULL,"PRESENT" CHAR(2) NOT NULL,"SESSION_SKEY" BIGINT NULL,"STUDENT_SKEY" BIGINT NULL,"CLASS_CAMPUS_SKEY" BIGINT NULL,"CLASSES_OFFERED_SKEY" BIGINT NULL,"LOAD_DT" DATETIME NULL,"COMPUTED_DT" DATETIME NULL);
View Replies !
Comparing Table Structure
I am writing an App to upload db based web Apps from one server to another. i.e Dev -> Testing -> Staging -> Production. I need to be able to compare the table structure of the source db tables to the target db tables, and the results processed in .Net. I have access to the syscolumns object in MS SQL, but I would like some hints as to the best way to do this. I could for instance get the columns and compare them, but the question is where is the best place to do that.
View Replies !
Big Table(?) Or Split Between Tables?
Hi Guys I have an application that runs on several sites that has a table with 36 columns mostly ints och small varchars. I currently have only one table that stores the data and five indexes and since the table on one location (and others soon) has about 18 million rows I have been trying to come up with a better solution (but only if needed, I dont think I have to tell you that I am a programmer and not an dba). The db file size with all the indexes is more then 10gb, in it self is not an problem but is it a bad solution to have it that way? The questions are: Are there any big benefits if i split it into several smaller tables or even smaler databases and make the SPs that gets the data aware that say 2006 years data is in table a and so on? Its quite important that there are fast SELECTS and that need is far more important then to decrease the size of the database file and so on. How many rows is okay to have in one table (with 25 columns) before its too big? Thanks in advance. Best regards Johan, Sweden. CREATE TABLE [dbo].[Cdr]( [Id] [int] IDENTITY(1,1) NOT NULL, [Abandon] [varchar](7) NULL, [Bcap] [varchar](2) NULL, [BlId] [varchar](16) NULL, [CallChg] [varchar](6) NULL, [CallIdentifier] [uniqueidentifier] NULL, [ChgInfo] [varchar](5) NULL, [ClId] [varchar](16) NULL, [CustNo] [smallint] NULL, [Digits] [varchar](32) NULL, [DigitType] [varchar](1) NULL, [Dnis1] [varchar](6) NULL, [Dnis2] [varchar](6) NULL, [Duration] [int] NULL, [FgDani] [varchar](13) NULL, [HoundredHourDuration] [varchar](3) NULL, [Name] [varchar](40) NULL, [NameId] [int] NOT NULL, [Npi] [varchar](2) NULL, [OrigAuxId] [varchar](11) NULL, [OrigId] [varchar](7) NULL, [OrigMin] [varchar](16) NULL, [Origten0] [varchar](3) NULL, [RecNo] [int] NULL, [RecType] [varchar](1) NOT NULL, [Redir] [varchar](1) NULL, [TerId] [varchar](7) NOT NULL, [TermAuxId] [varchar](11) NULL, [TermMin] [varchar](16) NULL, [Termten0] [varchar](3) NULL, [Timestamp] [datetime] NOT NULL, [Ton] [varchar](1) NULL, [Tta] [int] NULL, [Twt] [int] NULL, [DateValue] [int] NULL, [TimeValue] [int] NULL, [Level] [varchar](50) NOT NULL CONSTRAINT [DF_Cdr_Level] DEFAULT ('x:'), CONSTRAINT [PK_Cdr] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY] ) ON [PRIMARY]
View Replies !
Get Split Names From Id Of Other Table
Table :: master_Staff StaffID -Number StaffName-String Table :: master_Class ClassID-Number ClassTeacher-Number ( Forign Key of StaffID ) I have a set of StaffID saved using "," in the table class -------- 10,20,30 Now i want a Query to get the staff name -- hari, gopal, sekar , by using the classID ie:: select staffname from master_staff where staffid in ( select classteacher from master_class where classID = 1)
View Replies !
Deleting Data By Comparing To Another Table
I have an entry form allowing customers to enter up to 15 skus (productid) at a time, so they can make a multiple order, instead of enteringone sku, then submitting it, then returing to the form to submit thesecond one, and so forth.From time to time, the sku they enter will be wrong, or discontiued, soit will not submit an order.Therefore, when they are done submitting their 15 skus through the orderform, I want a list showing them all of those skus that came back blank,or were not found in the database.I'm doing this by creating two tables. A shopping cart, which holds allthe skus that were returned, and a holding table, that holds all theskus that were submitted. I want to then delete all the skus in theholding page that match the skus in teh cart (because they are goodskus) which will then leave the unmatched skus in the holding table.I'll then scroll out the contents of the holding table, to show them theskus that were not found in the database.(confused yet?)So what I want to do is have some sql that will delete from the holdingtable where the sku = the sku in the cart. I've tried writing this, butit dosn't work.I tiried this delete from holding_table where sku = cart.skuI was hoping this would work, but it dosn't. Is there a way for me to dothis?Thanks!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Help On Comparing And Modifying A Resultset Or Table
HI everybody need help badly on the following have to recordsets with the following with the following SQL SELECT place, count(minus)as countminus order by place group by place THE RESULT WOULD BE PLACE countminus ABC 10 DEF 50 HIJ 5 KLM 2 MNO 0 MY OTHER QUERY IS SELECT plus, count(plus) as countplus GROUP BY plus ORDER BY plus THE RESULT WOULD BE: PLUS COUNTPLUS ABC 10 DEF 20 my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS... with this condition.. I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF... THE RESULT WOULD BE THIS..... PLACE countminus COUNTPLUS ABC 10 10 DEF 50 20 HIJ 5 0 KLM 2 0 MNO 0 0 I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS
View Replies !
Comparing Table With Excel Spreadsheet
Hi all, I have two tables in SQL Server 2005 and excel sheet ( Office 2003). The colums of excel sheet are: name ,ssn, flagbit ( Note: Excel sheet contains data already) Columns of table_one and table_two are: name ,ssn I want to compare the ssn field from table_one and ssn in excelsheet_one, if it matches , then flagbit in excelsheet_one should say"T1". If i compare ssn field from table_two and ssn in excelsheet_one, if it matches, the flagbit in excelsheet_one should say "T2". Ex: Table_one (input) ---- excelsheet_one ( Output) ssn name ---- ssn name flagbit 11 NYC ---- 11 NYC T1 Both the tables refers same excelsheet_one and have to update the same flag bit column in excelsheet. Basically, i want to compare table and excel sheet, then if it matches, then update excel sheet. Does anybody how to do this. Any help will be greatly appreciated. Thanks
View Replies !
Comparing Rows Within The Same Table (duplicates)?
How do I only select rows with duplicate dates for each person (id)? (The actual table has approximately 13000 rows with approximately 3000 unique ids) p_key id date pulse -------------------------------------- 1 32 5/25/2006 80 2 32 5/25/2006 85 3 32 4/26/2006 81 4 32 6/15/2006 82 5 15 1/20/2006 75 6 15 3/25/2006 80 7 15 3/25/2006 83 Result table I am looking for: p_key id date pulse ------------------------------------ 1 32 5/25/2006 80 2 32 5/25/2006 85 6 15 3/25/2006 80 7 15 3/25/2006 83 Thanks.
View Replies !
SQL Optimistic (all In One Table Split Into Two Table)
I having a bit of confuse here. Can you please help me? I have about 5000 records all ready in oen table. Everything that I query is related to that table one way or the other. Now i having 2000 - 3000 more records to store in the database. In term of relation database then I can store the new data in a different table so I can can query it. Most of my queries are searching. So the question is is this better to store the data in another table or should store everything in the old table? Thanks a lot in advance for your help. I really do appreciate that. Maverick
View Replies !
Should I Split This 175 Million Record Table?
Hello,We maintain a 175 million record database table for our customer.This is an extract of some data collected for them by a third partyvendor, who sends us regular updates to that data (monthly).The original data for the table came in the form of a single, largetext file, which we imported.This table contains name and address information on potentialcustomers.It is a maintenance nightmare for us, as prior to this the largesttable we maintained was about 10 million records, with lesscomplicated updates required.Here is the problem:* In order to do the searching we need to do on the table it has 8 ofits 20 columns indexed.* It takes hours and hours to do anything to the table.* I'd like to cut down as much as possible the time required to updatethe file.We receive monthly one file containing 10 million records that arenew, and can just be appended to the table (no problem, simple importinto SQL Server).We also receive monthly one file containing 10 million records thatare updates of information in the table. This is the tricky one. Theonly way to uniquely pair up a record in the update file with a recordin the full database table is by a combination of individual_id, zip,and zip_plus4.There can be multiple records in the database for any givenindividual, because that individual could have a history that includesmultiple addresses.How would you recommend handling this update? So far I have mostlytried a number of execution plans involving deleting out the recordsin the table that match those in the text file, so I can then importthe text file, but the best of those plans takes well over 6 hours torun.My latest thought: Would it help in any way to partition the tableinto a number of smaller tables, with a view used to reference them?We have no performance issues querying the table, but I need somethoughts on how to better maintain it.One more thing, we do have 2 copies of the table on the server at alltimes so that one can be actively used in production while we runupdates on the other one, so I can certainly try out some suggestionsover the next week.Regards,Warren WrightDallas
View Replies !
T-SQL To Split Data From One Table Into Two Tables?
What's the best way to convert a large set of records from a simple schema where all fields are in one table to a schema where fields are split across two tables? The two table setup is necessary for reasons not worth getting into here. Doing this via cursor is pretty straightforward, but is there a comparable set-based solution? Here are sample create table commands. Obviously, the example below is simplified for discussion purposes. -- One record from here will produce a record in TargetParentRecords and a record in TargetChildRecords for a total of two records. CREATE TABLE OriginalSingleTableRecords ( ID INT IDENTITY (1, 1) NOT NULL, ColumnA VARCHAR(100) NOT NULL, ColumnB VARCHAR(100) NOT NULL, CONSTRAINT PK_OriginalSingleTableRecords PRIMARY KEY CLUSTERED (ID) ) CREATE TABLE TargetParentRecords ( ParentID INT IDENTITY (1, 1) NOT NULL, ColumnA VARCHAR(100) NOT NULL, CONSTRAINT PK_TargetParentRecords PRIMARY KEY CLUSTERED (ParentID) ) -- Each row in this table must link to a TargetParentRecords row CREATE TABLE TargetChildRecords ( ID INT IDENTITY (1, 1) NOT NULL, ParentID INT NOT NULL, -- References TargetParentRecords.ParentID ColumnB VARCHAR(100) NOT NULL, CONSTRAINT PK_TargetChildRecords PRIMARY KEY CLUSTERED (ID) )
View Replies !
Partition View (Table Split)
Hi All I am wondering if you guys can help me, I have table which has more than 7 million records of sales data, it has 7 years of data, I have created 7 diffrent tables for each fiscal year with check contraints of sales_date. All this table combined in one view, when I am doing select for current fiscal year according to SQL server standard it suppose to scan only for fiscal year table but it's doing for all tables eventhough I Select only current fiscal year, I am not very sure whether I am missing something the way I have created tables and view, If you guys can give me some suggestion that will be great. Thanks in advance Regards Leong
View Replies !
Please - Split Select From Table To Comma 1,2,3,...
help please i have this table number_row fld_index vtext ---------------------------------------- 1 101 a 2 101 b 3 101 c 4 102 d 5 102 e 6 102 f 7 103 g 8 103 h 9 103 i ...... ...... .... now i need to do this SELECT fld_index FROM table_index GROUP BY fld_index ---------------------------- and i get 101 102 103 104 how split this select and do this in('101' ,'102','103','104') ------------------------------ Code Snippet DECLARE @aaa set @aaa =(SELECT fld_index FROM table_index GROUP BY fld_index) set fld1 = CASE WHEN fld1 in(@aaa ) then '*' else fld1 end ---- need to split the @aaa ,'101' ,'102' , '103','104' , ---------------------------------------------------------------------------------------------------------------------------- instead of this update update [dbo].[tb_pivot_big] set fld1 = CASE WHEN fld1 in('101' ,'102','103','104') then '*' else fld1 end , fld2 = CASE WHEN fld2 in('101' ,'102','103','104') then '*' else fld2 end , fld3 = CASE WHEN fld3 in('101' ,'102','103','104') then '*' else fld3 end , fld4 = CASE WHEN fld4 in('101' ,'102','103','104') then '*' else fld4 end , fld5 = CASE WHEN fld5 in('101' ,'102','103','104') then '*' else fld5 end from [dbo].[tb_pivot_big]
View Replies !
Read The Filename, Split It And Put It In A Table
Hello I'm working on a package which loops through each xml file in a folder. The name of each xml file is put in variable. The format of the filename is something like "part1_part2_part3.xml" I need to store the 3 parts in three different columns of table A The content of the xml file needs to be manipulated ("." needs to be replaced with ",", ....)and put in serveral columns in tableB It's not clear to me yet how to start this but my main concern is read the three parts of the filename. I don't find any task in SSIS which could help me with that. Could someone give me some pointers? Many thanks! Worf
View Replies !
|
TRACKER
