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 Complete Forum Thread with Replies
Related Forum Messages:
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 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 !
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 !
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 !
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 !
Transferring Rows From One Table To 2 Tables
Hi, I have a table in Sql 2005 called Customers CustomerId CustomerName CustomerAge CustomerRank CustomerStCode I have to transfer the records into 2 tables CustomerMaster CustomerId CustomerStCode CustomerDetails CustomerId CustomerName CustomerAge CustomerRank I have to pick up a row from Customers and transfer it to CustomerMaster and CustomerDetails. CustomerId of CustomerMaster will be the CustomerId of CustomerDetails while transfer. How to do this? thanks
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 !
Copying Rows From Multiple Tables To A Single Table
Hi, I have 3 tables with the follwing schema Table <Category> { UniqueID, LastDate DateTime } Assume the follwing tables with data following the above schema Table Cat1 { 1, D1 2, D2 3, D3 } Table Cat2 { 2, D4 3,D5 4, D6 } Table Cat3 { 1, D7 3,D8 5,D9 } I have a Master and the schema is as follows Table master { UniqueId, Cat1 DateTime, -- This is same as the Table name Cat2 DateTime, -- This is same as the Table name Cat3 DateTime -- This is same as the Table name } After inserting the data from all these 3 tables, I want the my master table to look like this Table Master { UniqueId cat1 cat2 Cat3 ------------ --------- ------- ----------- 1 D1 NULL D7 2 D2 D4 NULL 3 D3 D5 D8 4 NULL D6 NULL 5 NULL NULL D9 } Please remember the column names will be same as that of table names can any one pelase let me know the query t o acheive this Thanks for your quick response ~Mohan Babu
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 !
Insert Multiple Rows To Table Based On Values From Other 2 Tables.
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT. I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId". What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display. Sample: ProjectId - SiteId 1 - 5 1 - 9 1 - 16 1 - 18 1 - 20 1 - 27 1 - 31 ProjectId stays the same, only values for SiteId are being different. I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
How To Split Cells In A Table Format
Hi Guys, I have this table formatting problem; I have a table report (with rows expanding) say sales report for items. In this report each item has 2 rows. I want to split the send row as shown below. So the results of the report should have the format below. rownumber ITEMNUNBER COST Sales On Hand 1 A CY4567 CY6780 CY567 2 A LY4678 LM678 LY7999 LM789 LY500 LM10 3 B 4 B 5 C 6 C IN this example for each item we show current year, last year and Last month Cost,sales and on hand. This is not a cross tab and number of items onthe report varies. In this example its 3 items but next time when you run it could be 100 items. Any help appreciated Sonny
View Replies !
Importing Split Data Into Table
Hi, I have Data split into 3 text files with 3 fields repeated in each to link then (key). I want to import this data into one table. I used DTS to create 3 tables with the data. Now i want to combine the 3 tables into only one (that i already created). How can i do this? Note: the field names in the source tables are different from the destination table. Thanks Guy
View Replies !
To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal
Hi, When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message. How can we handle transactions in SSIS 1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS. Any sort of help would be highly appreciated. Thanks,
View Replies !
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. Thetable holds the question and the possible choices in a varchar fieldseparated 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 portionanswer 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_intlistThe 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 whereANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (selectvalu from dbo.iter_intlist...It seems I might have to join or something, I am just a little lostright now.Here is my proc.ALTER procedure search_detailed_get_ids@question_id as integer,@answers as varchar(8000),@member_ids ntextasdeclare @v as varchar(8000)--get the delimited string of all possible answersset @v = (select bind_data from profiles_questions where question_id =@question_id)--prepare it for the function only accepting 1 charset @v = replace(@v, '||', '|')--gimme all members that matchselect member_id from profiles_answers where question_id = @question_idand 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, ','))returngo
View Replies !
Split Wide, Denormalized Table Into Normalized Structure
Thanks for reading. I'm building a system that imports data from several source, Excel files, text files, Access databases, etc. using DTS. The entire process revolved around MS SQL Server, by the way. I figured I would create denormalized tables that mirror the Excel and flat files, for example, in structure, import data to those, clean up and remove duplicates there, then break those out into my normalized table structure later. Now I've finished the importing part (though this is going to happen once a week) and I'm onto breaking up the denormalized tables. I'm hesitating because I'm not sure I've made the best decisions in terms of process, etc. I've decided to use cursors to loop over the denormalized tables and use batch insert statements to push data out to the appropriate tables. Any comments? Suggestions? All is welcome. I'm specifically interested in hearing back on the way I've set up the intermediate, denormalized tables and how I'm breaking them up using cursors (step 2 of the process below). Still, all comments are welcome. Thanks again... simplified example (my source files and denormalized tables are 20 - 30 colums wide) Excel or flat file (_FF_): =================== name, address, city, state, cellphone, homephone Denormalized table (_DNT_): =================== name, address, city, state, cellphone, homephone normalized tables (_NT_): ================== tblPerson [PK_person, name, age, height, weight] tblAddress [PK_address, FK_person, street, city, state, zip, addressType] tblContact [PK_contact, FK_person, data, contactType] Process: ========= (1) _FF_myExcelFile.xls --> DTS --> _DNT_myWideTable (2) _DNT_myWideTable --> storedProcs --> _NT_tblPerson, _NT_tblAddress, etc... Step 2 details: ================ DECLARE @vars.... (one for each column in my normalized table structure, matching size and type) DECLARE myCursor CURSOR FAST_FORWARD FOR SELECT name, address, city, state, cellphone, homephone FROM _DNT_myWideTable INTO WHILE @@Fetch_Status = 0 BEGIN -- grab the next row from the wide table FETCH NEXT FROM myCursor INTO @name, @address, @city, @state, @cellphone, @homephone INSERT INTO tblPerson (name) VALUES (@name) SET @personID = @@IDENTITY END
View Replies !
Split Wide, Denormalized Table Into Normalized Structure
Thanks for reading. This is pretty long, hopefully it isn't rambling. I'm building a system that imports data from several source, Excel files, text files, Access databases, etc. using DTS. The entire process revolved around MS SQL Server, by the way. I figured I would create denormalized tables that mirror the Excel and flat files, for example, in structure, import data to those, clean up and remove duplicates there, then break those out into my normalized table structure later. Now I've finished the importing part (though this is going to happen once a week) and I'm onto breaking up the denormalized tables. I'm hesitating because I'm not sure I've made the best decisions in terms of process, etc. I've decided to use cursors to loop over the denormalized tables and use batch insert statements to push data out to the appropriate tables. Any comments? Suggestions? All is welcome. I'm specifically interested in hearing back on the way I've set up the intermediate, denormalized tables and how I'm breaking them up using cursors (step 2 of the process below). Still, all comments are welcome. As are suggestions for further reading. Thanks again... simplified example (my denormalized tables are 20 - 30 colums wide) denormalized table: =================== name, address, city, state, cellphone, homephone normalized tables: ================== tblPerson [PK_person, name, age, height, weight] tblAddress [PK_address, FK_person, street, city, state, zip, addressType] tblContact [PK_contact, FK_person, data, contactType] I'm breaking up the denormalized tables like this (*UNTESTED*): ================================================= DECLARE @vars.... (one for each column in my normalized table structure, matching size and type) DECLARE myCursor CURSOR FAST_FORWARD FOR SELECT name, address, city, state, cellphone, homephone FROM _DNT_myWideTable INTO WHILE @@Fetch_Status = 0 BEGIN -- grab the next row from the wide table FETCH NEXT FROM myCursor INTO @name, @address, @city, @state, @cellphone, @homephone -- create the person first and get the ID with @@IDENTITY INSERT INTO tblPerson (name) VALUES (@name) SET @personID = @@IDENTITY -- use that ID to coordinate inserts across other tables INSERT INTO tblAddress (FK_person, address, city, state, addressType) VALUES(@person, @address, @city, @state, 'HOME') INSERT INTO tblContact (FK_person, data, contactType) VALUES(@person, @cellphone, 'CELLPHONE') INSERT INTO tblContact (FK_person, data, contactType) VALUES(@person, @homephone, 'HOMEPHONE') END
View Replies !
How To Split A Transaction Table To Create Training And Testing Set For AR
Hi all, I have a transaction table that has a composite key made up of transaction id and product id. where multiple products were purchased under same transaction, transaction ids got repeated. I would like to split the table randomly into 70%, 30% ratio to create training and testing set respectively in such a way that it does not split a same transaction under which multiple products were purchased (rows with same transaction id should not get split). is it possible? if possible what is the idea? It would be of great help. Thanks. Fakhrul
View Replies !
Deadlock Problem? 3 Way Conditional Split Of Data From One Table To Another Never Completes
I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete. Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.
View Replies !
Delete Rows From One Table Using Rows From Another Table
Using the the NumId from TitleData, I would like to delete thecorresponding row in Bookdata using pure SQL. I want it to delete allrows in bookdata where the Titledata.NumID is a match to bookdata.idThe two tables are linked in that the NumId of table Titledata isidentical to the Id of table bookdata. I can, using ADO, loop thrudeleting one by one but I would like to do this in a pure SQLstatement. Is this possible? Any help is appreciated.I was thinking something like this way :"Delete from Bookdata where Titledata.NumID = Bookdata.id"But of course it will error.My current code is:(frmlogon.tablename is really Titledata)Dim rstry As New ADODB.RecordsetDim values As VariantSQLQuery = "Select Numid from " & frmLogon.TablenameSet rstry = frmLogon.cnConnection.Execute(SQLQuery)values = rstry.GetRowsSet rstry = Nothing'now loop thruDim xx As Integerxx = 0Do Until xx > UBound(values, 2)SQLQuery = "Delete from Bookdata where bookdata.Id = '" & values(0,xx) & "'"frmLogon.cnConnection.Execute (SQLQuery)xx = xx + 1Loop'create statements for 2 tables involved areconn.Execute "CREATE TABLE TitleData" & _"(Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY," & _"NumId INT DEFAULT 0 )"conn.Execute "CREATE TABLE BookData" & _"(Id INT IDENTITY (1, 1) NOT NULL," & _"Titles TEXT DEFAULT ''," & _"GeneralNote TEXT DEFAULT ''," & _"Author VARCHAR(100) DEFAULT ''," & _"Imprint VARCHAR(100) DEFAULT ''," & _"ISBN VARCHAR(100) DEFAULT ''," & _"Description VARCHAR(100) DEFAULT ''," & _"CallNumberPre VARCHAR(5) DEFAULT ''," & _"CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"& _"Accession VARCHAR(25) DEFAULT ''," & _"Bibliography VARCHAR(100) DEFAULT ''," & _"Series VARCHAR(100) DEFAULT ''," & _"MyStatus VARCHAR(70) DEFAULT ''," & _"Barcode VARCHAR(50) DEFAULT ''," & _"LocalData VARCHAR(100) DEFAULT ''," & _"CheckoutPeriod VARCHAR(10) DEFAULT ''," & _"CatalogCard TEXT DEFAULT ''," & _"Summary TEXT DEFAULT ''," & _"MyCount VARCHAR(10) DEFAULT ''," & _"ItemDate DATETIME DEFAULT ''," & _"MyUser VARCHAR(50) DEFAULT ''," & _"MarcData TEXT DEFAULT ''," & _"SdlsRecord TEXT DEFAULT '', LOSC VARCHAR(5) DEFAULT '', LOSNDecimal(14,6) DEFAULT 0," & _"Edits Char(1) DEFAULT '', TitleDuplicate VARCHAR(50) DEFAULT '')"
View Replies !
Fetch Odd Rows And Even Rows From A Table
Dear Friends, Is there any way to display a table data separately like odd rows and even rows?I dont know this is possible or not?If it is possible means how can i achieve it?Please guide me a proper way. Thanks all! kiruthika http://www.ictned.eu
View Replies !
Fact Table Load - Will Receive 31968 Rows Vs. Will Receive 52428 Rows
I am getting different performance results when importing two different fact tables, and I'm wondering what I can do to tweak the other table so that it is faster. Both are using MSDAORA.1 OLE-DB source and SQL Server Destination. Both have 100,000 defaultbuffermaxrows and 10 enginethreads. Both are set to readcommitted. Can someone tell me what could be causing these two SSIS packages to have different results when reading from Oracle and writing to SQL Server?
View Replies !
Deleting Rows From Many Tables
What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:SoderquistString %>" DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID" SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @State)"> <DeleteParameters> <asp:Parameter Name="UserID" Type="Int32" /> </DeleteParameters> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment: DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID DELETE FROM [photo] WHERE [UserID] = @UserID; DELETE FROM [album] WHERE [UserID] = @UserID; DELETE FROM [comment] WHERE [UserID] = @UserID; ...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!
View Replies !
|
TRACKER
