ReUse Common Surrogate Key Pipeline
Jun 12, 2007
I have several stage to star (i.e. moving data from a staging table through the key lookups into a fact table) ETL transformations in a single SSIS package. Each fact table has a different set of measures but the identical foreign key set, e.g. ConsultantKey, SubsidiaryKey, ContestKey, ContestParamKey and MonthKey.
Currently I have to replicate the key lookup (Surrogate Key Pipeline, or SKP) for each data flow. If I could cache each dimension one time in the package and reuse it for each stage to fact it would be much more efficient.
Is there a way for me to reuse a common data flow?
View 6 Replies
ADVERTISEMENT
Apr 6, 2007
The scenario is as follows: I have a source with many rows. Each row has a column called max_qty_value. I need to perform a calculation using another column called qty. This calculation is something similar to dividing qty/(ceiling) max_qty_value. Once I have that number I need to write an additional duplicate row for each value from the prior calculation performed. For example, 15/4 = 4. I need to write 4 rows to the same target table as in line information for a purchase order.
The multicast transform appears to only support fixed and/or predetermined outputs. How do I design this logic in SSIS to write out dynamic number of rows to a target table.
Any ideas would be greatly appreciated.
thanks
John
View 18 Replies
View Related
Apr 30, 2007
Dear Folks,
I'm getting the error with application like this..............
"the transaction log for database "mydatabase" is full.....to find out why space in the log can not be reused, see the log_reuse_wait_desc column in sys databases. "
I've checked with the column, there are totally 3 options.(I've checked with all other databases)
1)checkpoint,
2)log_backup,
3)nothing
what can i choose for this error to be suppressed?
actually there was log_backup option.
please guide me in this regard.........
thanks in advace.
Vinod
View 1 Replies
View Related
Nov 3, 1999
hi!
when i read some reference books about the SQL7.0, i often met 'surrogate key'. what's the surrogate key? what's its funtion? could you give me a good example?
thanks very much!
View 1 Replies
View Related
Sep 19, 2006
Hi gurus
can any one tell me what is the best way to use surrogate key (except uniqueidentifier datatype)? how can I use with TSQL?
View 5 Replies
View Related
Apr 25, 2007
It's a pretty basic question but I haven't been able to find any examples out there. I dimmed a dataadapter and would like to reuse later in my code (line 3 in the code below). What is the correct syntax to do this? Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn)da.Fill(myDataTable)da.______ ("SELECT * FROM myTable2", conn)da.Fill(myDataTable2)
View 2 Replies
View Related
Jul 10, 2004
Hi,
I am getting the following error:
The SqlParameter with ParameterName '@pk' is already contained by another SqlParameterCollection
If I could work out what code to post I would, but I can say that I am managing my Sql data in my code by caching small arrays of SqlParameter objects as pulled from the database. If I need to update the DB I change the cached SqlParameter and re-insert it. If I perform a SELECT I check the cache first to see if I already have the SqlParameter.
However, currently, I am experiencing the above error when performing a select, then later an update, followed by another update.
Would I be correct in saying that a SqlParameter object can only be used once for a database operation and should be discarded? Would I be correct if I said that the SqlCommand object should be discarded? I am barking up the wrong tree entirely?
Distressed in DBLand,
Matt.
View 2 Replies
View Related
May 1, 2006
I can create a SqlCacheDependency, and link it to a cached item in httpcontext cache. When something change, it will remove the cached item from the cache. I think I have to redo the process when that happens - prepare sql command, create SqlCacheDependency and insert the item into cache. Now I only need a notification from my SQL when something changes in one of my table, I don;t need read anything from db, and I think I should find a way to not recreate the SqlCacheDependency object everytime?
any suggestion?
View 2 Replies
View Related
Aug 21, 2004
The orininal design of my db (part of it...) is the following
A JOB has a Number and a Description.
Each JOB can have one or two TASKS (min one, max two). Each TASK is identified by the JOB it belongs to and an Index (unique only for the same JOB).
Each TASK has one an only one set of INFO1, one and only one set of INFO2, one and only one set of INFO3 etc.
A: JOB (JobNum [PK], JobDescription, ...)
B: TASK (JobNum [PK] [FKa], Index [PK], TaskDescription, ...)
C: INFO1 (JobNum [PK] [FKb], Index [PK] [FKb], ...)
D: INFO2 (JobNum [PK] [FKb], Index [PK] [FKb], ...)
(There is a reason to keep INFO1, 2 and 3 separate, because eachof them will be linked to different table. This might influence the answer to my real question.)
First of all, I wouldn't add any surrogate key for TASK, not to loose the logic behind; plus I'd put an ined on JonMum only, being Index equal to 1 or 2 only, so not selective.
The real question is about INFO1 (and 2, 3 etc.) table: should I leave JobNum and Index as PK (consider that the PK of INFo1 will be used as FK for another table), or should I use a surrogate key, like for eaxmple
C: INFO1 (Info1ID [PK], JobNum [FKb], Index [FKb], ...)
I don't really like this solution. Actually I'd prefer the following
C: INFO1 (Info1ID [PK], ...)
where Info1ID = JobNum + Index (+ = string concatenation).
Any suggestion?
Thanks
View 3 Replies
View Related
Dec 5, 2007
Hi,
How to create surrogate key in a dimension table?
What transformations can be used to create it?
View 6 Replies
View Related
Jan 16, 2006
Hi, I'm trying to use the SK script from Donald Farmers book but the code isn't accepted
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim CurrentKey As Integer
Public Overrides Sub PreExecute()
CurrentKey = CInt(Me.Variables.FILCodesSK)
End Sub
Public Overrides Sub Input_ProcessInputRow(ByVal Row As Input0Buffer)
CurrentKey += 1
Row.SurrogateKey = CurrentKey
End Sub
End Class
There is a problem with the use of the overrides on the Input_ProcessInputRow sub should this be renamed?
Cheers, Al
View 1 Replies
View Related
Dec 15, 2007
Hello, Can I reuse the deleted primary key id? I'm using SQLServer2005. cheers,imperialx
View 6 Replies
View Related
Nov 14, 2006
What is the best code pratice to use do the following code,
SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
sum(In1+In2)/10 as inDec,
from fo group by fo.no,fo.name order by fo.name
instead of
SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
((sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END))+sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END)))/10 as inDec,
from fo group by fo.no,fo.name order by fo.name
I cant use functions and procedures. Is there any better and cleaner way to code this, reusing the calculated values?
View 1 Replies
View Related
Jul 23, 2005
I wonder if there is a solution for this in SQL 2000 (or do I have towait for SQL 2005)?I am currently in the middle of developing a 'Yahoo' style portal whichwill be rolled out in about 20 or so countries. I have set up in SQLServer one database per country. All the portals have the samefunctionality - but show different data.Is it possible to have a single database which holds storedproceedures, functions and views and have the individual countrydatabases use these?Note: I want to avoid using EXEC sp_executesql.I look forward to some good news on this! Thank you in advance.Dadou.
View 2 Replies
View Related
Apr 19, 2008
Hi. I'm starting to try out LINQ to SQL, and so I'm using SQL Express (on Vista) to experiment. My problem is that once I create and delete a database, I can never use the name again. If, after deleting the old database, I try to create another one with the same name (say Acct1), I get "Create failed for Database 'Acct1'. An exception occurred while executing a Transact-SQL statement or batch. The logical file name "Acct1" is already in use. Choose a different name. Error: 1828"
I am trying to create the database in SQL Server Management Studio Express. That database name does not appear in the list of databases: there is only AdventureWorks and the system databases. The .MDF and .LDF files have been deleted. Not just sent to the recycle bin, but permanently deleted.
I have already used up Acct1, Acct2, and Acct3, just to try out different scenarios. Each time, I delete the old database before trying to create a new one with the same name, but I am forced to always supply a new, different name. I have checked the directory with hidden files and system files showing to be sure there is no old file lurking there somewhere.
Is there a way to delete these old "logical file names"? I can't even find any reference to their existence except for the message that says they are already in use.
View 11 Replies
View Related
Jul 29, 2015
with c1 As (
select 1 As '1' , 2 As '2' , 3 As '3'
)
, c2 As (
select 4 As '4', 5 As '5', 6 As '6'
union all
select 1 , 2 ,3 from c1 -- >>>>> select from c1 here
) select * from c2
union all
select * from c1 -- >>>>>> and select from c1 here
According to the query above , I try to reuse the subquery by put the subquery into 'with cte' name (c1) then i select this 2 times .
if I do this way , how many time this subquery (c1) execute ?
if 1 time then this is the right way to reuse this subquery .
if 2 times , it is not then what should i do to reuse this subquery ?
View 3 Replies
View Related
Nov 26, 2007
I have read the articles posted online concerning different dialog reuse strategies. Most of them create a new table in the sender to hold dialog ids. I was wondering what is wrong, if anything, with the following approach:
Code Block
declare @dlg uniqueidentifier
select top 1 @dlg = conversation_handle from sys.conversation_endpoints where state IN ('CO')
if @dlg is null
begin
begin dialog conversation @dlg
from service [tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender]
to service '//TyMetrix360Audit/DataWriter','386DDD04-7E55-466A-BE83-37EFC20910B9'
on contract [//TyMetrix360Audit/Contract] with encryption = off;
end
;send on conversation @dlg
message type [//TyMetrix360Audit/Message] (@msg)
Here I simply select a conversation handle directly from the sys.conversation_endpoints table. Can anyone see any issues with this approach?
Thanks in advance....
View 6 Replies
View Related
Jan 3, 2007
I have been working with SQL for quiet a while but think this perhaps is a very basic question that has always escaped me:
At my work I was exposed to both, MS SQL Server 2000 and Sybase Adaptive Server Anywhere/Sybase SQL Anywhere.
Under Sybase I was able to use aliases in other calculations and filters but i have never been able to do the same with SQL.
Example:
In Sybase I can write this:
Select Price * Units as Cost Cost * SalesTax as TotalTaxFrom Invoice Where TotalTax > 3.5
However if i want to do this in MS SQL 2000 i have to go trough
Select
Price * Units as Cost
Price * Units * SalesTax as TotalTax
From Invoice
Where (Price * Units * SalesTax) > 3.5
In the long run this is costing me a lot of code redundancy, not to mention a debugging nightmare. Is there a way to replicate this alias usage in MS SQL Server?
View 3 Replies
View Related
Jun 5, 2007
I am working on a project developing a fairly large number of reports with a team of developers. Many of these reports have common elements and code, such as common headers with user-selectable colors. Additionally, many of the common parts of the reports are at mockup stage currently, and many features will have to be added to the reports as time goes on.
We're attempting to create a generalized framework that will minimize the duplication of effort as we develop these reports, and as we go back and modify or fix them later.
What is the best way to approach this?
My first attempt was to create a report template and base all the reports off of the same template. That was fine for the first pass, but as we need to make changes later, they will not be propagated to the already existing reports.
My second attempt was to have each componant of that template reference a subreport, so that changes to the actual report template will be minimized as we go forward. This works great for minimizeing work, but it appears that you lose many features with the use of subreports, and there seems to be a pretty serious performance impact as well. I have posted about one such issue here: Pagination
If anyone has pointers about how to go about this, and where I should start, they would be greatly apreciated!
View 2 Replies
View Related
Apr 20, 2006
Hi,
I want to incorporate this code but I dont know how to import Microsoft.SqlServer.Dts.Pipeline in an Integration Services Project template. I was thinking of putting this code in the script task but still, I cant import Pipeline. Add reference list does not have it as well. Please let me know how to incorporate this code. Thanks!
Code:
if (ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager != null)
{
cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager);
if (cm.CreationName == "FILE")
{
fileUsage = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);
if (fileUsage == Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)
{
connectionString = ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager.AcquireConnection(transaction).ToString();
if (connectionString == null || connectionString.Length == 0)
{
throw new Exception("No file name specfiy");
}
}
else throw new Exception("Incorrect file connection usage type, should be set to exiting file type");
}
else throw new Exception("Connection is not a file connection");
}
else throw new Exception("Connection is not as assign");
View 1 Replies
View Related
Jun 10, 2008
Hey All, I'm trying to decide what's the 'best' to use. I've been designing and creating database for a while and have pretty much always used a surrogate key and not a normal one. I've finally had some free time to start studying more so in my spare time and read up and come accross a lot of guides, articles and stories that tout that normal keys should be used whenever possible as they're a better identifier and that surrogate keys should only be used when there is not a readily available normal key. Now perhaps I'd be open to accepting that but absolutely every database I come across tends to only use surrogate keys. For example I'm doing an authentication system from scratch and am looking at the User table. Now of course the user name has to be unique, should that be the primary key or should I have a seperate column with a guid or an incrementing int or the like as the primary key? I can certainly see that username could be used. I can also see how it may be easier when looking through the data tables to identify who/what a table is refering to with a surrogate key. However it still seems sort of sloppy, for lack of a better word, to me. Where now I could have somebody's username (or any other piece of data used for this purpose) spread accross a lot of other tables. And while writting this I just thought of the scenario that perhaps somebody needs their username changed, with this method now the ids need to be changed on all the related rows of all the other tables whereas with a surrogate key it wouldn't matter. Anyways I'm mostly looking for opinions on which way to go (not just with the user sample, but more in general).Thanks.
View 2 Replies
View Related
Jan 22, 2001
Hello
I'm looking for a way of generating the next key value that works in MS and Sybase SQL Servers. Sybase identity columns are a bit dodgy, so...
If I have a separate table NextKey (NextKey int) with one row that I update as follows...
declare @NextKey int
update NextKey set NextKey = NextKey + 1, @NextKey = NextKey + 1
insert into myTable (PrimaryKeyCol, ....) values (@NextKey, ....)
are there any problems with concurrency ? As I see it the update will lock the row so different connections will always come up with a different @NextKey value....
Thanks
John
View 2 Replies
View Related
Aug 23, 2004
My previous post was not really clear, so I'll try again with a (hopefully) better (even if longer) example...
Consider the following...
A JOB describes the processment of a document.
Each document can exist in two versions: English and French.
A JOB can have 1 or 2 TASK, each describing the processement of either the English or French version.
So we have the following:
A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)
that is there is an identifying 1:M (where maxium allowed for M is 2) relationship between JOB and TASK; TASK being identified by JobNum and Version (where the domain for Version is {E, F}).
Each TASK may require a TRANSLATION sub_task.
Each TASK may require a TYPING sub_task.
Each TASK may require a DISTRIBUTION sub_task.
For example, for a given doc, the English TASK requires TRANSLATION and DISTRIBUTION, while the French only DISTRIBUTION.
That is, there is a 1:1 not-required relationship between TASK and TRANSLATION, TYPING and DISTRIBUTION.
So we have the following:
A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)
C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)
As you can see I am using the PK of TASK as FK and PK for each of the three SUB_TASKs.
To complicate things, each SUB_TASK has one or more assignments. The assignments for each SUB_TASK records different information from the others.
So we have...
A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)
C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)
F: TRA_ASSIGN (JobNum [PK] [FKc], Version [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (JobNum [PK] [FKd], Version [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (JobNum [PK] [FKe], Version [PK] [FKe], Index [PK], Pages, ...)
that is there is an identifying 1:M relationship between each SUB_TASK and its ASSIGNMENTs, each ASSIGNMENT being identified by the SUB_TASK it belongs to and an Index.
I wish I could send a pic of the ER diagram...
Maybe there is another and better way to model this: if so, any suggestion?
Given this model, should I use for TRANSLATION, TYPING and DISTRIBUTION a surrogate key, instead of using the composite key, like for example:
C: TRANSLATION (TranslationID [PK], JobNum [FKb], Version [FKb], DueDate, ...)
D: TYPING (TypingID [PK], JobNum [FKb], Version [FKb], DueDate, ...)
E: DISTRIBUTION (DistributionID [PK], JobNum [FKb], Version [FKb], Copies, ...)
this will "improve" the ASSIGNMENTs tables:
F: TRA_ASSIGN (TranslationID [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (TypingID [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (DistributionID [PK] [FKe], Index [PK], Pages, ...)
I could even go further using a surrogate key even for TASK, which leads me to the following:
A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (TaskID [PK], JobNum [FKa], Version , Priority, ...)
C: TRANSLATION (TaskID [PK] [FKb], DueDate, ...)
D: TYPING (TaskID [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (TaskID [PK] [FKb], Copies, ...)
F: TRA_ASSIGN (TaskID [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (TaskID [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (TaskID [PK] [FKe], Index [PK], Pages, ...)
I don't really like this second solution, but I'm still not sure about the first solution, the one with the surrogate key only in the SUB_TASks tables.
View 2 Replies
View Related
May 19, 2008
I am performing a Select Into from a #table into a real table that has a surrogate key. If this is in a transaction (or not in one) am I guaranteed that the records inserted will be sequential surrogate key ids?
Select * into REALTABLE from MYPOUNDTABLE --40 rows
Can I assume that if the first one inserted is id 32 that the last one is 72?
View 5 Replies
View Related
Jul 20, 2005
{CREATE TABLEs and INSERTs follow...}Gents,I have a main table that is in ONE-MANY with many other tables. For example, ifthe main table is named A, there are these realtionships:A-->BA-->CA-->DA-->EWith one field in Common (Person). The tables B, C, D and E are History tables,with Start and End dates. Each person has a Program history (table B, ie), anExperience history (table C, ie), and so on...many differernt types ofhistories, and it may grow from here....table F, G, etc.The included CREATE TABLEs and INSERTs contain tables A, B and C.The problem: Each tblCase (table A) record has a date. When joining all of thehistory tables to tblCase on Person, obviously you get a cross-product of eachhistory unless you specify a WHERE clause that extracts one single record fromeach of the histories (duh...that's the point...to extract a single record fromeach history, because there can only be one value in effect at the time of theCase.)QUESTION: From a performance standpoint, would it behoove me to maintain thesurrogate ***HistoryID from each history table in tblCase, or, assuming theindexes are set up properly, would a WHERE condition for each history besufficient? For example, the following select works as expected:SELECT CasePerson, CaseDate, ProCode, ExpYearFROM tblExperienceHistory INNER JOIN (tblCase INNER JOIN tblProgramHistory ONtblCase.CasePerson = tblProgramHistory.ProPerson) ON tblCase.CasePerson =tblExperienceHistory.ExpPersonWHERE CaseDate BETWEEN ProStartDate and ProEndDateAND CaseDate BETWEEN ExpStartDate and ExpEndDateIt extracts the single record from each history for each person for each case.But I'm afraid of performace with such a scenario.Instead, I could store each ***HistoryID in the table tblCase, and then justjoin on that...no WHERE needed. But the trade-off is that I'd have to buildprocesses to maintain that. ("Hey, when you insert a record into tblCase, makesure to go get each HistoryID from the History tables!" or "If the user changesthe date ranges in one of histories, make sure to update tblCase to match thenew historyID!")Maybe a clustered index on each ***History table on Person/StartDate combinedwith the WHERE clause should perform as well as a real JOIN on surrogateintegers.It seems cheesey to have to resort to surrogate IDs...but the performanceincrease might be worth it. Also, if I go that route, whenever I add a newhistory table, I'd have to change the design of tblCase AND any SPs thatreference it. With the WHERE solution, I'd only have to change the SPs.Comments are welcome! (tblCase grows at 250,000 records per year; the historytables will increase about 1000 records per year)DCMFANCREATE TABLE [dbo].[tblCase] ([CaseID] [char] (5) CONSTRAINT [PK_tblCase] PRIMARY KEY CLUSTERED NOT NULL ,[CaseDate] [smalldatetime] NOT NULL ,[CasePerson] [char] (5) NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblExperienceHistory] ([ExperienceHistID] [int] IDENTITY (1, 1) NOT NULL ,[ExpPerson] [char] (5) NOT NULL ,[ExpStartDate] [smalldatetime] NOT NULL ,[ExpEndDate] [smalldatetime] NOT NULL ,[ExpYear] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblProgramHistory] ([ProgramHistID] [int] IDENTITY (1, 1) NOT NULL ,[ProPerson] [char] (5) NOT NULL ,[ProStartDate] [smalldatetime] NOT NULL ,[ProEndDate] [smalldatetime] NOT NULL ,[ProCode] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO [tblCase]([CaseID], [CaseDate], [CasePerson])VALUES('12345', '3/1/03', '00000')INSERT INTO [tblCase]([CaseID], [CaseDate], [CasePerson])VALUES('A1G34', '4/23/03', '00001')INSERT INTO [tblExperienceHistory]([ExpPerson], [ExpStartDate], [ExpEndDate],[ExpYear])VALUES('00000', '1/1/03', '5/19/03', 1)INSERT INTO [tblExperienceHistory]([ExpPerson], [ExpStartDate], [ExpEndDate],[ExpYear])VALUES('00000', '5/20/03', '12/31/03', 2)INSERT INTO [tblExperienceHistory]([ExpPerson], [ExpStartDate], [ExpEndDate],[ExpYear])VALUES('00001', '4/20/03', '11/1/03', 0)INSERT INTO [tblProgramHistory]([ProPerson], [ProStartDate], [ProEndDate],[ProCode])VALUES( '00000', '2/1/03', '9/30/03', '55555')INSERT INTO [tblProgramHistory]([ProPerson], [ProStartDate], [ProEndDate],[ProCode])VALUES( '00000', '10/1/03', '5/1/04', '55555')INSERT INTO [tblProgramHistory]([ProPerson], [ProStartDate], [ProEndDate],[ProCode])VALUES( '00001', '1/1/03', '12/31/03', '55555')
View 4 Replies
View Related
May 19, 2008
I am performing a Select Into from a #table into a real table that has a surrogate key. If this is in a transaction (or not in one) am I guaranteed that the records inserted will be sequential surrogate key ids?
Select * into REALTABLE from MYPOUNDTABLE --40 rows
Can I assume that if the first one inserted is id 32 that the last one is 72?
View 6 Replies
View Related
Jun 19, 2014
I have a table in which there is a column of type identity(1,1),in the same table i have bulk data, and i have deleted a row from my table, so now i want to reuse that same Id again, how to do that.
View 3 Replies
View Related
Mar 4, 2008
Hello,
I have an ID column which is an Identity column, and it counts from 1 upwards.
If I have 10 records, I have ID's number 1 to 10.
If I then add another record, it will give the ID number 11.
Imagine I then delete ID number 9
Is it possible to get the next record I create to take number 9 instead of 12 ?
View 5 Replies
View Related
Feb 7, 2006
Hello
I have some common functions that i use in several script tasks. How du i store a function globaly so that i can use it from different projects and still only have to edit it one place ?
View 4 Replies
View Related
Oct 27, 2006
This is probably obvious, but how do I split a pipeline. I.e. I've got a data source with 200 columns - I need to split this into 20 pipelines each containing 10 of the original columns.
View 7 Replies
View Related
Jan 23, 2008
I have two tables:
countries(country_id integer, country_name string)
authors(auth_id integer, country_id integer, auth_name string)
...Where "country_id" in the authors table refers to the same country_id in the countries table.
I want a stored procedure to handle the insertion of new rows in the authors table. There are two methods of doing it:
1) CREATE PROCEDURE addAuthor( authorName, countryId )
And
2) CREATE PROCEDURE addAuthor( authorName, countryName )
Now, I like #1 because the implementation is simple -- the calling code simply passes an author name, and a country id and an INSERT INTO statement is called with those parameters
INSERT INTO authors( @authorName, @countryId )
I like #1, because it hides the surrogate "id" key from the application calling code. But on the downside, it has more overhead work, because you have to first a) verify a country with that name exists, and b) select that id into a variable.
DECLARE id INT;
IF EXISTS (select * from countries where country_id = @countryId ) THEN
SELECT country_id INTO id FROM countries WHERE country_name = @countryName;
END IF;
(Sorry I may have the SQL syntax wrong up there, but I was just trying to demonstrate the extra overhead involved).
Which approach do you guys think is better?
View 1 Replies
View Related
May 21, 2008
I am in the process of building a fact table in a staging area. The data in the host system has numerous composite keys, so I have replaced all the composite keys in the dimensions with surrogate keys (integer) which are generated using an identity at load time. When I load the staging (fact) table, I have set the default value of all the foreign keys to 0. What I must do now is update all the foreign key values with the surrogate key values from the dimensions. I'm using an update command and the original gid values from the source system in the where clause...i.e.
UPDATE X
SET x.key_1 = y.key_1
FROM TableA X WITH (NOLOCK)
INNER JOIN TableB Y WITH (NOLOCK)
ON x.org_id = y.org_id
AND x.bus_id = y.bus_id
AND x.prov_gid = y.prov_gid
AND x.log_gid = y.loc_gid;
This seems to work fine for most tables. However, I am now trying to update a table that has over 10 million rows and approximately 30 foreign keys. The script runs for hours. I ususally stop it after about 8 hours when it still hasn't completed. Since the keys are dynamic and they could possibly change during each load process, I can't add them during the load process.
Is there a better way to update these keys. I need to regenerate the fact tables every night and taking this much time to reload a fact table is just not practicle. I've indexed the alternate keys on all the dimensions and have also indexed the gids on the target fact table. Am I doing something wrong? Have I over indexed the target table? Please help! Thanks Jerry
View 1 Replies
View Related
Jul 22, 2007
Hi,
I have a dimension called 'Caller Type' with the following attributes:
CallerTypeKey ---- surrogate key
CallerTypeID
CallerTypeDesc
CreatedByKey ---- foreign surrogate key from User Dimension
I used Script Task to get the last used key and increment it so i can use it for new records in my dimension. however, my dimension is linked to a User Dimension and I need the surrogate key of that once I insert the new record to CallerType Dimension.
How would I do that?
cherriesh
View 3 Replies
View Related
