Prev: Migrating from MS Access
Next: sql 2000 error handling
From: Pantso on 9 Jun 2010 12:27
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 (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 although i know its wrong but 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

1. Excuse my english
2. Thanx in advance

From: Plamen Ratchev on 9 Jun 2010 14:53
I am not sure I understand the requirements, but try the following:

INSERT INTO table1 (column2, column3, column4)
SELECT T1.column2, T1.column3, T2.column2
FROM table1 AS T1
JOIN table2 AS T2
ON T1.column4 = T2.column1;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Q on 10 Jun 2010 21:58
Seems like you have to insert data into the parent table first, so that you
could get the reference ID. Then you can get the inserted ID by the built-in
function SCOPE_IDENTITY()

DECLARE @Table2ID int;

INSERT INTO Table2 (column2) VALUES ('Music');
SET @Table2ID = SCOPE_IDENTITY();

INSERT INTO Table1 (column2, column3, column4)
VALUES ('Tom', 'Jones', @Table2ID);

SELECT * FROM Table2;
SELECT * FROM Table1;


"Pantso" wrote:

> 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 (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 although i know its wrong but 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
>
> 1. Excuse my english
> 2. Thanx in advance
>
> .
>
 | 
Pages: 1
Prev: Migrating from MS Access
Next: sql 2000 error handling