SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 proble create insert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 07/05/2013 :  05:58:28  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
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)


how i make created procedure

for insert to table1

when name=james then in kod_name_d insert 1 (kod table2)



for example

i inserted table 1

(11111,'james')

after inserted table1

phone kod_name_d
----- -----
11111 1


kmkmmm

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/05/2013 :  06:03:36  Show Profile  Reply with Quote
you mean this i guess


create procedure inserttable1
@phone int,
@name varchar(30)
as
insert table1
select @phone,
kod
from table2
where nam_d = @name
go


and if you're not sure if table2 will have record with passed name you can even add that insert logoc also inside this

the procedure can be called as below


EXEC inserttable1 @phone=11111,@name='james'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 07/05/2013 :  06:14:02  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
thank you very much

kmkmmm
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 07/05/2013 :  06:34:32  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
but i once again question

create table table3(phone int,k_name int,fname nvarchar(20))

when i inserted table1 with procedure inserttable1

when inserted phone to table1 then insert this data to table

as trigger
but my server is mssql2000 in my server have not trigger


for example

insert into table 1

EXEC inserttable1 @phone='',@name='james'

table1

phone name
-- ---
0 1


EXEC inserttable1 @phone='33333',@name='james'

table1

phone name
-- ---
0 1

table3

phone k_name f_name

33333 1


kmkmmm
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 07/05/2013 :  07:08:21  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
??????????????

kmkmmm
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/05/2013 :  07:12:48  Show Profile  Reply with Quote
quote:
Originally posted by pascal_jimi

but i once again question

create table table3(phone int,k_name int,fname nvarchar(20))

when i inserted table1 with procedure inserttable1

when inserted phone to table1 then insert this data to table

as trigger
but my server is mssql2000 in my server have not trigger


for example

insert into table 1

EXEC inserttable1 @phone='',@name='james'

table1

phone name
-- ---
0 1


EXEC inserttable1 @phone='33333',@name='james'

table1

phone name
-- ---
0 1

table3

phone k_name f_name

33333 1


kmkmmm


trigger?
I never suggested any trigger usage.

when you use below execute statement

EXEC inserttable1 @phone='',@name='james'

you'll get 0 as value for phone as phone field is of type int and gets default value of 0

i dont know how you got posted result for

EXEC inserttable1 @phone='33333',@name='james'

as per my suggestion you should be getting this


phone kod_name_d
----- -----
33333 1



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 07/05/2013 :  07:34:25  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
thank you visakh
i posted result for your suggestion

but 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

kmkmmm
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 07/05/2013 :  07:35:10  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
how make i create procedure for 5 tables

kmkmmm
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 07/05/2013 :  08:24:20  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
???????????????????

kmkmmm
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/07/2013 :  10:06:42  Show Profile  Reply with Quote
quote:
Originally posted by pascal_jimi

how make i create procedure for 5 tables

kmkmmm


Same as how i gave suggestion for 2 tables. just extend it to include the logic for all the 5 tables.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 07/07/2013 :  16:22:49  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
thank you very much
visakh


kmkmmm
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/08/2013 :  01:27:43  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000