Insert

Question

Insert

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mike Levan SSChampion Points: 14201 More actions · Answer 2

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Check this out :

Generate insert statements

Also what do you need to present on that form??

Mike Levan SSChampion Points: 14201 More actions · Answer 4

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.

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

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.

Lowell SSC Guru Points: 323547 More actions · Answer 6

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mike Levan SSChampion Points: 14201 More actions · Answer 7

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Stupid question here... what programming/database design formation do you have?

Lowell SSC Guru Points: 323547 More actions · Answer 9

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mike Levan SSChampion Points: 14201 More actions · Answer 10

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.

Mike Levan SSChampion Points: 14201 More actions · Answer 11

Do U mean tht the Design is not at all OK.

What changes u think have to be made in this scenario.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

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?

Mike Levan SSChampion Points: 14201 More actions · Answer 13

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

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))

Mike Levan SSChampion Points: 14201 More actions · Answer 15

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.