How can I insert more than one record in a table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

How can I insert more than one record in a table?

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2006-01-24 : 11:41:10

Is there a better way to do this insert?:


CREATE PROC insert_defaults
@User_num bigint
As
 
Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, State, 1)

Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, City, 1)

Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, Telephone, 1)

etc,....

Complete explanation:

I have a table called ‘data_visibility’ to store user preferences about show or hide some data in the website:

Table name: data_visibility
Data_v_id
User_num
Data_name
ShowHide

When the user is registered for the first time I want to insert all the default values in that table, but to do so I have to insert more than one record (8), and I don’ t know if there is a more efficient way to do it than this:

(In ShowHide column ‘1’ means Visible, and ‘2’ means No Visible)


Use market8
GO
CREATE PROC insert_defaults
@User_num bigint
As

Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, State, 1)

Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, City, 1)

Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, Telephone, 1)

Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, Telephone2, 2)

Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, Address, 2)

Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, Zip_code, 2)

Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, Address2, 2)

Insert Into data_visibility (User_num, Data_name, ShowHide)
Values (@User_num, Zip_code2, 2)

GO

Is there a more efficient way to do this insert?

Thank you,
Cesar

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-24 : 11:46:10

[code]
Insert Into data_visibility
(User_num, Data_name, ShowHide)
select
@User_num, State, 1
union all
select
@User_num, City, 1
union all
select
@User_num, Telephone, 1
etc.

[/code]

CODO ERGO SUM

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-24 : 11:49:55

In stored proc, get the Data_Name parameter
Pass Data_Name also as a parameter, when calling that.

cesark
Posting Yak Master

215 Posts

Posted - 2006-01-24 : 12:03:27

Ok Michael! Works fine, thanks

Srinika, would you mind giving an example about what you suggested please?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-24 : 12:36:26

-- U should have an interface in client side to add records to the Data_Name table
-- That interface should have table names (selection list), the field names (for a selected table)
-- Once the needed Table & fields (to be displayed) are selected, a query should be created in a loop (to loop thru all the selected list items) and create the data for the SP containing the generic Insert Statement
eg. in stored proc

Alter sp_.... (@sUser varchar, @FieldName varchar, ...)

Insert into Data_Name (F1,f2,f3, ...)
values ((@sUser , @FieldName , ....)

cesark
Posting Yak Master

215 Posts

Posted - 2006-01-24 : 13:12:58

But.. The SP is as you see:

(ShowHide column ‘1’ means Visible, and ‘2’ means No Visible)
I changed this:(Data_num column is a FK that has a separate table with data description)


Use market8 
GO 
CREATE PROC insert_defaults 
@User_num bigint 
As 

Insert Into data_visibility (User_num, Data_num, ShowHide) 
Select @User_num, 1, 1
Union all
Select @User_num, 2, 1
Union all
Select @User_num, 3, 1
Union all
Select @User_num, 4, 2
Union all
Select  @User_num, 5, 2 
Union all
Select  @User_num, 6, 2 
Union all 
Select  @User_num, 7, 2 
Union all
Select @User_num, 8, 2

GO

The data doesn' t come from anywhere (except @User_num). This is because are default values, then later, the user can change them, and then will be when user will send data such as 'User_num', 'Data_num' and 'ShowHide' values. But in this case the user only sends 'User_num'

Correct?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources