INSERT Problem - Please help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

INSERT Problem - Please help

Author

Topic

Pantso
Starting Member

4 Posts

Posted - 2010-06-09 : 08:54:24

Hi all

I am new to SQL and i would to know if anyone can help me work around this problem:

i have 2 tables

table1 (id: integer, name: string, surname: string, department: integer)

table 2 (id: integer, desciption: String)

where table1.department is related to table2.id

I want to make a statement in order my user to be able to insert values in table1 but given the following
('Tom', 'Jones', 'Music')
so it can go and retrive the number that correspond to 'Music' from
table2 and put it on table1

1. Excuse my english
2. Thanx in advance

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-09 : 09:06:13

Here your query.

insert into table1 (name,surname,department)
Select 'Tom', 'Jones',(select id from table2 where desciption='Music') as dept_id

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-09 : 09:07:41

select tab1.name, tab1.surname, tab2.description
from table1 tab1
inner join table2 tab2
on tab2.id = tab1.department
where tab2.description like '%Music%'

Pantso
Starting Member

4 Posts

Posted - 2010-06-09 : 09:31:21

first of all Thanx to you (senthil_nagore and RickD) both but i think i didnt write my question very good or i cannot understand very good what you answered
so this is what i was trying

i would make it more understanding by explainng without the true columns

i have

table1 (column1, column2, column3, column4) where column1 = integer - auto increment, clumn2 = text, column3 = text , column 4 = integer

and

table2 (column1 , column2) where column1 = integer - auto increment and column2 = text

Now table1.column4 is related to table2.column1 thats table1.column4 = table2.column1

what i was trying is to be have an SQL statement that can insert values that are not fixed maybe different every time
but i want them to be added into table1 and given for example ('Tom', 'Jones', 'Music')
where 'Tom' corresponds to tale1.column2 , 'Jones' corresponds to table1.column3 but 'Music' correspond to table2.column2 and i want to add the corresponding integer of that description that is the table2.column1

I have tried the following but i know its wrong maybe it helps

select table1.column2,table1.column3,table2.column2 from table1, table2
(INSERT INTO table1 (column2,column3, column4) VALUES ('Tom', 'Jones', 'Music'))
where table1.column4=table2.column1

thanx again

Pantso
Starting Member

4 Posts

Posted - 2010-06-09 : 09:36:47

quote:
Originally posted by RickD

select tab1.name, tab1.surname, tab2.description
from table1 tab1
inner join table2 tab2
on tab2.id = tab1.department
where tab2.description like '%Music%'

thanx but i think i didnt write the question right please see the previous post

Pantso
Starting Member

4 Posts

Posted - 2010-06-09 : 09:37:49

quote:
Originally posted by senthil_nagore

Here your query.

insert into table1 (name,surname,department)
Select 'Tom', 'Jones',(select id from table2 where desciption='Music') as dept_id

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

thanx but i think i didnt write the question right please see the previous post

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-10 : 04:22:02

insert into table1
select 'tom', 'jones', table2.id
from table1 tab1
inner join table2 tab2
on tab2.id = tab1.department
where tab2.description like '%Music%'

Noor Anwar
Starting Member

8 Posts

Posted - 2010-07-24 : 04:30:49

Respected Sir,

I'm not sure if this is posted in the correct forum topic area. I'm just looking for some general advice.

I have an desktop application(whole hospital opd and indoor patient record) that uses SQL 2005 and Visual Basic .Net,
many of tables are used a lot(store and retrieve data from same table from multiple clients)

how should i implement the indices that insertion and retrieval of data may not suffer.(speed slow)
my another question
i heared that if index is applied on a table then retrival of data is so much fast but insertion take longer?

and another question
if a table has primary key (is primary key auto indexed)and
if a table has foriegn key relationship. Are foreign key tables are auto indexed or manually have to index the foreign key tables.
and how i can properly index this database that no future slowness problems occure with respect to retrival and insertion of records.
Sir i am searching that no future problems occur.(That of speed slow problem at storing time that was in sql server 2000 i experienced only at 70,000 records system cooled down)

Please feel free to share your methods with me so I can go away and research it. Thank you in advance!

Signature

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources