Newbie: Insert Statement 1

[LizSara]

I need to insert 4 values into a table and attach those values to a value in another table. can i do this using a single insert statement?



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[nickdel]

LizSara, technically you should use 4 seperate statements however you can also do it like this which is infact a little less resourceful than 4 inserts.

INSERT INTO [Table]
SELECT 'Value1','John'
UNION ALL
SELECT 'Value2','Liz'
UNION ALL
SELECT 'Value3','Nick'
....

 

[blom0344]

Isn't using a 'values' clause even more efficient?

What is meant by 'attaching those values to a value in another table' ?

Ties Blom

 

[nickdel]

blom, I'm not saying it's Gospel but I've always found if inserting a lot of values that this method produces slightly better results.


Nick

 

[LizSara]

that's good for the first bit but i then need to make the records i've inserted relate to another table that needs to use a third table to link them all together.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[nickdel]

LizSara, I'm afraid I dont really understand what you mean. in order to link them you need to have a common element like an unique ID. Do you have such a thing? Lets look at an example

Table A:

ID Name Country
-----------------------
1 Nick Scotland
2 John England
3 Paul Wales

Table B:

ID CustID Likes
-----------------------
1 1 Rugby
2 1 Football
3 2 Football
4 3 Tennis
5 3 Rugby

So in the above we have a unique ID on each table (Or primary key if you will) however we cannot use this to relate the 2 tables but we can us the ID from table A and the CustID from table B

select a.Name, a.Country, b.Likes
from TableA a inner join TableB b
         on a.ID = b.CustID

Results:
Nick, Scotland, Rugby
Nick, Scotland, Football
John, England, Football
Paul, Wales, Tennis
Paul, Wales, Rugby

Hope this helps!

Nick

 

[blom0344]

You mean you want to introduce RI (Referential Integrity) to your datamodel? You should look into RI / Foreign Keys constraints etc. Thats DDL , not DML ..

Ties Blom

 

[LizSara]

blom, i don't even understand what that means.

nickdel i need to link 2 tables because i need to relate the values i'm inserting to the primary key of the second table. unfortunately they don't link so i have to include a third table which links to both of them to bridge the gap.

what i need to know is if i can insert the values in table 1 attached to the primary key of table 2 (linked though table 3).

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[ca8msm]

It will be much easier for us to understand what you want if you show examples of what each table will contain (i.e. both the structure and actual data).


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

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[LizSara]

ok here goes;

table one needs to have choice_id, at_id, base_id, preference inserted into it but only once per row for stud_id from table 2.

table 1 (choices) doesn't link to table 2 (students) without table 3 (transfers) being in the middle.

so what i need to do is insert into choices (choice_id, at_id, base_id, preference) values (268275,140706,50,1) where stud_id =261555 but i can't.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[LizSara]

Does anyone have any thoughts on this, i seem to be banging my head against a wall.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[SQLSister]

join to all three tables.

Without knowing the eact structure of your tables I really can;t be more specific.

"NOTHING is more important in a database than integrity." ESquared

 

[blom0344]

Give us the joins between the 3 tables (relationship) and the tabledefinitions and we can compose the proper SQL insert DML..

Ties Blom

 

[LizSara]

the joins would be

choices.at_id = transfers.at_id and
transfers.stud_id = student.stud_id

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[blom0344]

Does the 'link' table contain multiple entries of at_id for each stud_id?
I suspect so, cause you want just one entry for each stud_id.
We need an example to give you feedback...

Ties Blom

 

[LizSara]

no the at_id is a 1 to 1 with the stud_id. the choice_id can be many though.

The at_id is created (usually) when the choice_id is entered for the stud_id.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[SQLSister]

so if you have the joins, what exactly is the problem you are having? CAn you post the code you have that isn;t working and explain what is the problem with it? referably with sample data from all three tables and samples results you want.

"NOTHING is more important in a database than integrity." ESquared

 

[LizSara]

what i have so far is:

insert into choices (choice_id, at_id, base_id, preference) values (268275,140706,50,1) where stud_id =261555

because i don't know how to join thet tables properly?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[ca8msm]

It will be much easier for us to understand what you want if you show examples of what each table will contain (i.e. both the structure and actual data).

with sample data from all three tables and samples results you want.

You still haven't posted the tables structures and example data from these tables. Post a test script that creates the three tables, inserts some dummy data into the tables that you do have data for, explain how the other tables should be populates based on this data and show what the data in these populated tables will look like once the solution has been completed.


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

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[LizSara]

I don't understand what you want in regards to table structures.

(from the insert i have included above) the result i want is

for stud_id 261555 i want a choice_id of 268275 an at_id of 140706 a base_id of 50 and a preference of 1

what else do you need?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~