Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

problem select insert Expand / Collapse
Author
Message
Posted Friday, July 5, 2013 6:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:02 AM
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
Post #1470697
Posted Friday, July 5, 2013 6:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:02 AM
Points: 46, Visits: 113
how i make create procedure


insert
Post #1470700
Posted Friday, July 5, 2013 6:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 27, 2013 5:37 AM
Points: 306, Visits: 524
Create Procedure [procedureName]
As
-- your query goes here


GO -- end of procedure









Neeraj Prasad Sharma
Sql Server Tutorials
Post #1470702
Posted Friday, July 5, 2013 6:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:02 AM
Points: 46, Visits: 113
but
for me this query very diffcult
Post #1470703
Posted Friday, July 5, 2013 6:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:02 AM
Points: 46, Visits: 113
i have a query for 2 tables


bur hier 6 tables

this is diffucult for me
Post #1470704
Posted Friday, July 5, 2013 6:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:02 AM
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
Post #1470705
Posted Friday, July 5, 2013 6:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:02 AM
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
Post #1470711
Posted Friday, July 5, 2013 6:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 7,135, Visits: 13,522
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
Post #1470713
Posted Saturday, July 6, 2013 3:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:02 AM
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
Post #1470905
Posted Monday, July 8, 2013 4:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 7,135, Visits: 13,522
-- 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
Post #1471107
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse