proble create insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

proble create insert

Author

Topic

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-05 : 05:58:28

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

52326 Posts

Posted - 2013-07-05 : 06:03:36

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

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-05 : 06:14:02

thank you very much

kmkmmm

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-05 : 06:34:32

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

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-05 : 07:08:21

??????????????

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 07:12:48

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

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-05 : 07:34:25

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

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-05 : 07:35:10

how make i create procedure for 5 tables

kmkmmm

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-05 : 08:24:20

???????????????????

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-07 : 10:06:42

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

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-07 : 16:22:49

thank you very much
visakh

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 01:27:43

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources