July 5, 2013 at 6:32 am
hi
in database have many tables for selection
for example
table 1
phone kod1 kod2 adress kod3 email kod4 kod5
--- --- --- --- --- ------- ---- --- ---- ----
table2
name1 kod1
---- ----
christie 1
james 2
table3
name2 kod2
------- -------
jhony 1
paul 2
table3
name2 kod2
----- -------
hasan 1
rashid 2
table4
name4 kod4
------ ---
asif 1
natiq 2
table5
name5 kod5
---------- -------
arnold 1
vandame 1
insert table1
11111 ,james ,jhony,chicago,hasan,www.mail.ru , asif,arnold
after inserted table 1
phone kod1 kod2 adress kod3 email kod4 kod5
--- --- --- --- --- ------- ---- ---
11111 2 1 chicago 1 http://www.mail.ru 1 1
July 5, 2013 at 6:38 am
how i make create procedure
insert
July 5, 2013 at 6:41 am
Create Procedure [procedureName]
As
-- your query goes here
GO -- end of procedure
Neeraj Prasad Sharma
Sql Server Tutorials
July 5, 2013 at 6:43 am
but
for me this query very diffcult
July 5, 2013 at 6:45 am
i have a query for 2 tables
bur hier 6 tables
this is diffucult for me
July 5, 2013 at 6:46 am
create procedure inserttable1
@phone int,
@name varchar(30)
as
insert table1
select @phone,
kod
from table2
where nam_d = @name
go
July 5, 2013 at 6:50 am
create table table1 (phone int,kod_name_d int ) --
create table table2 (nam_d nvarchar(30),kod int)
insert into table2 values('james',1)
insert into table2 values('stivens',2)
insert into table2 values('carlos',3)
create procedure inserttable1
@phone int,
@name varchar(30)
as
insert table1
select @phone,
kod
from table2
where nam_d = @name
go
EXEC inserttable1 @phone=11111,@name='james'
after inserted table1
phone kod_name_d
----- -----
11111 1
but i have 5 table for selection insert
July 5, 2013 at 6:52 am
gurbanov.1984 (7/5/2013)
butfor me this query very diffcult
It's very difficult for us too, because we don't know what you want to do. Please explain providing as much detail as necessary. There's a link in my signature ('please read this') to an article which provides guidelines for asking questions and what you may need to provide to us to assist in answering your question.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 6, 2013 at 3:12 am
create table table1 (phone int,kod_name int ,kod_fname int,kod_country int,kod_auto int) --
create table table2 (name_ nvarchar(30),kod int)
insert into table2 values('james',1)
insert into table2 values('stivens',2)
insert into table2 values('carlos',3)
create table table3 (f_name nvarchar(30),kod int)
insert into table2 values('john',1)
insert into table2 values('tayson',2)
insert into table2 values('swarzneger',3)
create table table4 (country nvarchar(30),kod int)
insert into table2 values('argentina',1)
insert into table2 values('brazilia',2)
insert into table2 values('korea',3)
create table table5 (m_auto nvarchar(30),kod int)
insert into table2 values('mersedec',1)
insert into table2 values('jaguar',2)
insert into table2 values('landrover',3)
how i make created procedure
for insert to table1
when name=james then in kod_name_d insert 1 (kod table2)
when fname=jhon then in kod_fname insert 1 (kod table3 )
when country=korea then in kod_country insert 3 (kod table4 )
when auto=jaguar then in kod_auto insert 2 (kod table5 )
forexamle
i inserted table 1
(11111,'james','jhon','korea','jaguar')
after inserted table1
table1
-----------
phone kod_name kod_fname kod_country kod_auto
----- -------- --------- ----------- --------
11111 1 1 3 2
July 8, 2013 at 4:19 am
-- First, correct all the mistakes in your sample table script:
DROP TABLE table1
DROP TABLE table2
DROP TABLE table3
DROP TABLE table4
DROP TABLE table5
create table table1 (phone int,kod_name int ,kod_fname int,kod_country int,kod_auto int) --
create table table2 (name_ nvarchar(30),kod int)
insert into table2 values('james',1)
insert into table2 values('stivens',2)
insert into table2 values('carlos',3)
create table table3 (f_name nvarchar(30),kod int)
insert into table3 values('john',1)
insert into table3 values('tayson',2)
insert into table3 values('swarzneger',3)
create table table4 (country nvarchar(30),kod int)
insert into table4 values('argentina',1)
insert into table4 values('brazilia',2)
insert into table4 values('korea',3)
create table table5 (m_auto nvarchar(30),kod int)
insert into table5 values('mersedec',1)
insert into table5 values('jaguar',2)
insert into table5 values('landrover',3)
-- You want to see what's happening with your INSERT.
-- this query shows all of the rows and all of the columns
-- which would be inserted, along with the input data.
-- Stare & Compare: check that everything looks correct.
;WITH NewRowForTable1 AS (
SELECT
phone = '11111',
name_ = 'james',
fname = 'john',
country = 'korea',
[auto] = 'jaguar')
SELECT
n.phone,
n.name_, kod_name = t2.kod,
n.fname, kod_fname = t3.kod,
n.country, kod_country = t4.kod,
n.[auto], kod_auto = t5.kod
FROM NewRowForTable1 n
LEFT JOIN table2 t2 ON t2.name_ = n.name_
LEFT JOIN table3 t3 ON t3.f_name = n.fname
LEFT JOIN table4 t4 ON t4.country = n.country
LEFT JOIN table5 t5 ON t5.m_auto = n.[auto]
-- Solution
-- INSERT the new row
;WITH NewRowForTable1 AS (
SELECT
phone = '11111',
name_ = 'james',
fname = 'john',
country = 'korea',
[auto] = 'jaguar')
INSERT INTO table1 (
phone,
kod_name,
kod_fname,
kod_country,
kod_auto)
SELECT
n.phone,
kod_name = t2.kod,
kod_fname = t3.kod,
kod_country = t4.kod,
kod_auto = t5.kod
FROM NewRowForTable1 n
LEFT JOIN table2 t2 ON t2.name_ = n.name_
LEFT JOIN table3 t3 ON t3.f_name = n.fname
LEFT JOIN table4 t4 ON t4.country = n.country
LEFT JOIN table5 t5 ON t5.m_auto = n.[auto]
-- Check that the INSERT succeeded
SELECT * FROM table1
-- Check that the inserted data is correct
SELECT t1.phone, t2.name_, t3.f_name, t4.country, t5.m_auto
FROM table1 t1
LEFT JOIN table2 t2 ON t2.kod = t1.kod_name
LEFT JOIN table3 t3 ON t3.kod = t1.kod_fname
LEFT JOIN table4 t4 ON t4.kod = t1.kod_country
LEFT JOIN table5 t5 ON t5.kod = t1.kod_auto
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 8, 2013 at 4:48 am
Duplicate thread I think...
http://www.sqlservercentral.com/Forums/Topic1470907-391-1.aspx
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply