September 1, 2005 at 12:04 pm
you keep dancing around the issue i think; does the foreign key in table two already exist? i think not. does the data exist in another table and you are trying to change this from a flat table to a one-to-many relationship?
i looked at the rough schema you provided, and i did not see any implied relationship between the two tables. no columns named same/similarly, etc.
if there was an implied relationship, then you could infer the FK value based on that relationship, but i don't think you are giving enough einformation.
forget asking "theoretical" questions. use the actual information.
show the actual schema of the tables.
show the first handful few rows of the data in the tables as they are now.
with that, someone might identify the relationship.
Lowell
September 1, 2005 at 12:29 pm
How can provide data for u, can I attach any files in this forum?
Anyways Let me explain u clearly..there are no matching records in both the tables. my assumption regarding the relationship is just to retreive data from both the tables together in my APP and so I added a FK column in table2.
Do I need to have a similar column other than FK in table2 which match with a column in table1.
September 1, 2005 at 1:15 pm
September 2, 2005 at 8:22 am
Ok let me give another example for INSERT query
Table1[MeasureNO(PK),subsection,code,owner]
Table2[Type,source,MeasureNo(FK),owner]
Here in Table2 MeasureNo has multiple owner. How can I insert owner column in table1 from table2 where MeasureNo has relation in both tables.
September 2, 2005 at 9:10 am
Let me see if understand this. Table 1 (which is the Primary table) has no record in it. Table 2 (which is the Foreign Key table) has a record in it. For some unknow reason, either the record in Table 1 was deleted without cascading or someone manually entered a value in Table 2, (whatever the cause...).
If MeasureNO (PK) in Table 1 is an IDENTITY field, you will need to SET IDENTITY_INSERT ON to put Parent (Table 1) values from the Child table (Table 2) into the Parent table (Table 1). Be sure to SET IDENTITY_INSERT OFF when you TRANSACTION is completed.
I would also recommend you get a count of how many of these records need this action. I would SET NOCOUNT OFF and wrap your tranaction to make sure the counts match.
SET NOCOUNT ON
BEGIN TRANSACATION InsertIntoTable1
SET IDENTITY_INSERT Table1 OFF
Do your insert
SET IDENTITY_INSERT Table1 ONN
ROLLBACK TRANSACTION InsertIntoTable1
Once your numbers match, you can COMMIT TRANSACTIONS InsertIntoTable1
(Theoretical questions are often VERY difficult to answer, especially when you will not give all the information requested..., numerous times....).
I wasn't born stupid - I had to study.
September 2, 2005 at 9:12 am
ok much better. you still should have provided real table names instead of examples.
since you are inserting into two tables at run time, you'll want to do this with a stored procedure:
CREATE PROC PR_INSERT_TABLE1 ( @subsection int,@code varchar(3),@owner int,@type int,@source int)
AS
BEGIN
DECLARE @MeasureNO INT
INSERT INTO Table1[MeasureNO,subsection,code,owner) values (@MeasureNO,@subsection,@code,@owner)
--get the newly created primary key
SELECT @MeasureNO =scope_identity()
--sql 7 has no scope_identity, use @@identity instead, be aware on high volume db's this is prone to mistakes, hence the better function in SQL2000
INSERT INTO Table2 (MeasureNo,Type,source,,owner) values (@MeasureNo,@Type,@source,@owner)
END
Lowell
September 2, 2005 at 9:58 am
OK I think I have to give REAL table. sorry for the inconvinence.
Table PM
MeasureNum int 4 (PK)Identity
PMOrderBy int 4
Type char 1
Measure nvarchar 255
Frequency nvarchar 255
MeasuredBy nvarchar 255
Goal nvarchar 255
Source nvarchar 255
Definition ntext 16
Formula nvarchar 255
Table Owners
ComptCode nvarchar 50
MeasureNum int(FK)
Owner nvarchar 50
MeasureNum is the relation between tables.
I have inserted data into PM from a table of different database. Now I have to insert data into Owners from a table of different database, but cud not do tht since I have relation.
September 2, 2005 at 10:19 am
Stupid question here... what programming/database design formation do you have?
September 2, 2005 at 10:21 am
in the "other" database which was your original source for the [PM] data, is the information all together is a larger table?
can you post the schema of the source table?
what was the original SELECT statement that pulled the data from the "other" database into [PM]
the WHERE statement might tell us what relationships you are not revealing here.
Lowell
September 2, 2005 at 10:30 am
This is the insert statement pulling data from other DB
INSERT INTO perfmeasure..PM
(pmorderby,type,measure,frequency,measuredby,goal,source,definition,formula)
SELECT
perfsection,subsection,pmorderby,type,measure,frequency,measureby,goal,source,definition,formula
FROM PM..boa_pm
May be very silly..but I am damn new to SQL, learning things here.
September 2, 2005 at 11:07 am
Do U mean tht the Design is not at all OK.
What changes u think have to be made in this scenario.
September 2, 2005 at 11:29 am
I'm not trying to insult you in any, but if you tell me you have 0 experience, you need to buy a course or follow a course... this site is for help, not for teaching everything from A to Z.
A few comments on the design :
Why are you using nvarchars instead of any number format suitable for the information?? what are they all set at 255?
September 2, 2005 at 12:12 pm
Yeah u r right but the thing is I migrated data from Access into sql database,when i tried changing data type then it says data may be lost.
What can be done in such cases
September 2, 2005 at 12:16 pm
You can make sure no data loss is possible :
Declare @demo table (name nvarchar(25) not null primary key clustered)
insert into @demo (name) values (N'this is a long string')
insert into @demo (name) values (N'a short one')
--check if data would be loss if converted to varchar(20)?
Select len(name), name from @demo where name cast(name as varchar(20))
September 2, 2005 at 12:18 pm
FYI..
I am doing a job where I have to migrate old databases from Access and Oracle to SQL Server 2000 and then redesign them in a normalized way,later import all the data from those databases into my new design.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply