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 Complete Forum Thread with Replies
Related Forum Messages:
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 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 !
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 !
Importing Data From Another Table
Hi All,I'm coming from using MySQL, and in their dialect you could pull data from one table to another using the following: INSERT INTO Table1 (fname, lname)VALUES( SELECT fname, lname FROM Table2 )Let's assume Table1 is a simple table with the fields ID (PK/Identity), fname, and lname. This query would grab all the first and last names out of Table2 (fname and lname fields) and insert them into Table1, generating the ID for each new row.How would I do this in T-SQL?
View Replies !
Need Help With Importing XML Data To A Table
Using some VB sample code on the Internet I have the followingthat works well and exports a set of records to an XML file:Dim oCmd, sSQL, oDomSet oDom = CreateObject("Msxml2.DOMDocument.4.0")Set oCmd = CreateObject("ADODB.Command")oCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=(local);InitialCatalog=TestXML;UID=sa;Password=123456"sSQL = "<ROOTxmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>" & "select *from tblTest for xml auto</sql:query></ROOT>"oCmd.CommandText = sSQLoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"oCmd.Properties("Output Stream") = oDomoCmd.Execute , , 1024oDom.Save "C: emp estdts.xml"This is my first day of using XML in SQL Server and I need help on howto imitate INSERT statements by basically importing data from an xml file.With some changes I managed to write VB code that seems to be readingthe XML file I exported earlier but I can't seem to know what to do to beable to take the data only of the XML and INSERT it into a table.The code I used is the one found in the SQL Online Help, search in INDEXfor "OPENXML" and then choose "USING OPENXML"If you have sample code or can point me to a link that has sample code toachieve what I want, I would appreciate your help.Thank you
View Replies !
Importing Unique Data && MAX Data To Table Using DTS
I am creating a DTS package that is combining several tables, converting one column of data to a new column removing all special characters, then exporting the unique data based on this column and another column, and the max of other duplicates to a new table. Now that I have the data in this table, I want to import any data that is not in my main table. This "CLEANED" table does not have a designated "key" column, but the table I want to import the unique items does have an ID column that is also a primary key column. DTS seems to want me to have a Key column to reference when importing from the CLEANED table to the MAIN table. How would I go about checking the MAIN table against the CLEANED table, having DTS import only the unique items from the CLEANED table that are not present in the MAIN table based on three columns? The rest of the columns I want to just extract the MAX data from the duplicates. Now here is the query I use to extract the unique values from the "CLEANING" table to get the data to the "CLEANED" table, but do not know how to use this to import into the MAIN table using something similar. Code: select partno2, MAX (partno) as partno, alt, MAX (C_alt) as C_alt, Max (cmpycd) as cmpycd, MAX (type) as type, compFN, MAX (pndesc) as pndesc, MAX (equipment) as equipment into tbl_CLEANED from tbl_CLEANING group by partno2, alt, compFN ORDER BY partno, compFN The three main columns I need to check against are: partno2 alt compFN I have named the columns the same in both tables. partno2 is the column that has been copied from partno with all special characters & spaces removed. This is the main column I am using as a reference for unique values, then if no match, I have it check against the alt column, then the comFN column. If there are no matches in any of these columns, then I want to extract the data to the MAIN table. How can I compare these tables and import only unique info to the MAIN table? In addition, how can I also check items that are the same in both tables and update the MAX info for the other columns (not the three I use for reference - these I need to leave alone) and update those if there is more data in the CLEANED table then in the MAIN table?
View Replies !
Importing Csv And XML Data From 1 File Into A Table
Hello - I am looking for a way to import data into a table where 1 of the fields holds an XML file but the rest of the fields hold basic csv data. We want to be able to set up a simple import script in our program so that a user can upload 1 file that contains both the data for the normal fields (example: last name, first name, date, source, etc) and the XML string that is parsed later on in our program. The entire XML string resides in its own field. I am wondering if there is anyway all of the data could be combined in 1 file. Thanks in advance.
View Replies !
Help For Importing CSV Data File To Table
Hi all- I am in need of some help importing a .CSV file into a SQL Server 2005 Enterprise Edition. The problem is I already implemented Bulk Insert task in SSIS but it is not importing any data. My detailed layout is as follows : In SSIS package1 - In Control Flow Bulk Insert Task has been inserted Properties of Bulk Insert Task: Connection adtc009d.ganny Destination Table ganny.dbo.t4 Format Format Specify Row Delimiter {CR} Column Delimiter Comma{,} Source Connection File r.csv Options Options Check Constraints Maxerrors 20 This bulk insert task is connected to Data flow task, if we click edit to data flow task, data flow section will come, here Flat file source & OLE DB Destination is there. Flat file source is connected to OLE DB Destination. Properties of Flat File Connection Manager Flat file connection Manager here by clicking new link flat file properties to this. Preview by clicking preview all data are visible Properties of OLE DB Destination editor Oledb connection manager adtc009d.ganny Data access mode: Table or View - fast load Name of Table or view dbo.t4 After designing all this then if I start debugging I could able to get records are imported to a table. Please suggest me where I am going wrong. Thanks in advance Karna
View Replies !
Importing Data From Web Page Into SQL Server Table
Hi,I have to find a way to do the following : a company provides for my company a web page displaying data separated with ;I need to automatically update a table in SQLServer with this data, ie delete the old data, read the web page containing new data as text, and insert these data into the table.I would actually prefer not to trigger this import process "by hand" ; if there's a solution to schedule it ...Thanks for your helpJohann
View Replies !
Importing Data In Table With Identity Column
Whenever I import (or even append from another table) data to a table that has an Identity column, I get an error: Cannot insert NULL values in Identity column. Isn't the Idenity column supposed to incement automatically without me having to provide a value? Using INSERT provides an Identity value automatically, using import however doesn't. How can I overcome this problem? (I now delete and recreate the Identity column - really BAD practice!) Thank you.
View Replies !
Importing ACCESS Data Into SQL Table With IDENTITY Column
I have a problem! We are trying to convert data from Access to a SQL Server database using VB (we are upsizing). Our problem is we use IDENTITY columns that were Autonumbers in Access, to preserve data integrity we need to import these values into the SQL IDENTITY columns, we can`t. I`ve read that we can use SET INSERT_IDENITY ON/OFF table but we can`t get it to work using a pass through query. Any suggestions or a code example to see where we are going wrong? Thanks, Martin Topping
View Replies !
Importing Table And Data From Access To SQL Server 7.0 Using DTS Package
Hi I have some Access DB files(.Mdb files) in the local Directory...I need to Create a DTS Package which takes Source Files as the local MDB files..destination server is SQL Server 7.0 and i need the DTS Package to Create all the tables and import all data from MDB files to SQL server 7.0. Once the Import of table and Data is done from Access to SQL Server 7.0 using Package i need to have this package run everytime during the Installation process so i am Planning to have this Package run from VB Script. Please Advice me in doing this.... Thanks in Advance Kamalesh D
View Replies !
Importing Textfile Data To Existing Table (append?)
I have an existing table I need to add data to. The data is in a text file, and the existing table already has data in it (I don't want to delete this I want to add to it). I used Microsoft's import utility but this created a seperate table with generic fieldnames (column01, column02, ect). Is there a step in this wizard I missed?
View Replies !
Issue In Importing Data From Excel Into Database Table
Hi I am facing issue while loading data from excel into Sql database table. The format of spreadsheet is like Date - Name - A B C Section Marks X1 10 10 10 X2 20 30 10 X3 I need to load this data into table with columns Date Name Section Marks Please share your thoughts on how to load this kind of data into table. Thanks Anshu
View Replies !
Advice On Importing Access Data Into MSSQL Table Using Code
Hi, I'm about to embark on writing some code in perl or VBscript that automatically synchronises a constantly updated Access database with an MSSQL database. I know MSSQL has an import tool built into Enterprise manager but I'm wondering if theres a stored procedure that does this? The way I'm thinking of doing it is to read the all the access tables into separate hash arrays and then INSERTing them into the MSSQL database after checking for any duplicates. This all sounds a bit time consuming (there are a large number of tables) and processor intensive. If anyones done anything like this before, I'd love to hear their views......! 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 !
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 !
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 !
Split Data
My company use SQL server 2005 standard. considering deal with huge data, how if we want to split data (date range yearly or monthly) in order to ease transaction. that's simply for us to use query, but how if we want to split data that can be easily execute by operator (non-admin privilege). Is there any another way?
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 !
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 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 !
Data Flow Split
I'm trying to figure out how in a Data Flow Transform I can split some data. I have data coming through that has a PK (col1) and datetime col (col2). This data may contain multiple rows for the PK, col1. I want to be able to take the min datetime for each row for col1 and send down one path and all other rows down another path eg. There are 20 rows in the Data Flow coming through. Col1 has value 1 for first 10 rows, value 2 for remaining 10 rows and there are different values for Col2 for all rows. Take the Min value in Col2, where col1=1 and also the min value in Col2, where col1=2 and send down one path. Send all other rows down another path. I thought of using the Conditional Split Transform but my Expression knowledge isin't experienced enough (I'm looking into this) and I'm not sure if it can be done. I also though of using the Multi-Cast Transform and then using the Aggregate function, on each data set. While I can do this easily for the first data flow. SELECT col1, MIN(col2) FROM tbl1 GROUP BY col1 which is easily done in the Aggregate transform, the other side of the data flow I can't see how can be done using the Aggregate Transform SELECT col1 FROM tbl1 WHERE col2 NOT IN (SELECT MIN(col2) FROM tbl1 GROUP BY col1). Are either methods feasilble or is there another way. I want to avoid putting this data into temp tables in a SQL database and manipulating the data from there. The data has been extracted from a flat file source. Any help and ideas welcome.
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 !
Split The Data Into Columns
I have a table called products with the values like ProductId ProductName 10 A 20 D,E,F,G 30 B,C 40 H,I,J I need to display each productid's with ProductId ProductName 10 A 20 D 20 E 20 F 20 G 30 B 30 C 40 H 40 I 40 J I will be appreciated if you can send me the code. Thanks, Mears
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 !
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 !
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 !
Using Condation Split To Update Data
hi all i using SSIS to update data on tables based on another tables as follow start connect to DS --> using Lookup to located the key fields on the 2 tables --> on error start insert into the second table else if the recorde is already exist start update my problem is Performance --source table rows count almost 60 million --distination table almost 3 million my package execute from 2 days and still working till now updated 122,000 and insert 4 million kindly i need support thanks & regards
View Replies !
How To Split Data From The MS SQL 2000 Database?
Hi All, We are working on a project, (C#.Net 2003, MS SQL 2000) where database is growing as 2 to 3 GB per day (Scanned Documents are storing in Database). There is only one table in the database. Currently database size is grown upto 200 GB. For safety reason we are planning to split the database accoring to one key field in the table say Book_no. Please guide/suggest me how to split the database now on the SQL query like "SELECT * FROM master_records WHERE book_no=1" And later how to merge/combine all these splitted database into one. I thought of using "Data Export" and later "Import with Append" but don't know whethere it will effective or not? Any method or tool available to Split the database table and later combine them to make again a single one? Please help me, its urgent. Thanks in advance.
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 !
Importing Access Table Into SQL Server 2005 Express Table And Adding One Field
Hi all, Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance. Bill
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 !
|
TRACKER
