How To Assign Unique PKs And FKs On Split Of Txt Rows Into DB Tables
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 Complete Forum Thread with Replies
Related Forum Messages:
How To Split Out Table Rows Into 3 Tables
I imported all rows of my txt file using SSIS 2005 into a table. I am now trying to figure out how to split out the header, payment rows, and maintenance rows. First, some information. An example of table results is here: http://www.webfound.net/split.txt The table has just one field of type varcha(100) because the incoming file is a fixed length file at 100 bytes per row The header rows are the rows with HD in them...then followed by detail rows for that header (see here http://www.webfound.net/rows.jpg). I need to 1) Split out the header into a header table 2) Split out the maintenance rows (related to the header) into a maint table 3) Split out the payment rows (related to the header) into a payment table I'll need to maintain a PK/FK relationship between each Header and it's corresponding maint and payment rows in the other 2 tables. To determine if it's a payment vs. maintenance row, I need to compare chars 30 - 31. If it contains 'MT' then you know it's a maintenance row, else it's a payment row. How in the hell do I do this???
View Replies !
How To Split And Get The Unique Ids
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 Replies !
Split Rows
not sure if this is possible... but lets say i make a select like select products, stock from table and my rs is chair | 1 couch | 3 lamp | 2 is there anyway in the select to make any row that has stock of more than 1 to make a new row... so my rs would come back as chair couch couch couch lamp lamp Any info would be helpful... Thanks, ~ moe
View Replies !
How To Split Data Into Two Rows
I have a query that returns a table similar to: State Status Count CA Complete 10 CA Incomplete 200 NC Complete 20 NC Incomplete 205 SC Incomplete 50 What sort of query will allow me to reformat the table into: State Complete Incomplete CA 10 200 NC 20 205 SC NULL 50
View Replies !
Can I Split Matrix Into 2 Or 3 Rows?
Hi All I have a matrix with single row. The no. of column varies and sometimes goes to 10-15. So it goes to next page and while exporting it inserts blank pages when exported to PDF. I need the column width at least 2.5cm. I need to break the matrix to next row instead of it going to next page say after 6th or 8th column. I tried to work with the example given in the site http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx by Chris Hays. But it is showing matrix for each Row Group, which doesn't meet my requirement. I had a work around which worked by putting two matrix one below the other and filtering the columns to be shown in each matrix. If anybody faced this issue or anybody solved the issue kindy reply which will be very helpful for me. One more doubt, Can I get the Column number of the matrix? Thanks in advance Dileep
View Replies !
Split One Field In Multiple New Rows
HiHo, just a beginners question: I have the following row with 2 fields: Field 1: Task A Field 2:´ 1;2;3;4 The number of semicolon divided elements in Field 2 is variabel. I would like to create new rows like: Row 1 Field 1: A Field 2: 1 Row 2 Field 1: A Field 2: 2 Row 3 Field 1: A Field 2: 3 Row 4 Field 1: A Field 2: 4 I think I should use a Foreach Loop. But I don't exactly how to do it? best regards Chris
View Replies !
How Can I Split The Rows In 2 Textboxes - Urgent
Hi, I have a report and its been populating from a sproc. and i have 2 text boxes called both of them are poplulated by Fields!Investment Names, but right i can display the data left to right but i want to display the Data starting top to bottom and then towards the right. I tried grouping the data in this way for one text box = CountRows()/2 > 10 . and this shows all the records one below the other, so is there a way that i can display half the records in one text box and the other half in the other text box. I am going kinda nuts over this. Can someone please help me. Regards Karen
View Replies !
One For The SQL Gurus: Split A Delimited Field Into Rows
Hi. I'm trying to write an SQL Query that will take a delimited field and return each item as a row. Example Take the AuthorizedRoles and TabID fields from the Tabs table AuthorizedRoles TabID 0;11;__________1 0; 15 ;17;______6 -2;____________7 I would like to return a unique record for each Authorized Role AuthorizedRole TabID 0____________1 11___________1 0____________6 15___________6 17___________6 -2___________7 Any ideas? Cheers Dave
View Replies !
SQL View To Split Rows In Single Table...
I've been searching the forums and Google for some help splitting up rows in a table without any luck. I'm not quite sure what to even look for I have a table is MSSQL 2000 that looks as follows: Code: id custnum b1_email b2_email b1_sub b2_sub ------------------------------------------------------------------------ 1 123456 b1@host1.com b2@host1.com 0 0 2 654321 b1@host2.com b2@host2.com 1 0 3 321654 b1@host3.com b2@host3.com 0 1 Now... I am hoping create a view that splits these rows up so that only a single email address is on each row. I'd like to split it up as follows: Code: custnum email sub ---------------------------------- 123456 b1@host1.com 0 123456 b2@host1.com 0 654321 b1@host2.com 1 654321 b2@host2.com 0 321654 b1@host3.com 0 321654 b2@host3.com 1 Any help would be great! I imagine some sort of join command can be constructed using a single table?
View Replies !
Split One Row Into Multiple Rows Based On Time Elements
I'm dealing with a problem. The record information example DateTimeStart , DateTimeEnd , action , duration (seconds) 2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600 what i want is for every half hour between start and end a record 10.30 action1 11.00 action1 11.30 action1 how can i create this, i'm a little stuck on 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 !
Getting Unique Rows
Is it possible to use the DISTINCT clause on just one field in the SELECT statement? The following SQL statement causes an error: SELECT DISTINCT appt.ref, appt.notes FROM Appointments appt ...because DISTINCT can't be used on the notes field as it of type 'text'. How can I focus the DISTINCT keyword on just the ref field? (I know ref is the primary key, so this example wouldn't need the DISTINCT keyword, but I've simplified a much more complex statement) Paul
View Replies !
How To Get Unique Combination Of Rows
Hi, Following is my table:Bets BetId GameID 500 108 500 109 501 108 501 109 501 110 502 108 502 109 I want BetId 500 and 502 to be returned as result if i give select criteria where game id = 108,109. Pls.Note: It should not return BetId 501 in the result, since it belongs to different combination(108,109,110). Similarly if i give, select criteria where game id =(108,109,110) it should return BetId 501.not the 500 and 502..which is different combination.. Hope i clarified my problem..pls help me in this regard.Thanks a lot...
View Replies !
Getting Unique Rows From The Resultset
Hi, I am trying to do a join which involves more than 3 tables. One is a parent table and the other two table have 1:n relationship with that parent table. But duplicate records are being returned in the resultset. How do I eliminate duplicate records? This is my query. SELECT table1.* from table1 left outer join table2 on table1.id=table2.id left outer join table3 on table1.id=table3.id I tried doing DISTINCT here but with no success. SQL with distinct clause. SELECT distinct table1.id, table1.* from table1 left outer join table2 on table1.id=table2.id left outer join table3 on table1.id=table3.id This is the error I get: The text data type cannot be selected as DISTINCT because it is not comparable Please help.
View Replies !
Duplicate Key But Unique Rows.
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique. How do I filter out only the duplicate zips. Randy Garland
View Replies !
Retrieving Unique Rows
I have the following sql: SELECT DISTINCT patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date FROM patient LEFT JOIN patient_record ON patient_record.patientID = patient.patientID WHERE (sub_categoryID = 4 OR patient_record.allocated = 4) AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5) GROUP BY patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date This brings up duplicate records, my aim is to bring distinct records, now if I take out the other returned fields after patientID and using the following sql: SELECT DISTINCT patient.patientID FROM patient LEFT JOIN patient_record ON patient_record.patientID = patient.patientID WHERE (sub_categoryID = 4 OR patient_record.allocated = 4) AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5) GROUP BY patient.patientID This bring up distinct results, but I need to retrieve the other fields from the database i.e. patientFirstName and patientLastName Please can you help.
View Replies !
Split Tables And Keep ID
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 Replies !
Selecting Unique Rows In A Join
I got the following query:SELECT TOP (8) ext.extID, ext.Quote, ext.sourceTitle, ext.extRating, gf_game.gameID, gf_game.catID, gf_game.URL, gf_game.TitleFROM gf_game_ext AS ext INNER JOIN gf_game ON gf_game.gameID = ext.gameIDWHERE (ext.Approved = 1)ORDER BY ext.extID DESC which is e.g. producing this output: 6000 -some text- Title 90 1960 2 tom-cl tom cl5999 -some text- title 90 1960 2 tom-clcl asdf5998 -some text- title 90 1959 2 tom-cl-cl asdfWhat I'd like to do now is to filter out the duplicate GameIDs (= 1960) so that just one unique row with the gameid 1960 is remaining. If I put in a SELECT DINSTINCT TOP(8) it just counts for the table ext, but I need it to count for gf_game.gameID - is that possible?Thanks a lot!
View Replies !
How Do I Get Unique Rows Based On StudentID
I am a beginner at SQL so thanks ahead of time.....How do I get unique rows based on studentID? Distinct and group by don't seem to workDESIRED RESULTSStudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 640484566 TERE BALDERAZCURRENT SQL AND RESULTS.....SELECT ClassRosterRecID, StudentDataRecID, StudentDataKey, StudentID, FirstName, LastName, CurrentGrade, Gender, Ethnicity, EconDisadvantaged, TitleI, Migrant, LEP, Bilingual, ESL, SpecialEducation, GiftedTalented, AtRisk, CareerTech, Dyslexia, LastName + ', ' + FirstName AS LastNameFirstName, EconDisadvantagedSort, TitleISort, MigrantSort, LEPSort, BilingualSort, ESLSort, SpecialEducationSort, GiftedTalentedSort, AtRiskSort, CareerTechSort, DyslexiaSort, DistrictID, CampusID FROM vClassDemographicsDetail WHERE (DistrictID = '057910') AND (CampusID = '057910101') AND (LastName LIKE '%BALDERAZ%')StudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 640484566 TERE BALDERAZ640484566 TERE BALDERAZ640484566 TERE BALDERAZ
View Replies !
Unique Rows Of Data Query
How would I get the unique email addresses and its associated row of data from a SQL Server table that has no unique fields defined? If there is a duplicate email address then only show the first one and not the other rows with the same email address. Example table and data UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997623 KAIS-22 A.KAIS-22@SampleISD.org997624 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.ZKAIS-22@SampleISD.org997627 KAIS-22 AX.KAIS-22@SampleISD.org Result UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.KAIS-22@SampleISD.org Thanks
View Replies !
Union Query - How To Get Unique Rows
Hello everyone! I've got the challenging task of taking a union query which returns a data set similar to this (it's proprietary data, so I had to make use of this sample data set). The problem is that there is a lot of repetition of product names and descriptions, because each product may have several locations and customer priceing levels and such. This union query populates an Excel spreadsheet and gets posted on the company intranet site for management to view. I'd like to remove all the duplicate names, so that it would appear as the second set of data, which is less redundant. I would definitely appreciate advice on how to do this. Thanks! Name Price Advance ------------ --------------------- --------------------- business 19.9900 5000.0000 business 11.9500 4000.0000 business 2.9900 10125.0000 business 19.9900 5000.0000 mod_cook 19.9900 9.0000 mod_cook 2.9900 15000.0000 Name Price Advance ------------ --------------------- --------------------- business 19.9900 5000.0000 11.9500 4000.0000 2.9900 10125.0000 19.9900 5000.0000 mod_cook 19.9900 9.0000 2.9900 15000.0000 popular_comp 22.9500 7000.0000
View Replies !
Select Unique Rows For All Columns
Using DISTINCT with SELECT have effect only for one column. But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work. Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows Select DISTINCT a4 From Y ---> " 85 rows Any thoughts? Jorge3921
View Replies !
Selecting & Then Inserting Unique Rows
As a beginner i am having trouble with this. i have two different tables , both have a name column, nvarchar datatype. I would like to select from table B all the rows which contain a name which is not in table A. Then insert these rows, into table A tried a few different ways & just keep getting strange errors that refer to courier font ?? SQL Team Your my Hero !
View Replies !
Design -- Should This Be Split Up Into A Few Tables?
I'm grappling with this design problem right now: I have a table of users. Every user has an e-mail address and (hashed) password. Some of those users work for a company, and some of them do not. Of those who do not work for a company, some are salespeople who sell to one or more companies. Some users are simply administrators who don't work for a specific company. So here's what my users table looks like right now: "UserID, Email, Password, CompanyID (Nullable), IsAdmin" And here's my companies table: "CompanyID, CompanyName, SalespersonID" Of course, I could separate it out and make a Users table, an Employees table, and a Salespeople table. The way the relationship works out, though, I could use the same ID number for all three tables, and that indicates to me that perhaps they all belong in the same table. It seems silly, after all to have a Salespeople table whose only field is "UserID." Two factors of the first design concern me: First is the fact that a salesperson could also have a company. I guess I could write a check constraint to prevent this, but doesn't having the companyID in the Users table violate a normalization rule? Maybe? The second is the fact that the Companies table relies upon Users, which in turn relies upon Companies. In OOP, this usually isn't a good thing, but I'm not sure whether it's cause for concern in a relational database. Anyway, I really don't know what I should be doing with this design. Any suggestions? Thanks in advance, -Starwiz
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 !
Selecting The Rows Based Off Of Unique Columns
Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ... There will be entries where there can be duplicate customerFirst and customerLast names. I would like to just return a single entry of the duplicate names and all associated row information. IE: [customerID], [customerFirst], [customerLast], [ email], [program] 01 Bill Smith bill.smith@hotmail.com ymca 02 Bill Smith bill.smith@hotmail.com Sports 03 jon doe jon.doe@hotmail.com AAA 04 jon doe jon.doe@hotmail.com Ebay 05 Paul Sprite paul.sprite@hotmail.com Rec Desired Returned result: 01 Bill Smith bill.smith@hotmail.com ymca 03 jon doe jon.doe@hotmail.com AAA 05 Paul Sprite paul.sprite@hotmail.com Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); dAdapter.Fill(pocDS, "Data Set"); However this is throwing up an error when i build the app: An expression of non-boolean type specified in a context where a condition is expected, near ')'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An expression of non-boolean type specified in a context where a condition is expected, near ')'. Source Error: Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison 1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry
View Replies !
Insert Unique Rows In Temp Table
i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table. MemberID + Month + Year should ne unique in Temp table
View Replies !
Deriving Unique Rows From Historical Data
My application is to capture employee locations.Whenever an employee arrives at a location (whether it is arriving forwork, or at one of the company's other sites) they scan the barcode ontheir employee badge. This writes a record to the tblTSCollected table(DDL and dummy data below).The application needs to be able to display to staff in a control roomthe CURRENT location of each employee.[color=blue]>From the data I've provided, this would be:[/color]EMPLOYEE ID LOCATION CODE963 VB002964 VB003966 VB003968 VB004977 VB001982 VB001Note that, for example, Employee 963 had formerly been at VB001 but wasmore recently logged in at VB002, so therefore the application is notconcerned with the earlier record.What would also be particularly useful would be the NUMBER of staff ateach location - viz.LOCATION CODE NUM STAFFVB001 2VB002 1VB003 2VB004 1Can anyone help?Many thanks in advanceEdwardNOTES ON DDL:THE BARCODE IS CAPTURED BECAUSE THE COMPANY MAY RE-USE BARCODE NUMBERS(WHICH IS DERIVED FROM THE EMPLOYEE PIN), SO THEREFORE THE BARCODECANNOT BE RELIED UPON TO BE UNIQUE.THE COLUMN fldRuleAppliedID IS NULL BECAUSE THAT PARTICULAR ROW HAS NOTBEEN PROCESSED. THERE ARE BUSINESS RULES CONCERNING EMPLOYEE HOURSWHICH OPERATE ON THIS DATA. ONCE A ROW HAS BEEN PROCESSED FORUPLOADING TO THE PAYROLL APPLICATION, THE fldRuleAppliedID COLUMN WILLCONTAIN A VALUE. IN THE PRODUCTION SYSTEM, THEREFORE, ANY SQL ASREQUESTED ABOVE WILL CONTAIN IN ITS WHERE CLAUSE (fldRuleAppliedID IsNULL)if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSCollected]GOCREATE TABLE [dbo].[tblTSCollected] ([fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTimeStamp] [datetime] NULL ,[fldRuleAppliedID] [int] NULL ,[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOINSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB001', '2005-10-18 11:59:27.383', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB002', '2005-10-18 12:06:17.833', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB001', '2005-10-18 12:56:20.690', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB002', '2005-10-18 15:30:35.117', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB003', '2005-10-18 16:05:05.880', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 11:52:28.307', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB002', '2005-10-18 13:59:34.807', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 14:04:55.820', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB003', '2005-10-18 16:10:01.943', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB001', '2005-10-18 11:59:34.307', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB002', '2005-10-18 12:04:56.037', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB004', '2005-10-18 12:10:02.723', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (977, 'VB001', '2005-10-18 12:05:06.630', 96879)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (982, 'VB001', '2005-10-18 12:06:13.787', 96697)
View Replies !
Unique Index Returns Duplicate Rows
We are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique. select top 100 s.*, x.col1 from Table_1 s left outer join Table_2 x on x.col1 = s.col1 and x.sys1 = 'SYSTEM0' Unfortunately this query returns duplicate rows. And every time the result is different But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups. Any ideas? Thanks Steve
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 !
Split Single File Over Two Tables.
I have an input file with fixed-width columns that I want to import into two tables.. 5 of the input columns go to 1 table and the remaining 15 go to another table. What's a good way to do this in SSIS? TIA, Barkingdog
View Replies !
Split Row's Columns Across Multiple Tables
Hello, Hoping someone here can help. Perhaps I'm missing something obvious, but I'm surprised not to see a data flow task in SSIS for splitting *columns* to different destinations. I see the Conditional Split task can be used to route a *row* one way or another, but what about columns of a single row? As a simple and somewhat contrived example, let's say I have a row with twelve fields and I'm importing the row into a normalized data structure. There are three target tables with a 1-to-1 relationship (that is, logically they are one table, but physically they are three tables, with one of them considered the "primary" table), and the twelve input fields can be mapped to four columns in each of the three tables. How do I "split" the columns? The best way I can see is to Multicast the row to three different OLE-DB Destinations, each of which inserts to one of the three target tables, only grabbing the four fields needed from the input row. Or should I feed the row through three successive OLE-DB Command tasks, each one inserting into the appropriate table? This would offer the advantage, theoretically, of allowing me to grab the identity-based surrogate primary key from the first of the three inserts in order to enable the two subsequent inserts. Thoughts? Thanks in advance, Dan
View Replies !
To Retrieve And Delete The Dupilicate Rows From Which Does Not Have Any Unique Fields
is there any way to Retrieve duplicate rows from a table which does not have any unique columns. that is a select should be written in such a way that it returns all the duplicate rows. a delete statement should written to delete those duplicate rows. if there any seudo colums in sql server as in oracle. if not how to implement the concept. that is there should be a dummy column in the result set of the select which should display the row number like the seudo column rownum in oracle.
View Replies !
Unique Key In 4 Tables
Hi! I'm not sure how to resolve this problem: I've got 4 tables: - Table1 (UniqueCode, Col11, .. , Col1N) - Table2 (UniqueCode, Col21, .. , Col2M) - Table3 (UniqueCode, Col31, .. , Col3X) - Table4 (UniqueCode, Col41, .. , Col4X) Each TableX has the same column: UniqueCode and its column's value can't be duplicated in the tables. Ex. Insert Table1 Values ('Code1', 'Ex1', ... ) Insert Table2 Values ('Code1', 'Ex2', ... ) -> Error! 1) Should I control this programming a Trigger? 2) Would be better to do a new Table? TableUnique( UniqueCode, IdTable1, IdTable2, IdTable3, IdTable4) .. for example?? Thanks!!!
View Replies !
ERROR - The Row Value(s) Updated Or Deleted Either Do Not Make The Row Unique Or They Alter Multiple Rows.
i am getting the above error on my database i have 2 rows with the same info on and another 2 with the same info on. example: ID username password 1 bob bob 1 bob bob 1 john john 1 john john I know this is a fault with ms sql 2005 however how do i fix it? Ive found this link which explains everything but how do i start a query. I tried clicking on new query and copying the code. What is table1 meant to be? the database is dbl.tbl_admin. It wont find my database. Im not sure how to do it anyway. I need to change it though as its my admin password and Ive given it out to web design companys http://geekswithblogs.net/allensb/archive/2006/07/27/86484.aspx Can some 1 read the above page and give me full instructions, I dont know what im doing thanks info@uktattoostudios.co.uk
View Replies !
Update Rows To Resolve Issues About Duplicate Keys On Create Unique Index
Hi there ...here comes a tricky one. I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables. This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one! I can find those rows by SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0 WHERE EXISTS ( SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1 WHERE table0.ParentREF = table1.ParentREF AND table0.UniqueName = table1.UniqueName AND table0.OID != table1.OID ) ORDER BY ParentREF, UniqueName, ModifiedUTC desc ...but I struggle to make the required SQL (SP?) to update the "invalid" rows. Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged! ATM the preferred (cause easiest) way is to rename the invalid rows with UniqueName = OID because if I use any other name I risk to create another double entry. Thanks in advance to whoever can help me
View Replies !
Unique Values Between Two Tables.
Hi,I have two tables such that in each table I need to make sure thatcolumn x in table A and column y in table B have a unique valuesmeaning that a user cannot insert a value to column A if its alreadyexist in column B and vice versa.How can I enforce it? Please remember that this two different tables.Thanks,Ori.
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 !
Unique Fields In SQL Server Tables
I feel sure that this is a very naive question, but it is freeky me out that I can solve it. With SQL server what is the easiest way to make a field other than the Primary key unique?Would appreciate help with this. ThanksPaul
View Replies !
Merge Tables On Unique Key Using Different Columns
hi guys, i have 3 tables (T1, T2, T3), each with the same structure: ID1 -> char(10) ID2 -> char(12) NULL ID3 -> char(10) Value1 -> money Value2 -> money Value3 -> money Note1 -> Text Note2 -> Text ID1+ID2+ID3 is the clusterd unique key in each table what i want: ID1, ID2, ID3 (with distinct occurencies of all 3 tables), T1.Value2, T2.Value2, T2.Value3, T3.Value1, T3.Note1 what i tried is to get all possible rows with SELECT ID1,ID2,ID3 FROM T1 UNION SELECT ID1,ID2,ID3 FROM T2 UNION SELECT ID1,ID2,ID3 FROM T3 but i dont know how to join or add the other columns. maybe with WITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ... any ideas? thx
View Replies !
Trying To Identify A Unique Record On Two Separate Tables
Hello, I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage. The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2 While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index. Contents RowGUID (unique key)ContentGUIDVersionViewsRatingComments................RowGUID ContentGUID (unique key)Version (unique key)Description..... Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database. Any advice, thoughts or comments would be helpful. Thanks,P.
View Replies !
Assigning Unique Sequence Numbers Across Different Tables
I have a procedure which updates a sequence number in a table such as the one below. Seq Sequence_Id ------ ------------------ NextNum 1 This is the procedure ... create procedure DBO.MIG_SYS_NEXTVAL(@sequence varchar(10), @sequence_id int) as begin update mig_sys_sequences set @sequence_id = sequence_id = sequence_id + 1 where seq = 'CSN' return(@sequence_id) end The purpose of this is to generate a sequential number each time the procedure is called. This number would then be used in a number of different tables to allocate a unique id so that the id is unique across the different tables. 1). What is the most efficient way of allocating these unique ids? The tables that I plan to update will already be populated with data. 2). How would I call the above procedure from an UPDATE statement? Many thanks, Fred
View Replies !
Inserting Unique Values Into A Different Tables If They Don't Exists Already.
Hi I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want. Code Snippet SET NOCOUNT ON INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID) SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7' WHEN 2 THEN '8' WHEN 3 THEN '9' WHEN 4 THEN '10' END FROM Members M INNER JOIN _MemberProfileLookupValues ML ON M.MemberID = ML.MemberID WHERE M.Active = 1 AND OptionID <> 6 When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already. Any ideas / help will be greatly appreciated. Please help. Kind Regards Carel Greaves
View Replies !
Child Tables Accessing UNIQUE Constraint.
What query gives me the list of child tables accessing a unique key (But Not PK) for a given table? For. E.g. In the folloiwng scenario I should get o/p as t3 create table t1 (id1 int primary key , id2 int unique ) create table t2 (id1 int references t1(id1)) create table t3 (id2 int references t1(id2)) ------------------------ I think, therefore I am - Rene Descartes
View Replies !
How Do I Copy Data From Similar Tables Knowing Unique ID Fields
I have two tables in my database called CartItems and OrderItems. Istore all of a session's shopping cart items in the CartItems tableusing the sessionID as the identifier (called cartID in my DB). Afteran order is placed and is approved, I would like to copy all of theitems in the CartItems table for that given cartID to the OrderItemstable given a new orderID.I will know the cartID and orderID ahead of time and would like tosend them both into a stored procedure and have the transfer takeplace.Example:take this data...CartItems (table)--------------------------------------cartID | itemID | quantity | price--------------------------------------12345 2 1 12.9512345 7 2 17.95and make it this data...OrderItems (table)--------------------------------------orderID | itemID | quantity | price--------------------------------------00001 2 1 12.9500001 7 2 17.95via some stored procedure that I send (@cartID,@orderID)Any help would be greatly appreciated!!
View Replies !
Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.
Hi, I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error. e.g MOB 401.908.804 - Fails 0401.907.324 - okay 8239 9082 (pager) - fails Anyone got an idea on how to fix this???? Regards.. Peter.
View Replies !
|
TRACKER
