Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


problem select insert


problem select insert

Author
Message
gurbanov.1984
gurbanov.1984
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 113
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 www.mail.ru 1 1
gurbanov.1984
gurbanov.1984
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 113
how i make create procedure


insert
Neeraj Prasad Sharma
Neeraj Prasad Sharma
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 675
Create Procedure [procedureName]
As
-- your query goes here


GO -- end of procedure

Neeraj Prasad Sharma
Sql Server Tutorials
gurbanov.1984
gurbanov.1984
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 113
but
for me this query very diffcult
gurbanov.1984
gurbanov.1984
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 113
i have a query for 2 tables


bur hier 6 tables

this is diffucult for me
gurbanov.1984
gurbanov.1984
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 113
create procedure inserttable1
@phone int,
@name varchar(30)
as
insert table1
select @phone,
kod
from table2
where nam_d = @name
go
gurbanov.1984
gurbanov.1984
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 113
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
gurbanov.1984 (7/5/2013)
but
for 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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
gurbanov.1984
gurbanov.1984
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 113
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
-- 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




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search