MS SQL Server :: Big Table(?) Or Split Between Tables?

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 !

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 !

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 !

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 !

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 !

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 !

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 !

Measure Page Split Online For A Particular Table?
Is there a way to measure page split for a table during data insertion/update (not base on dbcc showcontig result)?

thanks...byyu

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 !

Load The Conditional Split Conditions From A Table
Hi everybody,

I want to load the conditions for a conditional split from a table, so that the bussiness logic can be changed with a simple user interface .

How can I do this?

Best regards

John

View Replies !

How Do I Split And Insert A Comma Delimited String To A Table?
I am passing in a string of dates, delimited by a comma.

So

01/01/04, 02/01/04, 03/01/04

etc

I would like to enter each of these values into a table via an INSERT stored procedure.

Does anyone have any code for this?

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 !

Transfer SQL Server Objects Task (for A Table): Can It Be Split Into Smaller Batches
We are using the Transfer SQL Server Objects Task to transfer a large table. The trans log is filling up for this table. Is there a method to split the Data Transfer Task into smaller batches? (Smaller tables are transferring without issue.)

Thanks.

View Replies !

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query
Hello,

I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.

I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.

This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.

So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?

Thanks in advance,
Andy

Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View Replies !

Many Tables Vs One Big Table
Dear experts,If I have a number of related groups of data (e.g., stock prices fordifferent stocks) is it better to put them into many tables (e.g., onetable per stock) or into one big table (e.g., a single table where onecolumn is the stock id other columns are the price and other data)?The single table approach with a clustered primary key including thestock id and other information that is different for each stock seemscleaner to me. But I worry that having a single table could hurtpreformance when the database gets very large. The clustered primarykey should make queries on the single table fast, but I'm worried aboutinserts. According to my vague understanding of a clustered primarykey, records are physically stored on disk based on the primary key. Soif I insert a record for a given stock does the database have tophysicall "move down" all the records that are below that or does it dosomething smarter?To summarize:1. Should I use a single table or many tables?2. If using a single table, how are inserts handled by the DB toprevent inserts from getting slower and slower as more items are addedto the DB?Thanks in advance for your help and advice.

View Replies !

Using PM Of Two Tables As A PM Of Third Table
I want to use the Primary keys of two tables say Tb1 and Tb2 in a table Tb3, such that the combination of the Primary Keys of Tb1 and Tb2 will be the primary key of the table Tb3. Please guide me here.

View Replies !

Creating A Third Table From Two Tables
Hi All,I will like to create a table from two tables this (explained below) way using a stored procedure. Basically i want the stored procedure to return me the third table but i dont know how to do it.Table ATopicID Topic ----------------------------- 1 Sometopic 12 Some topic 23 Some topic 34 Some topic 4 Table BRateID Rate TopicID---------------------------------------1 5 12 5 13 5 24 4 25 4 36 3 5 Table CTopicID Topic Rate--------------------------------------------1 Some topic1 52 Some topic 2 5 3 Some topic 3 5Basically i have a table that stores topics associated with a particular subject. Those topics are stored in table A above. My users can just read the messages associated with the topic and rate that message. The rating is then stored in table B.Now i will like my users to search for topics with a particular word or sentence in table A and the topics that are returned have a rating they specify. My procedure takes the word or sentence to search in table A and and the rating which will be used in table B. I will like to construct another table that has this newly searched results and return it using a stored procedure or anything simple. I dont know how to do this because i have very little knowledge in stored procedures or sql..... Any help(Code) will be greatly appreciated...

View Replies !

Table Insert For Three Tables??
I have 3 tables, Type, Subtype, and Subtype2. All which are related by the ID of preceding table. Using a 3 textboxes and a button I want to insert new Types and subtypes to the tables. How do I go about identifying the new types new ID insert it in to the other tables? I am using ASP.net (vb) with sql server.

View Replies !

Getting Tables That Link To A Table
Hi,How can I run a query to figure out what tables and correspondingcolumns link to any column in my table T?Thanks, - Dave

View Replies !

Copy Two Or More Tables To Another Table?
I have 3 tables(Table1, Table2, Table3) and i want this 3 tables to be inserted/copied to NewTable.
this is my query and it is good only for one table:
INSERT INTO NewTable
SELECT *
FROM Table1;

Can you help me to solve this problem?

Thanks....

View Replies !

Create Table From Two Other Tables??

Im trying to bring together two tables into one, but they're records dont always match, so im getting akward returned records. So now i want to combine the fields i need from these two tables to create a new one. How would that be coded.

I want to

SELECT

[Customer_Bill_Customer_No],

[Customer_Bill_Customer_No],

[Sales_Header_Order_DateTime],

[Sales_Header_Your_Reference]

from

[NavisionReplication].[dbo].[Tbl_Sales_Header]

and
SELECT
[Sales_Invoice_Header_No]

FROM [NavisionReplication].[dbo].[Tbl_Sales_Invoice_Header]

now , how would i do that?

View Replies !

Insert From One Table Into 2 Tables
Hi, i need to create a stored procedure that will take data from one table and insert it into one tabe.

I have 3 tables
tblTest is the table that i need to take the informations from.

intD1 to intD4 is a score

I don't know if iexplain my sefl correctly tks in advance!
tblTest
1 , 2 , 'A', 'B', 0 , 1 ,2 , 4

i need this

tblResultat
1, 2, 'A','B'

tblResultatXReponse
1,1,0,0
2,1,1,1
3,1,2,2
4,1,4,3

tblTest
intTestId autonumber
intCoursId
strCodeAbsent1
strCodeAbsent2
intD1
intD2
intD3
intD4

tblResultat
intResultatsId autonumber
intCoursId
strCodeAbsent1
strCodeAbsent2

tblResultatXReponse
intResultatXReponseId autonumber
intResultatsId
intReponseDesc
intOrdre

View Replies !

When Have 4 Tables How To Compare Table With 4nd Table? ( Need To Join And Compare With Datatime)
Table MediaImportLog
column â†˜ImportIndex     ImportFileTime            ImportSource
value    â†˜80507             20060506001100          815
              80511             20061109120011           CRD                       ã€? P.S the values type of ImportFileTime 20060506001100 â†’ 2006 -year 05-month 06-day 00-HH 11-minute 00-secondã€‘
Table BillerChain
columnâ†˜BillerInfoCode       ChainCode
value   â†˜750                    815
value   â†˜81162                  CRD
Table   Biller
columnâ†˜CompanyCode         BillerCode
value   â†˜999                     750
value   â†˜81162                  516
TAble DataBackup
columnâ†˜CompanyCode         Keepmonth
value   â†˜999                     6
value   â†˜81162                 12

---------------------------------------------------

when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ?

thank you very much

View Replies !

4 Seperate Tables Or One Large Table?
I have 4 tables with the respective amount of records
1) 6755
2) 2021
3) 2021
4) 355

They all have the same columns. However, they need to be seperate, or at least when I query them. I'll be accessing this database via the web. i was first afraid that a large database would cause major slow down when accessing the db. So I broke it up into 4 tables. If I combined all 4 tables into one large table and just had a column that differentiated the 4, how significant would be the change in speed when accessing the table? It's not a big deal to keep them seperate, its just that when I have to add or remove a column from one table I have to remove it from all the tables. Furthermore, I'm using a module from DEVEXPRESS, don't know if anyone has heard of it, but when you use a gridview, it loads up the entire table even though your paging (which I think is retarded), so for that reason I was afraid it would slow up my access to the db. Any thoughts?

View Replies !

INSERT Among 3 Tables And 1 Is A Junction Table
Looking for some help here, so thanks for any input. I'm a painfully new newbie to SQL scripting.Situation:
I have a simple database to handle an organization's events. Those
events are categorized and may have more than one category assigned to
each event. I need a maintenance Web Form to update their events.Set
Up (so far): I have a CATEGORIES table. It has an auto incrementing UID
and a Category name field. This table will be updated so infrequently,
I plan to update it manually (no need for a maintenance Web Form). Next
is the EVENTS table. It also has an auto incrementing UID along with
several fields (Title, Location, DateTime, etc.). The junction table is
named jEVENTSCATEGORIES. It has its own auto incrementing UID along
with 2 fields named for the primary keys (UIDs) in the other 2 tables
(EventsID and CategoryID).Goal: On the Web Form, I have a
CheckBoxList control that's populated by the CATEGORIES table. One or
more categories can be checked for each event. I have a FormView
control that allows Edits and Inserts.Need: I need to know the
INSERT statement(s) required to insert a new record in the EVENTS table
and then to update one or more rows in the junction table
(jEVENTSCATEGORIES).My Assumptions: I know how to create
SELECTs and INSERTs and whatnot, but I'm not certain how to create a
second INSERT statement that is based on a variable (or output) from a
previous action. So any help would be MUCH appreciated. Thanks for your
time!

View Replies !

Update Table With Value Of Another Tables Column?
How do I update a table's column, with the values of another tables column where the primary key between is Part_Number?
table2 = temp_table
Update Actuator_Components Set Scanned_Drawings = table2.Scanned_Drawings

View Replies !

DB Design: One Table Or Multiple Tables?
I am developing a Content management system of sorts and I want it
to be pretty flexible. I have noticed that with web content (News,
Articles, Events, FAQs) that there is a lot of similarity between these
items. They all have the same basic fields:
TitleSynopsisBodyStartDateEndDate
(for News, Articles, FAQs start and end date can be used for content
expiration. For events, they are used for the actual event dates.)
But for some types of content, I will need fields specific to that
type. For instance, I want to have a few custom settings for a "Photo
Gallery" type, like, how many rows/columns of thumbnails to display per
page.

I feel like I have 3 options, but would really appreciate your advice. I created diagrams for the 3 options, located here: http://nontalk.com/dbdesign/

View Replies !

Table Variables Vs. Temp Tables
How do I know when to use a table variable, and when to use a temp table in my stored procedures? It seems that in most cases table variables are more efficient (in terms of execution time / CPU usage) but some of my stored procedures perform an order of magnitute better with temp tables instead.
Short of testing the stored proc both ways, how do I know what to do?
declare @Temp table
or
create table #Temp

View Replies !

Get A Table's Row Count From System Tables
Anyone knows how to get a Table's Row Count from system tables?

Thanks.

View Replies !

A Query Where Two Tables Are Linked To The Same Another Table
Hello,I'm not an expert in SQL, if you could help me for that littleproblem:I had tree simple tables with their fields:[Client] IdClient, Param[Sale] IdSale, IdClient, Param[Param] IdParam, ValueHow can I retrieve a recordset with this columns ?IdClient, IdSale, ValueOfParamClient, ValueOfParamSaleThe problem is that I can retrieve a Param for one table (Client orSale) like this request :SELECT Client.IdClient, Sale.IdSale, Param.ValueFROM(ClientINNER JOIN SaleON Sale.IdClient = Client.IdClient)LEFT JOIN ParamON Param.IdParam = Sale.ParamBut how can I retrieve the Param of the another table in a simplequery ? (because I would also like that it works for access)Thank for your help,Marc

View Replies !

Temp Tables Vs Table Variables
I am running SQL Server Best Practices on a SQL 2000database and it is recommending me to change the temptables inside SPs to table variables.I had read already in other places to use table variablesover temp tables. I also know I can't create indexes asI can on temp tables. Instead I'll have to create eithera primary key and/or a unique index on a table variable.One question I have is let's say I will be putting thousandsof records in a temp table, should i still choose a tablevariable over a temp table for this? Or is there arecommended limit where if I have to store certainnumber of records then it's better to store them ina temp table rather than a table variable? Or numberof records is not the factor to decide whether to usetemp tables or table variables?I would like to know when it's ideal or best to usetemp tables instead of table variables and vice versa.Thank you

View Replies !

Updating Tables From A Table Variable
I have a table variable into which I insert the results of a select statement. Based on the records held in the table variable I then want to update a field in one table and insert the records in the table variable into another table.

This works fine in a self contained test:

declare @table table(electionchangeid int)
declare @anothertable table(ID int)

insert into @table(electionchangeid) values(1036276)

update electionchange
set exportdate = GETDATE()
from electionchange ec
join @table t on t.electionchangeid = ec.electionchangeid

insert into @anothertable
select * from @table

But does not work within my sp .... (see next post).

It doesn't generate an error. It just doesn't update or insert any records. I would think that it was a scope issue, except that I can do a select on the table variable and see that it does contain records.

I would be very interested to hear people's thoughts on this.

Regards
Emma

View Replies !