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"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[ca8msm]

Do you know what a sql script is? If not, it is sql code that can be used to run some sql. In this case, we want you to create a script that will:

1) Create the tables you are referring to
2) Inserts some dummy data into these tables
3) Explain how the other tables should be populates based on this data
4) Show what the data the tables will contain after you have run the solution that we provide

When you post this script, we can run it our our development machines. Then, we will have the same database structure as you and will be able to make some sense out of this thread.


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

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]

 

[gmmastros]

Mark,

I think it might be best to explain the click-click process for scripting tables.

Clickety-click-click.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LizSara]

Oh forget it, i was hoping for help not sarcasm

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"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]

And if you look at the number of posts this thread has (23 just to get to this point!), people have been trying to help you. If you can't follow simple instructions in order to show your problem, or be able to articulate your problem correctly, then there's not much anyone can do to help you. You need to start actually reading people's responses and take the time to understand what you need to do in order for us to help you.

Maybe a read of this will help you?


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

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]

You need to start actually reading people's responses and take the time to understand what you need to do in order for us to help you.

So not only are you sarcastic you're rude as well. Classy!

Obviously new users of SQL aren't welcome in your little club so i'll leave you to it.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"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 wasn't meant to be rude. Let's look at some of the responses you've had:

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

LizSara, I'm afraid I dont really understand what you mean.

You mean you want to introduce RI (Referential Integrity) to your datamodel?

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

Surely you can see from these responses that people can't understand what you actually want? You haven't, or weren't able to, actually show any level of understanding the need to explain in sql terms, the problem in hand. So, to spell it out clearly for you, you have been asked [!]3 times[/!] to post a script which highlights the problem:

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.

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.

You obviously haven't read these responses correctly (which is why I said you need to actually start reading people's responses and not just "gloss" over them), or are not capable of understanding why you need to provide this script, as 25 replies to this thread and we still haven't seen one. I even took the time and effort to explain in simple steps what we need:

1) Create the tables you are referring to
2) Inserts some dummy data into these tables
3) Explain how the other tables should be populates based on this data
4) Show what the data the tables will contain after you have run the solution that we provide

Now, even after all of this attempted help, you come back to the thread with a, quite frankly, bad attitude towards those who are trying to help you. If you look at those people who have tried to help you, they are respected members here and you should start to treat them with this respect. If you don't, and continue to act in this manner, then put simply you won't remain a member here for very long.

Obviously new users of SQL aren't welcome in your little club so i'll leave you to it.

Most of the members who post questions here are "newbies" and they get lots of help. Have a look at most of the recent questions and you'll see that new members get a lot of help regardless of their ability or experience. If you are not getting the help you think you should, maybe you should look at why. If others are getting help, but you aren't maybe there are several reasons as to why. Have a think about what reasons these could be, do something about them and then maybe people might be inclined to help you.


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

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 find this overquoting post above to be very confusing, mostly because every time i was asked to do something i provided what i thought i was being asked for and asked for clarification on the things i didn't understand. This i did more than once and (the post about RI, the post about table structures). You say that i haven't read responses, which is plainly untrue, i have tried to provide the information i was being asked for every time or asked for clarification.

The fact that i don't understand what was being asked for (and stated that quite plainly) you have totally ignored. Incidentally the post where you spelled out what you wanted was very helpful although i'm not sure how to do it i can try to find out.

I take offence at being told i have a bad attitude, when someone has posted a response that offers no help at all and includes a laughing icon. That's what i refer to when i talk about sarcasm and your club. Obviously other people know what that poster is referring to, i have no idea (and how could i).

So thanks to all the people that bothered to try and help me. Obviously the way i am trying to explain myself isn't helping any of you.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"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]

LizSara,

To hand you an SQL script, we need the following:

1. Table-structure and example of contents (all 3 tables)
2. Relationship between tables (you did provide this earlier)
3. Your business needs (which will become much clearer when we see example data)

Even if some comments have been a little baffling or perhaps have shown some impatience, the goal is still to solve your issue..

Ties Blom

 

[LizSara]

By table structure do you mean the results of desc on the table or something else?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"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"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[drlex]

I'll stick my head over the parapet - I find it unclear as to why you need a link to another table, as you appear to have the values to insert
You say that you have to

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

What else needs to be inserted to the table choices? Is it in fact an update of an existing record?



soi la, soi carré

 

[GKWilly]

LizSara,

It sounds like you need this script to UPDATE your record not INSERT..

Update choices set choice_id = 268275,at_id = 140706,base_id = 50, preference = 1
where stud_id =261555

Gone Drinking

 

[gmmastros]

LizSara,

I apologize. Clearly I have offended you, and I admit that was wrong. It is unusual for a question in this forum to go unanswered for several days. Usually, when that happens, it is the fault of the 'asker', not the 'answerers'. Sometimes questions are asked that are a bit incomplete, but there is enough information that 'we' can make some reasonable assumptions and help the asker anyway. In this case, there simply isn't enough information for us to help you.

You have 2 choices, you can provide the information required so that we can help you, or you can walk away from this site. It's your choice.

I will attempt to help you, but you will need to help me first. I am going to ask you to run some queries on your database. You should post the output of the queries in to a new post. I will attempt to ask ALL of the questions in this post. Please understand that there may be more information required in order to adequately answer your questions.

First, we need to know what the table structures look like.

Select Table_Name, Column_Name, Data_Type
From   Information_Schema.Columns 
Where  Table_Name In ('Choices', 'Transfers', 'Student')
Order By Table_Name, Ordinal_Position

Next, some sample data would be helpful. We are not looking for thousands of records. Usually just a small sampling would do.

So, run these:

Select top 5 * From Choices
Select top 5 * From transfers
Select top 5 * From Student

Finally, since you are trying to do an insert, show us what data you have available and then, for the 3 tables involved, how you want them to look AFTER the insert.

For Example.

Choices
[tt]
choice_id at_id base_id preference
--------- ----- ------- ----------
1 1 1 1
2 1 3 7[/tt]

When posting tabular data, it is usually best to enclose them in [ignore][tt](table data here)[/tt][/ignore]

Of course, if you insist on remaining mad at me, please let me know so that I don't spend any more time on this thread.







-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LizSara]

I've tried to explain what i need below. I hope i have included everything i should have done:

Every record in the database has a stud_id which is the Primary key of the Student table and is stored in other tables as well

Each student (stud_id) identified in a list has 1 or more preferences which are stored in the choices table. Each preference has a choice_id, preference and base_id

Each of these students also has an unique admission reference (at_id) which is generated when their preference are entered (1 at_id for 1 stud_id)

Each of these students also has a unique tg_id which is assigned to them when they are entered into the transfer group

The transfer group has a tgh_id which is assigned to it.

i have a list of students stud_id's, their preferences (preference number and base), a choice_id, tg_id, tgh_id and an at_id which i need to get from my list into the database so that these students can go to school next year.

The choices table doesn't include a stud_id

The transfers table does include a stud_id

Choices Table

CHOICE_ID NOT NULL NUMBER(10) *Primary Key*
BASE_ID NOT NULL NUMBER(10)
PREFERENCE NUMBER(2)
AT_ID NOT NULL NUMBER(10)

Transfers Table

AT_ID NOT NULL NUMBER(10) *Primary Key*
TG_ID NOT NULL NUMBER(10)
TGH_ID NOT NULL NUMBER(10)
STUD_ID_DESC NOT NULL NUMBER(10) (same as 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"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[LizSara]

Sorry, i think we posted at the same time before.

i am looking for the result to be

choice_id at_id base_id pref stud_id tg_id tgh_id
268275 140706 50 1 261555 1 43
268276 140706 1077 2 261555 2 43
268277 140706 172 3 261555 3 43
268278 140707 134 1 286395 4 43
268279 140707 211 2 286395 5 43
268280 140707 271 3 286395 6 43
268281 140708 73 1 267418 7 43


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"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"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[kaht]

LizSara, I hate to beat a dead horse here, but from what you pasted above you're still making us weed thru what you want the results to be before you've provided the source. For instance, I can see in what you want the results to look like that each of those columns corresponds to the tables that you've posted above. However, when trying to decrypt it to make some test data I can't get the results to match up. That may just be my inadequacies in SQL, because I still consider myself pretty green when it comes to databases the language. However, you still haven't provided the test data which (for me at least) is somewhat crucial to put it all together - some of the experts may not need that extra level of help that I do. So, given what you've posted so far, here's what I propose that you should provide. This makes it a simple copy/paste for someone such as myself to come up with your solution. First, create temporary table variables that match the table structure of your environment. Then fill those temporary table variables with sample data by inserting rows that would be similar to what is in your development environment. To do that, you would provide us with code similar to this. Also take note, when posting code snippets on Tek-Tips it is beneficial for all the readers if you wrap the snippet in [ignore]

[/ignore] tags. This creates the code boxes that you see - making the code easier to read and presenting it in a monospaced format.

[COLOR=green]--create the 2 test tables
[/color][COLOR=blue]declare[/color] @choices [COLOR=blue]table[/color] (CHOICE_ID [COLOR=blue]decimal[/color](10), BASE_ID [COLOR=blue]decimal[/color](10), PREFERENCE [COLOR=blue]decimal[/color](2), AT_ID [COLOR=blue]decimal[/color](10))
[COLOR=blue]declare[/color] @transfers [COLOR=blue]table[/color] (AT_ID [COLOR=blue]decimal[/color](10), TG_ID [COLOR=blue]decimal[/color](10), TGH_ID [COLOR=blue]decimal[/color](10), STUD_ID_DESC [COLOR=blue]decimal[/color](10))

[COLOR=green]--insert test data into choices table
[/color][COLOR=blue]insert[/color] [COLOR=blue]into[/color] @choices
[COLOR=blue]select[/color] 268275, 50, 1, 140706 union
[COLOR=blue]select[/color] 268276, 1077, 2, 140706 union
[COLOR=blue]select[/color] 268277, 172, 3, 140706 union
[COLOR=blue]select[/color] 268278, 134, 1, 140707 union
[COLOR=blue]select[/color] 268279, 211, 2, 140707 union
[COLOR=blue]select[/color] 268280, 271, 3, 140707 union
[COLOR=blue]select[/color] 268281, 73, 1, 140708

[COLOR=green]--insert test data into transfers table
[/color][COLOR=blue]insert[/color] [COLOR=blue]into[/color] @transfers
[COLOR=blue]select[/color] 140706, 1, 43, 261555 union
[COLOR=blue]select[/color] 140706, 2, 43, 261555 union
[COLOR=blue]select[/color] 140706, 3, 43, 261555 union
[COLOR=blue]select[/color] 140707, 4, 43, 286395 union
[COLOR=blue]select[/color] 140707, 5, 43, 286395 union
[COLOR=blue]select[/color] 140707, 6, 43, 286395 union
[COLOR=blue]select[/color] 140708, 7, 43, 267418

[COLOR=green]--let's see what we've put into the tables
[/color][COLOR=blue]select[/color] * [COLOR=blue]from[/color] @choices
[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @transfers

When you present your test samples in the form above it allows us to copy/paste your code directly into query analyzer. Since all the data is stored in table variables, the resources are released after the queries have ran - which means that it will not affect any production or development data. This way we can make changes to your examples to try to come up with a solution.

The sample data I posted above was what I tried to extract from your desired output, but by joining the 2 tables on the AT_ID column the result was not correct. If you could fill out the sample data like I did above and post it back here, it would make helping you much easier.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]