How To Insert Multiple Values
Apr 14, 2014this is my data
AM1WSJZ1241
AM1WLSU7162
AM1SXBI5100
AM1TWXX0477
AM1MSMQ6167
AM1WRQP1810
AM1HNME1411
i want a query to insert a data in table
ADVERTISEMENT
'Insert Into' For Multiple Values Given A Table Into Which The Values Need To Go
Sep 1, 2007Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz
Insert Multiple Row Values
Oct 25, 2007hi everyone
how do i insert multiple rows in a database ?
i came up with something like this after googling but it does not work
INSERT INTO tblSold (LID, BuyerID,Date)select ('759','2106','2441') UNION ALLselect ('0','0','0') UNION ALLselect ('10/25/2007','10/25/2007','10/25/2007')
Insert Multiple Values
Jan 5, 2000i have about 2,000 record and i need to insert them in my table.
how do i insert these informations using this syntax??
au_id au_name au_fname
1003 vivian latin
1005 cecy mani
1004 bili david
insert into autors (au_id, au_name,au_fname)
Values ('1101', 'Rabit','jesicca')
thanks for your time and help.
Insert Multiple Values Into One Field
Jul 24, 2006i can't believe my situation is unique, but my searches for answers have proven fruitless, so please bear with me.i have a date field in my database, and have previously used a simple textbox to allow users to insert a date. i want to make this a little step a little nicer, however, and have added a dropdown for the month, a textbox for the date (i'm not "good" enough to dynamically change this around for a dropdown list), and a dropdown list for the next few years.what "insert" statement would allow me to accomplish this? what i currently have doesn't blow up, but neither does it actually insert anything. here's my statment:<asp:SqlDataSource ID="sdsVersion" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>" InsertCommand="INSERT INTO [Version] ([Major], [Minor], [Sub], [Rev], [Date]) VALUES (@Major, @Minor, @Sub, @Rev, @Month + '-' + @Day + '-' + @Year)"> <InsertParameters> <asp:Parameter Name="Major" Type="Int32" /> <asp:Parameter Name="Minor" Type="Int32" /> <asp:Parameter Name="Sub" Type="Int32" /> <asp:Parameter Name="Rev" Type="Int32" /> <asp:ControlParameter ControlID="ddlMonths" Name="Month" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="tbDate" Name="Day" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="ddlYears" Name="Year" PropertyName="SelectedValue" Type="Int32" /> </InsertParameters></asp:SqlDataSource>
View 7 Replies View RelatedSQL Server Insert Multiple Values
Dec 30, 2004I am dynamically creating a sql insert query from a web page that can potentially have multiple values to be inserted into a table. The query I have right now my query is shown below. When it runs, it only inserts one record (the values 24,3). Any ideas what I am doing wrong?
INSERT INTO [tblWebPageByRank]
([WebPageID],[RankID])
Values (24,3,24,5)
INSERT Multiple Values With One Query
Oct 26, 2004I want to know if MS SQL has ability to INSERT multiple values with one statement. For example MySQL is able to do it like so
INSERT INTO myTable (col1,col2,col3)
VALUES
('1','1','1'),
('2','2','2'),
('3','3','3'),
('4','4','4');
and this will insert four rows.
Does MS SQL Have anything similar or do I have to repeat the INSERT statement if I need to generate a dynamic insert?
Thanks,
Lito
Insert With Max Values From Multiple Tables
Dec 20, 2007I am trying to add records to 2 separate tables and each table has a unique ID from the other table. I need to find the highest number ID from both tables (and add 1) and add a record with both new values to both tables.
I have tried the following, but it is not working. Any help would be appreciated, Thanks (my tables are MEMOS and PDSMSGC)
INSERT INTO MEMOS (MemoID, ParentID, FieldName, MemoText)
SELECT MAX(MemoID) + 1 FROM "MEMOS", MAX(MessageID) +1 FROM "PDSMSGC", 'pmc:MemoID', 'Hi my name is bob'
INSERT INTO PDSMSGC (MessageID, MemoID) SELECT MAX(MessageID) + 1 FROM "PDSMSGC", MAX(MemoID) FROM "MEMOS"
Insert Values In Multiple Table With Foreign Key
Feb 28, 2008Hi,
I've been working as web dev for quite sometime now but there are still few things that i would like to clarify. Hope you guys can shed lights.
I currently have several tables and all this tables are having some 1 to many relationships. I know that how i insert value into my tables are very inefficient. I do it in this manner..
-> Insert a value in TableA
-> Query the latest ID that i have inserted in Table A then
-> Insert this value in TableB. And so on and so forth.
I believe there is an efficient way to to do this but i'm not sure how? Could anyone shed me a light on this matter?
Your reply would be highly appreciated. TIA.
Why Wont This Multiple Selection Listbox Insert Values Into Db
Jan 19, 2008Hi, im a newbie. using VB.net / visual studio 2005 and SQL express
I have pasted my page code below
Basically i have a lisbox which is bound to an sqldatasource. It allows the user to select multiple names. I then have another datasource which when the page is submitted it posts various values from different fields into my db. The insert works fine.My problem is that the multiple selection wont allow me to insert into my db, it just captures the first value.I used this sub below to prove that when i click a button on my page it takes all my selected choices and puts them inside a text box, this works i can see them all. I then bound the textbox text value to the one im inserting into the db, however once again its only inserting the first value from the text box, how is this possible, the text box shows all the values and its set to string, it should insert everything in the text box Protected Sub selectedContact_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim alternatives As ListBox = CType(UploadPictureUI.FindControl("alternatives"), ListBox)
Dim TextBox1 As TextBox = CType(UploadPictureUI.FindControl("TextBox1"), TextBox)
For intLoopIndex As Integer = 0 To alternatives.Items.Count - 1
If alternatives.Items(intLoopIndex).Selected Then
TextBox1.Text &= alternatives.Items(intLoopIndex).Text & _
ControlChars.CrLf
End If
Next
End Sub
Here is my full page code
1 <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Create Profile" Debug="true" %>
2 <script runat="server">
3 Protected Sub UploadPictureUI_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles UploadPictureUI.ItemInserting
4 'Reference the FileUpload control
5 Dim UploadedFile As FileUpload = CType(UploadPictureUI.FindControl("UploadedFile"), FileUpload)
6
7 'Make sure a file has been successfully uploaded
8 If UploadedFile.PostedFile Is Nothing OrElse String.IsNullOrEmpty(UploadedFile.PostedFile.FileName) OrElse UploadedFile.PostedFile.InputStream Is Nothing Then
9 ShowErrorMessage("No file was uploaded. Please make sure that you've selected a file to upload.")
10 e.Cancel = True
11 Exit Sub
12 End If
13
14 'Make sure we are dealing with a JPG or GIF file
15 Dim extension As String = Path.GetExtension(UploadedFile.PostedFile.FileName).ToLower()
16 Dim MIMEType As String = Nothing
17
18 Select Case extension
19 Case ".gif"
20 MIMEType = "image/gif"
21 Case ".jpg", ".jpeg", ".jpe"
22 MIMEType = "image/jpeg"
23 Case ".png"
24 MIMEType = "image/png"
25
26 Case Else
27 'Invalid file type uploaded
28 ShowErrorMessage("Only GIF, JPG, and PNG files can be uploaded.")
29 e.Cancel = True
30 Exit Sub
31 End Select
32
33 'Specify the values for the MIMEType and ImageData parameters
34 e.Values("MIMEType") = MIMEType
35
36 'Load FileUpload's InputStream into Byte array
37 Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte
38 UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
39 e.Values("imageData") = imageBytes
40 End Sub
41
42 Private Sub ShowErrorMessage(ByVal msg As String)
43 ErrorMessage.Text = msg
44 ErrorMessage.Visible = True
45 End Sub
46
47 Protected Sub UploadPictureUI_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles UploadPictureUI.ItemInserted
48 'If item successfully inserted, send user back to default
49 If e.Exception Is Nothing Then
50 Response.Redirect("~/confirmation.aspx")
51 End If
52 End Sub
53
54 Protected Sub selectedContact_Click(ByVal sender As Object, ByVal e As System.EventArgs)
55 Dim alternatives As ListBox = CType(UploadPictureUI.FindControl("alternatives"), ListBox)
56 Dim TextBox1 As TextBox = CType(UploadPictureUI.FindControl("TextBox1"), TextBox)
57 For intLoopIndex As Integer = 0 To alternatives.Items.Count - 1
58 If alternatives.Items(intLoopIndex).Selected Then
59 TextBox1.Text &= alternatives.Items(intLoopIndex).Text & _
60 ControlChars.CrLf
61 End If
62 Next
63 End Sub
64
65
66 </script>
67 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
68 <table border="0" cellpadding="0" cellspacing="0" style="vertical-align: middle;
69 width: 795px; text-align: center">
70 <tr>
71 <td align="center" style="height: 650px" valign="middle">
72 <asp:DetailsView ID="UploadPictureUI" runat="server" AutoGenerateRows="False" CssClass="createProfile"
73 DataKeyNames="id" DataSourceID="accessProfiles" GridLines="None" DefaultMode="Insert">
74 <Fields>
75 <asp:TemplateField HeaderText="Create Profile">
76 <InsertItemTemplate>
77 <table style="width: 350px">
78 <tr>
79 <td align="center" style="width: 150px; height: 30px">
80 Name:</td>
81 <td align="center" style="width: 200px; height: 30px">
82 <asp:TextBox ID="Nametxtbx" runat="server" CssClass="textboxstyle" Text='<%# Bind("Name") %>' MaxLength="20"></asp:TextBox>
83 <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Nametxtbx"
84 CssClass="errorMessage" ErrorMessage="First Name is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
85 </tr>
86 <tr>
87 <td align="center" style="width: 150px; height: 30px">
88 Email:</td>
89 <td align="center" style="width: 200px; height: 30px">
90 <asp:TextBox ID="emailtxtbx" runat="server" CssClass="textboxstyle" Text='<%# Bind("email") %>'></asp:TextBox>
91 <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="emailtxtbx"
92 CssClass="errorMessage" ErrorMessage="Email Address is Required" ForeColor="">*</asp:RequiredFieldValidator>
93 <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="emailtxtbx"
94 ErrorMessage="RegularExpressionValidator" ForeColor="" ValidationExpression="w+([-+.']w+)*@w+([-.]w+)*.w+([-.]w+)*">*</asp:RegularExpressionValidator></td>
95 </tr>
96 <tr>
97 <td align="center" style="width: 150px; height: 30px">
98 Mobile:</td>
99 <td align="center" style="width: 200px; height: 30px">
100 <asp:TextBox ID="mobiletxtbx" runat="server" CssClass="textboxstyle" Text='<%# Bind("mobile") %>' MaxLength="11"></asp:TextBox>
101 <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="mobiletxtbx"
102 CssClass="errorMessage" ErrorMessage="Mobile Number is Required" ForeColor="">*</asp:RequiredFieldValidator>
103 </td>
104 </tr>
105 <tr>
106 <td align="center" style="width: 150px; height: 30px">
107 Extension:</td>
108 <td align="center" style="width: 200px; height: 30px">
109 <asp:TextBox ID="extensiontxtbox" runat="server" CssClass="textboxstyle" Text='<%# Bind("extension") %>' MaxLength="11"></asp:TextBox>
110 <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="extensiontxtbox"
111 CssClass="errorMessage" ErrorMessage="Phone Extension is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
112 </tr>
113 <tr>
114 <td align="center" style="width: 150px; height: 30px">
115 Alternative Contact:</td>
116 <td align="center" style="width: 200px; height: 30px">
117 <asp:ListBox ID="alternatives" runat="server" CssClass="listboxstyle"
118 DataSourceID="getAlternatives" DataTextField="Name" DataValueField="Name" SelectedValue='<%# Bind("alternativeContact") %>'
119 SelectionMode="Multiple"></asp:ListBox>
120 <asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="alternatives"
121 CssClass="errorMessage" ErrorMessage="Alternative Contact is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
122 </tr>
123 <tr>
124 <td align="center" style="width: 150px; height: 30px">
125 Job Role:</td>
126 <td align="center" style="width: 200px; height: 30px">
127 <asp:DropDownList ID="DropDownList2" runat="server" CssClass="dropdownliststyle" DataSourceID="accessrole"
128 DataTextField="role" DataValueField="roleID" AppendDataBoundItems="True" SelectedValue='<%# Bind("roleID") %>'>
129 <asp:ListItem Value="" Selected="False">- Please Select -</asp:ListItem>
130 </asp:DropDownList>
131 <asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="DropDownList1"
132 CssClass="errorMessage" ErrorMessage="Job Role is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
133 </tr>
134 <tr>
135 <td align="center" style="width: 150px; height: 30px">
136 Responsibilities:</td>
137 <td align="center" style="width: 200px; height: 30px">
138 <asp:TextBox ID="resposibilities" runat="server" CssClass="textareastyle" Text='<%# Bind("responsibilities") %>'
139 TextMode="MultiLine" MaxLength="200"></asp:TextBox>
140 <asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ControlToValidate="resposibilities"
141 CssClass="errorMessage" ErrorMessage="Role Responsibilities are Required" ForeColor="">*</asp:RequiredFieldValidator></td>
142 </tr>
143 <tr>
144 <td align="center" style="width: 150px; height: 29px">
145 Hobbies:</td>
146 <td align="center" style="width: 200px; height: 29px">
147 <asp:TextBox ID="hobbiestxtbx" runat="server" CssClass="textareastyle" Text='<%# Bind("hobbies") %>'
148 TextMode="MultiLine" MaxLength="200"></asp:TextBox></td>
149 </tr>
150 <tr>
151 <td align="center" style="width: 150px; height: 30px">
152 Team Name:</td>
153 <td align="center" style="width: 200px; height: 30px">
154 <asp:DropDownList ID="DropDownList1" runat="server" CssClass="dropdownliststyle" DataSourceID="accessTeam"
155 DataTextField="TeamName" DataValueField="TeamID" AppendDataBoundItems="True" SelectedValue='<%# Bind("TeamID") %>'>
156 <asp:ListItem Value="" Selected="False">- Please Select -</asp:ListItem>
157 </asp:DropDownList>
158 <asp:RequiredFieldValidator ID="RequiredFieldValidator9" runat="server" ControlToValidate="DropDownList2"
159 CssClass="errorMessage" ErrorMessage="Team Name is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
160 </tr>
161 <tr>
162 <td align="center" style="width: 150px; height: 30px">
163 I am a Manager:</td>
164 <td align="center" style="width: 200px; height: 30px">
165 <asp:CheckBox ID="isManager" runat="server" Checked='<%# Bind("isManager") %>' Text="Yes" /></td>
166 </tr>
167 <tr>
168 <td align="center" style="width: 150px; height: 30px">
169 I am a Team Manager:</td>
170 <td align="center" style="width: 200px; height: 30px"><asp:CheckBox ID="isTeamManager" runat="server" Checked='<%# Bind("isTeamManager") %>' Text="Yes" /></td>
171 </tr>
172 <tr>
173 <td align="center" style="width: 150px; height: 30px">
174 MyManager is:</td>
175 <td align="center" style="width: 200px; height: 30px">
176 <asp:DropDownList ID="myManagerIs" runat="server" CssClass="dropdownliststyle" DataSourceID="MyManager"
177 DataTextField="Name" DataValueField="id" AppendDataBoundItems="true" SelectedValue='<%# Bind("teamManagerID") %>'>
178 <asp:ListItem Value="" Selected="False">- Please Select -</asp:ListItem>
179 </asp:DropDownList>
180 </td>
181 </tr>
182 <tr>
183 <td align="center" style="width: 150px; height: 30px">
184 Upload Image:</td>
185 <td align="center" style="width: 200px; height: 30px">
186 <asp:FileUpload ID="UploadedFile" runat="server"
187 Width="150px" /> </td>
188 </tr>
189 <tr>
190 <td align="center" style="width: 150px; height: 30px">
191 Image Title:</td>
192 <td align="center" style="width: 200px; height: 30px">
193 <asp:TextBox ID="imagetitle" runat="server" CssClass="textboxstyle" Text='<%# Bind("imageTitle") %>' MaxLength="30"></asp:TextBox>
194 <asp:RequiredFieldValidator ID="RequiredFieldValidator10" runat="server" ControlToValidate="imagetitle"
195 CssClass="errorMessage" ErrorMessage="Image Title is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
196 </tr>
197 <tr>
198 <td align="center" style="width: 150px; height: 30px">
199 Hide This Profile:</td>
200 <td align="center" style="width: 200px; height: 30px">
201 <asp:CheckBox ID="profileHide" runat="server" Checked='<%# Bind("Display") %>' /></td>
202 </tr>
203 <tr>
204 <td align="center" colspan="2" style="height: 30px">
205 <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("alternativeContact") %>'></asp:TextBox><br />
206 <asp:LinkButton ID="selectedContact" runat="server" OnClick="selectedContact_Click" OnClientClick="selectedContact_Click">Add Selected Contacts</asp:LinkButton></td>
207 </tr>
208 <tr>
209 <td align="center" colspan="2" style="height: 30px">
210 <asp:ValidationSummary ID="ValidationSummary1" runat="server" CssClass="errorMsgstyle"
211 ForeColor="" />
212 </td>
213 </tr>
214 </table>
215 </InsertItemTemplate>
216 <HeaderTemplate>
217
218 </HeaderTemplate>
219 </asp:TemplateField>
220 <asp:TemplateField ShowHeader="False">
221 <EditItemTemplate>
222
223 </EditItemTemplate>
224 <InsertItemTemplate>
225 <asp:LinkButton ID="btnInsert" runat="server" CausesValidation="True" CommandName="Insert"
226 Text="Insert"></asp:LinkButton>
227 <asp:LinkButton ID="btnCancel" runat="server" CausesValidation="False" CommandName="Cancel"
228 Text="Cancel"></asp:LinkButton>
229 </InsertItemTemplate>
230 <ItemTemplate>
231
232 </ItemTemplate>
233 </asp:TemplateField>
234 </Fields>
235 </asp:DetailsView>
236 <br />
237 <asp:Label ID="ErrorMessage" runat="server" CssClass="errortext"></asp:Label> <br />
238
239 </td>
240 </tr>
241 <tr>
242 <td style="width: 100px">
243 <asp:SqlDataSource ID="accessProfiles" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
244 InsertCommand="INSERT INTO [employeeDetails] ([isManager], [isTeamManager], [Name], [RoleID], , [extension], [mobile], [alternativeContact], [imageTitle], [imageMimeType], [imageData], [TeamID], [Display], [teamManagerID], [responsibilities], [hobbies]) VALUES (@isManager, @isTeamManager, @Name, @RoleID, @email, @extension, @mobile, @alternativeContact, @imageTitle, @MimeType, @imageData, @TeamID, @Display, @teamManagerID, @responsibilities, @hobbies)" >
245 <InsertParameters>
246 <asp:Parameter Name="isManager" Type="byte" DefaultValue="0" Size="1" />
247 <asp:Parameter Name="isTeamManager" DefaultValue="0" Size="1" Type="Byte" />
248 <asp:Parameter Name="Name" Type="String" />
249 <asp:Parameter Name="RoleID" Type="Int32" />
250 <asp:Parameter Name="email" Type="String" />
251 <asp:Parameter Name="extension" Type="String" />
252 <asp:Parameter Name="mobile" Type="String" />
253 <asp:Parameter Name="alternativeContact" Type="String" />
254 <asp:Parameter Name="imageTitle" Type="String" />
255 <asp:Parameter Name="MimeType" Type="String" />
256 <asp:Parameter Name="imageData" />
257 <asp:Parameter Name="TeamID" Type="Int32" />
258 <asp:Parameter Name="Display" type="Byte" DefaultValue="1" Size="0" />
259 <asp:Parameter Name="teamManagerID" Type="Int32" />
260 <asp:Parameter Name="responsibilities" Type="String" />
261 <asp:Parameter Name="hobbies" Type="String" />
262 </InsertParameters>
263 </asp:SqlDataSource>
264 <asp:SqlDataSource ID="accessrole" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
265 SelectCommand="SELECT [role], [RoleID] FROM [role] ORDER BY [role] DESC"></asp:SqlDataSource>
266 <asp:SqlDataSource ID="MyManager" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
267 SelectCommand="SELECT id, Name, isManager, isTeamManager FROM employeeDetails WHERE (isManager > 0) OR (isTeamManager > 0) ORDER BY Name DESC">
268 </asp:SqlDataSource>
269 <asp:SqlDataSource ID="accessTeam" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
270 SelectCommand="SELECT [TeamID], [TeamName] FROM [Team] ORDER BY [TeamName] ASC"></asp:SqlDataSource>
271 <asp:SqlDataSource ID="getAlternatives" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
272 SelectCommand="SELECT [Name] FROM [employeeDetails] ORDER BY [Name]"></asp:SqlDataSource>
273 </td>
274 </tr>
275 </table>
276 </asp:Content>
277
278
Thanks for your help
T-SQL (SS2K8) :: Insert Multiple Values Based On Parameter
Jul 22, 2014I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below
CLASS Days
A sun
A Mon
A wed
Insert Multiple Rows To Table Based On Values From Other 2 Tables.
Nov 21, 2007I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId - SiteId
1 - 5
1 - 9
1 - 16
1 - 18
1 - 20
1 - 27
1 - 31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2
Apr 30, 2015table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
Reporting Services :: Selecting Multiple Parameters Values For Comma Separated Values In SSRS?
Jun 17, 2012I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies View RelatedRetrieving Multiple Values From One Field In SQL Server For Use In Multiple Columsn In Reports
Mar 30, 2007I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
Multiple Columns With Different Values OR Single Column With Multiple Criteria?
Aug 22, 2007Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
Adding Values To A Parameter That Can Take Multiple Values
Jun 6, 2007If I have a Select statement like this in my C# code:
Select * From foods Where foodgroup In (@foodgroup)
And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?
I tried
meat, dairy, fruit
'meat', 'dairy', 'fruit'
but neither worked. Is this possible?
SqlDataReader - Pulling Multiple Values Into Multiple Variables
Mar 29, 2007Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
View 1 Replies View RelatedOPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.
Mar 24, 2008Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
How Can I Insert Date Values From Char Values
Jan 4, 1999My process return a date in char format like "3/12/1998",
How could I put this data ( in char ) directly to date ISQ Server field ?
Thank's
Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values
May 10, 2015Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?
Aug 10, 2015Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
Multiple Insert Into Multiple Tables With A Stored Procedure
Mar 1, 2007Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
Insert Single Row / Multiple Rows Into Multiple Tables
Sep 3, 2014How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies View RelatedBulk Insert Multiple Files To Multiple Tables - How?
Feb 15, 2008I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?
Here is XML file:
Code Snippet
<ReferenceFiles>
<File>
<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>
<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>
Thanks.
How To Insert Values Without Duplicate Values?
Apr 8, 2008Hi,
I want to insert data into table without duplicate values ..
how to do?
Wants To Insert Into Multiple Table By A Single Insert Query
Apr 11, 2008Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
Multiple Insert Call For A Table Having Insert Trigger
Mar 1, 2004Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform
Jan 25, 2008I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
Transact SQL :: Update One Table Based On Another Table Values For Multiple Values
Apr 26, 2015I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies View RelatedVariable Insert To SQL Server Insert Satement Setting Values For The @variable INSIDE Sql
Apr 29, 2007ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
View 1 Replies View RelatedMultiple Values
Jan 18, 2002PO table has
itemnum - over 16000 records
closedate - can be 2001, 2002
location - is 1, 2, 3 etc
Like to find itemnum where location is 1 and itemnum has closedate of 2001, if it does have close date of 2001 than check if another record of same item with closedate of 2002. If yes, than diplay that item.
The result would be itemnum which have closedate of 2001 as well as 2002. Each item may have more than one record for each year.
MAX With Multiple Values.
Jun 17, 2008Hi.
I;m trying to sort out some values.
Ok so for sort. We have a contract and a renewal column.
A contract can have many renewals.
I'm trying to get the latest renewal for each contract (latest renewal is the one with the higher number.
so im trying this..
select distinct c.policyno,c.renewalno
from CONTRACT AS c inner join multicontract as d ON c.multicontractID = d.ID
where
c.renewalno in (select max(crn.renewalno) from contract crn
where
c.id= crn.id
But i keep getting multiple results (p.e. contra 12 ,renewal 13,14,15 and i want contract 12, renewal 15)
Any help?