insert into table1 (colname) values (value1) can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table? We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take value1, value2, value3 .... from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks
I have searched in length and cant seem to find a specific answer.I have a tmptable to hold user "shoppingcart" ( internal supplies)What i want is to take when the user clicks order to take that table pull the records with that user and populate the order and order details tablesThe order table has a PK of orderID and the orderdetails has a FK of orderIDI know how to insert to the main table, but dont know how to populate the details at the same timeI have this.Insert into supplyordersselect requestor from tmpordercart where requestor = &name so how do i also take from the tmpordercart the itemno and quanity and put them into the orderdetails so that it links back to order table?
Hi Can anyone help with inserting Multiple records in to db using a select command or dropdownbox or listbox If I have a dropdownbox with 10 records in using a select query DRopdownbox data = select username, memberId, age from Memberslist where age = 30 I also have a textbox on the form called messageDetail Insert username, age and messagedetails into Messages for each item in dropdownbox
Hi and thanks for any advice.Right now I have a for loop that inserts multiple records. The first record is inserted into the database and I am not sure why. Here is the code I am using - Dim intPhotoKeyID As Integer Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc " InsertCmd += ") values " & _ "(@OriginalName, @GalleryID, @PhotoDesc " & _ ") " InsertCmd += "; SELECT CAST(scope_identity() AS int);"
Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString) Dim MyCommand = New SqlCommand(InsertCmd, DBConnection) MyCommand.Connection.Open() MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150)) MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int)) MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text)) myCount = myCount & i 'Is zero for first two records MyCommand.Parameters("@OriginalName").Value = "Orig" & i MyCommand.Parameters("@GalleryID").Value = 5
Try intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar()) MyCommand.Connection.Close() Catch Exp As Exception Response.Write(Exp) 'ResultsLabel.Text = Exp.ToString() End Try DBConnection.Close() Below is the whole procedure which either uploads an image or directory of images and resizes them. Then gets the meta data from the image. Then creates an entry into the database for each image. If I am dealing with 4 images then 4 images are uploaded to the new gallery folder. But 5 entries are added to the database. Thanks again for any help, Jennifer Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
If ListDirectories.SelectedValue.Length > 0 Or fileUpEx.HasFile Then Dim i As Integer Dim selectedDirectory As String = ListDirectories.SelectedValue Dim photoDescription As String Dim photoAuthor As String Dim photoTitle As String = "" Dim photoName As String Dim myFiles As String() Dim fileCount As Integer
If multiUpload.Visible = True Then myFiles = Directory.GetFiles(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/") fileCount = myFiles.Length - 1 Else fileCount = 0 End If
'Get last photo order from Photo table Dim PhotoMaxOrder As Integer = 0 Dim DS As DataSet ' DataSet object Dim SQL As String = "SELECT MAX(PhotoOrder) FROM Photos WHERE GalleryID=" & Request("ID").Trim Dim connString As String = ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString Dim sqlDA = New SqlDataAdapter(SQL, connString) DS = New DataSet sqlDA.Fill(DS, "Photos") If Not DS.Tables("Photos").Rows(0).Item(0) Is System.DBNull.Value Then If DS.Tables("Photos").Rows.Count > 0 Then PhotoMaxOrder = Convert.ToInt32(DS.Tables("Photos").Rows(0).Item(0)) + 1 End If Else PhotoMaxOrder = 1 End If
For i = 0 To fileCount
If multiUpload.Visible = True Then photoName = Right(myFiles(i), InStr(StrReverse(myFiles(i)), "/") - 1) Else photoName = fileUpEx.PostedFile.FileName End If
If InStr(photoName, ".jpg") > 0 Then Dim MyPhoto As Bitmap
If multiUpload.Visible = True Then MyPhoto = New Bitmap(myFiles(i)) Else MyPhoto = Bitmap.FromStream(fileUpEx.PostedFile.InputStream) End If 'testFile = myFiles(i) Try 'Get photo description Dim Make As PropertyItem = MyPhoto.GetPropertyItem("270") Dim ascii As Encoding = Encoding.ASCII photoDescription = ascii.GetString(Make.Value, 0, Make.Len - 1) Catch ex As Exception photoDescription = "" End Try
Try 'Get photo author Dim Make As PropertyItem = MyPhoto.GetPropertyItem("315") Dim ascii As Encoding = Encoding.ASCII photoAuthor = ascii.GetString(Make.Value, 0, Make.Len - 1) Catch ex As Exception photoAuthor = "" End Try
Dim photoXmpData As String = GetXmpXmlDocFromImageStream(MyPhoto)
If Not photoXmpData = "" Then photoTitle = GetXmpXmlNode(photoXmpData) End If
'insert photo record into photo table
Dim intPhotoKeyID As Integer Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc " InsertCmd += ") values " & _ "(@OriginalName, @GalleryID, @PhotoDesc " & _ ") " InsertCmd += "; SELECT CAST(scope_identity() AS int);"
Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString) Dim MyCommand = New SqlCommand(InsertCmd, DBConnection) MyCommand.Connection.Open() MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150)) MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int)) MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text)) myCount = myCount & i 'Is zero for first two records MyCommand.Parameters("@OriginalName").Value = "Orig" & i MyCommand.Parameters("@GalleryID").Value = 5
Try intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar()) MyCommand.Connection.Close() Catch Exp As Exception Response.Write(Exp) 'ResultsLabel.Text = Exp.ToString() End Try DBConnection.Close()
'check photo width and height Dim NewFilePath As String = Server.MapPath("/appscode/galleries/photos/g" & Request("ID").Trim & "/") & "p" & intPhotoKeyID.ToString & ".jpg" ' & photoName lblMessage2.Text = lblMessage2.Text & InsertCmd If MyPhoto.Width.ToString = "200" Or MyPhoto.Height.ToString = "200" Then 'just copy the image If multiUpload.Visible = True Then File.Copy(myFiles(i), NewFilePath, True) Else fileUpEx.SaveAs(NewFilePath) End If Else 'resize image Dim NewSize As System.Drawing.Size = New System.Drawing.Size(200, 200) ResizePicture(MyPhoto, NewFilePath, NewSize) 'and save it End If MyPhoto.Dispose() GC.Collect() End If
Next If Not myFiles Is Nothing Then If myFiles.Length > 0 And multiUpload.Visible = True Then 'delete directory that was just processed Directory.SetCurrentDirectory(Server.MapPath(".")) Directory.Delete(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/", True) ListDirectories.Items.Remove(selectedDirectory) 'lblMessage2.Text = "Your file(s) have been added." End If Else 'lblMessage3.Text = "Your file has been added." End If
Else lblMessage2.Text = "Please select a folder." End If
Using SSE 2012 64-bit.I need to insert records from multiple Access Tables into 1 Table in SSE and ensure no duplicates are inserted.This is executing, but is very slow, is there a faster way?
Code: INSERT INTO dbTarget.dbo.tblTarget (All fields) SELECT (All Fields) FROM dbSource.dbo.tblSource WHERE RecordID NOT IN (SELECT RecordID FROM dbTarget.dbo.tblTarget)
This is a bit lengthy, but lets say we have three tables
a) tblSaleStatementCustomer b) tblCreditors c) tblReceiptDue
which shows records like below
Table 1 - tblSaleStatementCustomer
ID  CustomerName   VoucherType   Outbound   Inbound   CustomerType ---------------------------------------------------------------------------------------------- 1   ABC                Sales         10000        0          Dealer 2   MNC               Sales          9000        0          Dealer 3   MNC               Sales          4000        0          Dealer
Table 2 - Â tblCreditors
ID  Name   OpeningBalance ---------------------------------------------------------------------------------------------- 1   ABC      20000  2   MNC     15000 3   XBM     18000 4   XYZ      12000
On my site users can register using ASP Membership Create user Wizard control. I am also using the wizard control to design a simple question and answer form that logged in users have access to. it has 2 questions including a text box for Q1 and dropdown list for Q2. I have a table in my database called "Players" which has 3 Columns UserId Primary Key of type Unique Identifyer PlayerName Type String PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table. I am having problems getting this to work and keep getting exceptions. Be very helpful if somebody could check the code and advise where the problem is??
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e) { SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1"); MembershipUser myUser = Membership.GetUser(this.User.Identity.Name); Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue; DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString()); DataSource.Insert();
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?
Is there a way to insert multiple records into a database table when you're just given "count" of the number of rows you want? I want to do this in ONE insert statment, so I don't want a solution that loops round doing 100 inserts - that would be too inefficient.
For example, suppose I want to create 100 card records starting it card number '1234000012340000'. Something like this ...
declare @card_start dec(16) set @card_start = '1234000012340000' declare @card_count int set @card_count = 100
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc: exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8' Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs) CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150) ASBEGIN DECLARE @FileID INT SET NOCOUNT ON /*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate) Select @FileID=@@Identity /*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000) SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')' exec(@ProductFilesInsert) EndGO
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT   PE.PropertyExternalReferenceValue  [BAReferenceNumber] , PA.DescriptionCode   [PSDCode] , PV.ValuationEffectiveDate   [EffectiveDate] , PV.PropertyListAlterationDate   [ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set  tables multiple timesÂ
Hi.. I really need a help. Is there any way to insert multiple records into a table in one go?
I have a table named Fruit. It contains FruitId,OwnerId,Colour
The list of colour is got from another table name FruitColour. FruitColour Consists of 2 column, FruitName and Colour.
Is it possible to insert multiple records into Fruit table with one query if only the colour is changed.
Sample case I have an Apple. Fruit id=2 OwnerId=2 Colour -- > Select Colour From FruitColour where FruitName='Apple' (Will return multiple records)
I tried to insert using this query: Insert into Fruit(FruitId,OwnerId,Colour) Values (2,2,Select Colour from FruitColour where FruitName='Apple').
Gives me this error Subqueries are not allowed in this context. Only scalar expressions are allowed.
I need to do this because actually I am inserting multiple fruit at one time, and each have multiple colour. If I need to insert the colour one by one for each fruit it will takes a very long time.
Any suggestion are welcomed. Thank you in advanced.
I am building an invoicing database. I have no problems searching fordue dates and generating the invoice header. The problem is generatingthe invoice detail.My customers may have more than one item that needs to go into theinvoice detail table.For example:customer #123 has 2 items that need to be placed into the detailtable.Rate 1 email accountRate 2 hosting accountI have to get both of these records into the detail table.When using the conventional method, I get something alongthe lines of" insert failed. more than one record was returned"-------INSERT INTO detailSELECT (SELECT max([id])FROM iheader),CustomerRates.custid,rates.Price, rates.nameFROM CustomerRates INNER JOIN Rates ON CustomerRates.Rateid = rates.IDWHERE NextBill > GETDATE()-------I have even considered a cursor to loop through the records but I cantmake it run properly. I am not crazy about the performance of cursorsanyway.Any aideas would be greatly apreciated.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
tblPayments (Contains the records of Payments we made)
ID Â Â Â Â DATE Â Â Â Â Â Â Â Â Â AMOUNT Â Â Â Â BANK ---------------------------------------------------------- 1 Â Â Â Â Â 05/05/2015 Â Â Â Â 5000 Â Â Â Â Â Â Â Natwest 2 Â Â Â Â Â 05/05/2015 Â Â Â Â 2000 Â Â Â Â Â Â Â Lloyds 3 Â Â Â Â Â 05/06/2015 Â Â Â Â 3500 Â Â Â Â Â Â Â Natwest 4 Â Â Â Â Â 05/07/2015 Â Â Â Â 4000 Â Â Â Â Â Â Â Natwest 5 Â Â Â Â Â 05/08/2015 Â Â Â Â 1500 Â Â Â Â Â Â Â Lloyds
tblReceipts (Contains the records of Receipts we received)
ID Â Â Â Â DATE Â Â Â Â Â Â Â Â Â AMOUNT Â Â Â Â BANK Â ---------------------------------------------------------- 1 Â Â Â Â Â 05/06/2015 Â Â Â Â 5000 Â Â Â Â Â Â Â Natwest 2 Â Â Â Â Â 05/06/2015 Â Â Â Â 2000 Â Â Â Â Â Â Â Lloyds 3 Â Â Â Â Â 05/07/2015 Â Â Â Â 3500 Â Â Â Â Â Â Â Natwest 4 Â Â Â Â Â 05/07/2015 Â Â Â Â 4000 Â Â Â Â Â Â Â Natwest 5 Â Â Â Â Â 05/08/2015 Â Â Â Â 1500 Â Â Â Â Â Â Â Lloyds
Now, I also have a blank table (tblBankStatement) which contain the following columns
ID Â Â Â DATE Â Â Â Â Â Â Â Â RECEIPT Â Â Â Â Â Â Â Â PAYMENT Â Â Â Â Â Â Â BANK -----------------------------------------------------------------------------
I want that when I execute the query, the query should INSERT the records to the New Table (tblBankStatement) from tblPayments and tblReceipts by Date Ordered in ascending way WHEREBank should be 'Natwest'.
Also the Amount Column Data in tblPayments should be Inserted into the Payment Column in tblBankStatement and the Amount Column Data in tblReceipts should be Inserted into the Receipt Column in tblBankStatement.
So I could get the data just like below
tblBankStatement
ID Â Â Â DATE Â Â Â Â Â Â Â Â RECEIPT Â Â Â Â Â Â Â PAYMENT Â Â Â Â Â Â Â BANK -------------------------------------------------------------- 1 Â Â Â Â 05/05/2015 Â Â Â Â Â Â 0.00 Â Â Â Â Â Â Â Â Â Â Â Â Â 5000 Â Â Â Â Â Â Â Natwest 2 Â Â Â Â 05/06/2015 Â Â Â Â Â Â 0.00 Â Â Â Â Â Â Â Â Â Â Â Â Â 3500 Â Â Â Â Â Â Â Natwest 3 Â Â Â Â 05/06/2015 Â Â Â Â Â Â 5000 Â Â Â Â Â Â Â Â Â Â Â Â Â 0.00 Â Â Â Â Â Â Â Natwest 4 Â Â Â Â 05/07/2015 Â Â Â Â Â Â 0.00 Â Â Â Â Â Â Â Â Â Â Â Â 4000 Â Â Â Â Â Â Â Natwest 5 Â Â Â Â 05/07/2015 Â Â Â Â Â Â 4000 Â Â Â Â Â Â Â Â Â Â Â Â Â 0.00 Â Â Â Â Â Â Â Natwest
What query should I write to perform the task above.         Â
Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.
I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic.
I'm trying to update a checkbox from "False" to "True" within a single table for multiple records. I can update a single record using the script below. However, I'm having trouble applying additional Id's to the string.
(Works) - Update Name_Demo set KEY_CONTACT = 'true' where ID = 225249
(doesn't work) - Update Name_Demo set KEY_CONTACT = 'true' where ID = '225249, 210014, 216543'
It says query executes successfully but returned no rows.
I have struggled and can't seem to get the SQL correct to get the records I want. I have 2 tables containing information from 2 separate independant sensor arrays which collect data about trains in a rail network. I need to get the most current records about the trains at any given time.
For any given moment in time, I want to query the database and get the last known position of each train and it's status. So I want a result table like this: given time = 10:07:20 TrainId | PosTime | X | Y | StatusTime | SensorId | Delay | OffDuty 109 | 5/10/2007 10:07:15 | 62389 | 25002 | 5/10/2007 10:06:51 | 75 | -5 | N 210 | 5/10/2007 10:07:15 | 65489 | 25432 | 5/10/2007 10:04:54 | 85 | -10 | Y 857 | 5/10/2007 10:07:15 | 62889 | 25983 | 5/10/2007 10:05:21 | 231 | +1 | N
OR given time=10:03:30 1 | 109 | 5/10/2007 10:03:30 | someX | someY | 5/10/2007 10:02:34 | 72 | -3 | N 2 | 210 | 5/10/2007 10:03:30 | someX | someY | null | null | null | null (Since train 210 it hasn't tripped a status sensor yet, we don't know it's status at that time) 3 | 857 | 5/10/2007 10:03:30 | someX | someY | 5/10/2007 10:02:48 | 199 | +2 | N
Any help to achieve these results would be MOST appreciated, Thanks.
I need to create a trigger to save every record which has been updated in a table (e.g. "Contacts") into another (e.g. "Shadow_contacts). So basically something like a versioning on database level. I have no experience with it and any precise hints/solutions would be very appreciated
we have various user defined fields, so when the user insert new value to this field , i want new records (other user field to get populated) be inserted relevant to the inserted value FROM another table (lookup)
This field is a drop down list , after insert committed , i want other user defined field to populate.
Scenario. Drop down is a list of users, after selecting and updating then i want contact details of the above selected user be populated.
CREATE TRIGGER User_Defined_field_KAE ON [dbo].[AMGR_User_Fields_Tbl] AFTER INSERT
AS
INSERT INTO [dbo].[AMGR_User_Fields_Tbl] (Client_Id, Type_Id, name SELECT Client_Id, '1089', A.USER_PHONE_1 FROM inserted , [dbo].[ADMN_User_Details] as A WHERE name = a.user_id;
