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

SQL Server Performance Forum – Threads Archive

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

Is there a better way to do this insert?:<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE PROC insert_defaults<br />@User_num bigint<br />As<br /> <br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 1, 1)<br /><br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 2, 1)<br /><br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 3, 1)<br /><br />etc,…</font id="code"></pre id="code"><br /><br /><br /><br /><br /><b>Complete explanation:</b><br /><br />I have a table called ‘data_visibility#%92 to store user preferences about show or hide some data in the website:<br /><br />Table name: data_visibility<br />Data_v_id <br />User_num <br />Data_num <br />ShowHide <br /><br /><br /> 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 (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, and I don#%92 t know if there is a more efficient way to do it than this: <br /><br />(In ShowHide column ‘1#%92 means Visible, and ‘2#%92 means No Visible)<br />(Data_num column is a FK that has a separate table with data description)<br /><pre id="code"><font face="courier" size="2" id="code"><br />Use market8<br />GO<br />CREATE PROC insert_defaults<br />@User_num bigint<br />As<br /><br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 1, 1)<br /><br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 2, 1)<br /><br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 3, 1)<br /><br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 4, 2)<br /><br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 5, 2)<br /><br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 6, 2)<br /><br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 7, 2)<br /><br />Insert Into data_visibility (User_num, Data_num, ShowHide)<br />Values (@User_num, 8, 2)<br /><br />GO</font id="code"></pre id="code"><br /><br />Is there a more efficient way to do this insert?<br /><br />Thank you,<br />Cesar
Hi,<br /><br />your SP seems to be ok with the information you give. I see only one problem.<br />If default value for one Data_num is changed then you will have to change your SP.<br /><br />You say that you have one table with data_num information, so this table could include one extra column with the default value for new users so you’d have:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Data_mundescriptiondefault_value<br />1Option 11<br />2Option 21<br />3Option 31<br />4Option 42<br />…<br /></font id="code"></pre id="code"><br /><br />probably you have one sp to insert new users so your sp will do<br /><br />1 – Insert new user into users table<br />2 – Insert user preferences into data_visibility table<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />INSERT <br />INTO data_visibility <br />SELECT <br /> @user_num, <br /> data_num, <br /> default_value <br />FROM data_num_table <br /><br /></font id="code"></pre id="code"><br /><br /><br />In this way if you change default value for one data_num you will not change your SP. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br /><br />
Use a SELECT statement instead of the VALUES statement – which for some reason is the first thing you will find in BOL. Yes, you can use a SELECT query, and it would be nice if it was the forst option they gave you in BOL.
Hi ji06, that#%92 s a very good idea!, thank you so much [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
]]>