Hey everyone,
I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model.
The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the Table Property.
I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time.
Jeremy
hello, I have several tables that have guids as their primary keys and the tables are related as follows: Table1 - primary key = ServiceNo (Guid), Filter Key = CampaignNo Table2 - primary key = CostBasisNo (Guid), Foreign Key = ServiceNo (from Table1) Table3 - primary key = UserId, Foreign Key = ServiceNo (from table1) Table4 - primary key = SourceServiceNo (Foreign Key from Table1), MemberServiceNo(Foreign Key from Table1) what I need to do is copy all records from Table1 where CampaignNo = @CampaignNo and insert them into table1, this I can do easily but I will generate a new ServiceNo for each one and associated a new CampaigNo which is fine. The problem comes in that I need to also copy the contents of Table2 = Table3 for all ServiceNos that have been copied from Table1 but insert the new Guid that will have been created when copying the rows in Table1 This is further compounded when I need to do the same to Table4 but this time I need to insert the newid's for SourceServiceNo and the related MemberServiceNo which all would have changed. I haven't the first clue where to start with this task, do I need to use temporary tables, cursors? any help gratefully received, even if it's a pointer to the most efficient approach. regards
Ok, im a lil confused I know that SSSME (SQL Server Management Studio Express) CTP is a simple gui that you can use to manage your sql server databases and connections... In Visual Basic express, you also have the Database Explorer and the option to add a database and create a connection and a gui to edit ur database/tables/fields etc, but it starts another sqlserver.exe service under the user that is currently logged in, so does it create and start its own server or how is that working?....also when i use one, i cant use the other....so which one should i use? Im going to be using connection strings and creating my own datasets/data adapters so i dont really need the one used inside VS Express....
Still confused bout some things: In SSSME under Registered Servers, there is currently one server that automatically starts, what if i want to add another server? it lets be add it, but it doesnt let me start the serive or connect to it....is express limited to only one server? or is my understanding of it off?
how/where would i add the database to my project? where should the actually database file be located?
sorry for these q's, moving from access to sql express
Hey everyone, I am brand new to SQL and I need some help getting started how to learn about relational databases and how to make the databases via Microsoft SQL(I have visual Studios PRO) and access and use them via C#. I believe that the program that I am writing will be significantly better with a database. What I am doing is making a program that stores user input. But each user input that a person stores can have a reference to another user input, hence why I think that a relational database to store this information will be work out well. I also just want to learn more about databases because I think that it will be a good step in me learning more about programming. What I need from you guys is a point into a direction for a book or a specific spot in a website that will introduce and teach me how to design these relational databases via Microsoft SQL. I have programmed alot with Java and recently switched over to C#, using the .net framework and found it to be great. I need to find out how to make these databases but also how to connect and access them through my application that will be coded in C#. I have "Programming in C#" by O'Reilly but it really doesn't go in that much about SQL databases. The question that I really have come from the limited knowledge that I have gained looking online and talking to my brother (progamming whiz). A database is managed by a DBMS and clients can interact with the DBMS to get to the database. The question to me is how does one interact with a DBMS through a programming language like C#. I guess I am just really confused at the this link. If anyone could point me into the direction of a book to answer these question but help teach me to apply it to my own program, I would really appreciate it. Thank your for time. P.S. I have found some books online but I dont really know which one I really need. One that I found is called "Learning SQL on SQL Server 2005". If you have read, I would appreciate your opinion. Thanks
I’m working with VB6.0 and SQL Server6.5 using ADO accsesing technology. I have a silly problem with an If. I declare a recordset called rstprimas to access to a table called “primas” I make a “select sum” to this table and if the result is null I have to move zero to a variable called liquidacion_primas. I make the select and in the “inmediate window” I can see that the result of the select, that is, rstprimas(0) is null, but in the if instruction the execution go for the else option. That is, it doesn’t make the instruction “liquidacion_primas = 0”. The if must be wrong, but.... why??, do it has someting to do with the “SELECT SUM”??? Can anybody tell me what is the wrong thing?
Thank you very much
MY CODE:
Dim liquidacion_primas as integer Dim Sql_primas as string
Set rstprimas = New ADODB.Recordset
‘sql for accesing the table “primas” Sql_primas = "SELECT SUM(prima) FROM primas WHERE codigo_cuenta = '" _ & rstliquidacion!codigo_cuenta & "' AND fecha_sesion = '" _ & fecha_sesion & "'"
‘select sum rstprimas.Open Sql_primas, cnnoddo, adOpenKeyset, adLockOptimistic
If rstprimas(0) = Null Then liquidacion_primas = 0 Else liquidacion_primas = rstprimas(0) ‘the program make that instruction End If
I am trying to find out what is the reason for this error on my report server.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'Reporting_FSSRC'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local
server machine, or enable remote errors
I checked the log and this is what is says.
<Header> <Product>Microsoft SQL Server Reporting Services Version 9.00.3042.00</Product> <Locale>en-US</Locale> <TimeZone>Central Daylight Time</TimeZone> <Path>C:Program FilesMicrosoft SQL ServerMSSQL.1Reporting ServicesLogFilesReportServer__07_18_2007_16_07_19.log</Path> <SystemName>BRIARD</SystemName> <OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName> <OSVersion>5.2.3790.65536</OSVersion> </Header> w3wp!webserver!6!7/18/2007-16:07:19:: i INFO: Reporting Web Server started w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file. w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file. w3wp!resourceutilities!6!7/18/2007-16:07:19:: i INFO: Reporting Services starting SKU: Enterprise w3wp!resourceutilities!6!7/18/2007-16:07:19:: i INFO: Evaluation copy: 0 days left w3wp!runningjobs!6!7/18/2007-16:07:19:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds w3wp!runningjobs!6!7/18/2007-16:07:19:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds. Cycle: 60 seconds w3wp!runningjobs!6!7/18/2007-16:07:19:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds. Cycle: 60 seconds w3wp!runningjobs!6!7/18/2007-16:07:19:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds. Cycle: 60 seconds w3wp!library!7!07/18/2007-16:07:21:: Call to GetPermissionsAction(/Versatile). w3wp!library!7!07/18/2007-16:07:22:: i INFO: Catalog SQL Server Edition = Enterprise w3wp!library!6!07/18/2007-16:07:22:: Call to GetPropertiesAction(/Versatile, PathBased). w3wp!library!7!07/18/2007-16:07:22:: Call to GetSystemPermissionsAction(). w3wp!library!6!07/18/2007-16:07:22:: Call to ListChildrenAction(/Versatile, False). w3wp!library!7!07/18/2007-16:07:22:: Call to GetSystemPropertiesAction(). w3wp!library!6!07/18/2007-16:07:22:: Call to GetSystemPropertiesAction(). w3wp!library!6!07/18/2007-16:07:29:: Call to GetPermissionsAction(/Versatile/Versatile Customer). w3wp!library!7!07/18/2007-16:07:29:: Call to GetSystemPropertiesAction(). w3wp!library!6!07/18/2007-16:07:29:: Call to GetPropertiesAction(/Versatile/Versatile Customer, PathBased). w3wp!library!7!07/18/2007-16:07:30:: Call to GetSystemPermissionsAction(). w3wp!library!6!07/18/2007-16:07:30:: Call to GetPropertiesAction(/Versatile/Versatile Customer, PathBased). w3wp!library!7!07/18/2007-16:07:30:: Call to ListEventsAction(). w3wp!library!6!07/18/2007-16:07:32:: Call to GetSystemPropertiesAction(). w3wp!processing!7!7/18/2007-16:07:33:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Reporting_FSSRC'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Reporting_FSSRC'. ---> System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper.Open() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc) --- End of inner exception stack trace --- w3wp!processing!7!7/18/2007-16:07:33:: e ERROR: Data source 'Reporting_FSSRC': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Reporting_FSSRC'. ---> System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. w3wp!processing!7!7/18/2007-16:07:33:: e ERROR: An exception has occurred in data source 'Reporting_FSSRC'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Reporting_FSSRC'. ---> System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. w3wp!processing!7!7/18/2007-16:07:33:: i INFO: Merge abort handler called. Aborting data sources ... w3wp!processing!7!7/18/2007-16:07:33:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ; Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Reporting_FSSRC'. ---> System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. --- End of inner exception stack trace ---
Why does it say not associated with trusted sql server connection. I am the admin. How do i fix this, so i can view this report and its datasource correctly on the server.
hi. How to update FormA table from customer table. Let say i wish to keep small number of fields from each table so i use foreign keys as reference. However i had a problem when i tried to save the relationships of both tables, i receive the error that FormA_id is not able to insert null into value. Cust_id(PK) is identify column, as well FormA_id(FK) and FormA_id(PK) too. For example, when i insert a record from customer table, it will automatically create id for FormA. Table structure. Customer cust_id(PK),name,age,formA_id(FK) Table structure, FormA formA_id(PK), info, date, How to solve ?
now, when in insert a row in "TV" the foreign key "IdCamera" will relate to a row in either "InfraredCamera" or in "DaylightCamera" depending on the "DeviceType" value.
in other words, if i insert a row with DeviceType=0, then IdCamera will have to point to a row in the "InfraredCamera" table. And if i insert a row with DeviceType=1, then IdCamera will have to point to a row in the "DaylightCamera" table.
so, my question is, how can i make the constraints relationship so that the idCamera relates to a row in DaylightCamera or in InfraredCamera depending on the value of DeviceType? should i make 2 foreign keys with allow null? or should i place both relationships to the same foreign key? im not sure what to do
Thanks guys for your help. it is really appreciated!
I am trying to query the database to get me the foreign key columns and the tables they belong to.I have: The name of the tableI need:The name of the column in the target tableThe name of the column in the referenced tableThe name of the referenced table Any help would be great, thanks
Hi, I don't know if this is possible, i believe not, so I'm here to ask the experts if is possible to have a foreign key constraint that references the key of one of two tables. Like this: I have 3 tables: TABLE X, TABLE A and TABLE B Is it possible to the FK on TABLE X refernce the PK of TABLE A OR TABLE B? If yes, how can I do this? If not, I need to have a fourth table, so TABLE X references TABLE A and TABLE Y references TABLE B. Thanks!
I have a setup with a bridge table. There are about 5 different tables on one side of the bridge (all with compatable PK columns) one of which is called 'mobilesub', and one on the other side called 'allcostcenters'. The bridge table is called 'subaccountcostcenter'.
I can enter data for mobilesub in the bridge table. But then when I try to enter the info into the bridge table for any of the other tables, such as localsub, there is a conflict like this:
INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK_subaccountcostcenter_mobilesub'. The conflict occurred in database 'test1', table 'mobilesub'. The statement has been terminated.
Is there some rule against using a bridge table that references several different tables, and I'm just not aware of it. Because I've done everything I can to make sure the info from the different tables don't conflict . . . The same error comes up if I do the localsub table first--in that case the foriegn key messing me up is FK_subaccountcostcenter_localsub. So it's not something with the individual tables.
I know altering the schema of system tables is a big no-no, but I was wondering if setting up a table that has foreign keys pointing to a system table is bad.
Basically what I'm refering to is in some cases I have CreationDate and CreatedBy fields in my tables that correspond to GETDATE() and USER_NAME() functions in insert statements....I want the CreatedBy field to be a valid SQL server DB username ... and not some unchecked string value (SYSNAME actually)
I was trying a joining example provided in my book in which customer is a table and person is another table. The query provided in the book is this... USE AdventureWorks2012;
GO SELECT c.CustomerID, c.PersonID, p.BusinessEntityID, p.LastName FROM Sales.Customer AS c INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID;
This is the query that I did....
SELECT Â Â Â Â Â c.CustomerID,p.FirstName,p.MiddleName,p.LastName FROM Â Â Â Â Â Sales.Customer AS c INNER JOIN Person.Person AS p ON Â Â Â Â Â c.CustomerID=p.BusinessEntityID
ORDER BY Â Â Â Â Â Â Â Â p.BusinessEntityID;
Keys :-
Person Table [PK_Person_BusinessEntityID] [FK_Person_BusinessEntity_BusinessEntityID]
However,both of them gives a very different result set.But my question is why do we need to use the Customer.PersonID instead of Customer.CustomerID. Is it really important to use one primary key and one foreign,is there any specific reason why the book showed c.personId=p.BusinessEntityId.??
I have a situation that I must resolve. I have a program being used by many but I had to create a new table to provide a new feature. The problem I have is this table must use the primary key from the parent table as its primary key, meaning when a user adds a new record to parent table, I need to instantly add the primary key to the child table. Now this was done in the program using sql statements, but I need to implement a trigger or such as to keep me from having to reinstall application on many computers.
basically person inserts new record, then I need to get the new primary ket and add insert it into the child tables. how can I do this with a trigger. I have tried to use an insert into statment with my trigger, but I can't seem to pass the parameters correctly.
CREATE Trigger dbo.Table_Borrower_Insert_Keys ON Table_Borrower AFTER INSERT AS begin declare @bid as int
@bid = select MAX(BorrowerID) FROM Table_SoldProgression
INSERT Table_SoldProgression(BorrowerID) values (@bid) end GO
another attempt
CREATE Trigger dbo.Table_Borrower_Insert_Keys ON Table_Borrower AFTER INSERT AS
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table DepartmentDetail table references Department table Department table references Employee table
Hi all, In my project i will have the data in a collection of objects, I need to update series of tables with foreign key relations Right now my code looks like this foreach(object obj in Objects){ int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId ///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table } The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.
I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table DepartmentDetail table references Department table Department table references Employee table
I'm shredding the below xml into relational tables. Each element of the xml has it's own table and there is a foreign key to join the tables, you can see this in the below picture. The process I follow is each relational table I always bring the nesecary xml and store it in the table and when shredding I always look at the parent table.So for example when processing the seat table, I use seat xml from the parent route table, also taking the ROUTEID from the route table. The reason I do this is all about taking the id from the previous step to create the relationships between the tables. without taking the xml down to the tables?The problem with this approach is I have xml stored in most tables and the tables are becoming very large.
Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?
For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.
I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example :
table MOTHER (MOTHER_ID, MOTHER_NAME)
table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME)
After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table.
My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table".
The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times...
Is there a solution to chain two insertions with a foreign key constraint in a data flow?
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO ======================================= -- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO ========================================= -- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
======================================== The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask: (1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables? Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period? (2) How can I set up the relationships among these 3 dbo Tables?
I am trying to join two tables on multiple fields. But the nulls aren't considered a match so they aren't included in the results set.
Select A.Lot, A.Block, A.Plan, B.Key from A join B on A.Lot=B.Lot A.Block=B.Block A.Plan=B.Plan
In the data, there can be an instance where Block is null in both tables so it "matches" but not in SQL. How do I get the "matched" nulls to be returned as well?
Update the taskname as 'projectname' + '_' + 'workname' for any projectid. projectname and workname coming from the projectid and workid in the task record
so the task table becomes 1,project1_work1,1,1 2,project1_work2,1,2 3,project2_work3,2,3
I can get all the records doing this
SELECT p.projectname+ ' ' + w.workname AS 'NEWNAME' FROM task t JOIN work w ON t.workid = w.workid JOIN project p ON t.projectid = p.projectid WHERE projectid = 2
