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 tables
The order table has a PK of orderID and the orderdetails has a FK of orderID
I know how to insert to the main table, but dont know how to populate the details at the same time
I have this.
Insert into supplyorders
select 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?
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
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables. 2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables #3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1 2nd choice: Change step 1 to use #3 instead of #1 3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Hello 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
I 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?
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
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
hello, I am new to Slq 2000 Database,Now I create an asp.net application with sql 2000, in my database I have two 2 table lets' say "OrderHead" and "OrderDetail",they look like this: OrderHead orderdetail ---order no ----orderno ---issuedate ----itemname ---supplier ----desccription ---amount -----price ----Qty Now I created a user-defined Collection class to storage order detail data in memory and bind to a datagrid control. I can transfer Collection data to xml file ,my problem as below : There have multiple records data in my xml file,and I want to send the xml file as argument to a store procedure in sql 2000
anyone can give me some advise or some sample code ?
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.
I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):
Table 1: Classes --Columns: ClassID, ClassName
Table 2: Roster--Columns: ClassID, StudentID, Student Name
What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.
What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.
The stored procedure I created was
DELETE FROM Classes WHERE ClassID=@classid
I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:
The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'. The statement has been terminated.
What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?
I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.
Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.
Further, is there something else I need to do besides assigning primary keys and foreign keys?
Hi, I have 3 tables: Employees with the fields:idEmployee and employeeName Roles with the fields:idRole and roleName. An employee can have one or many roles. I created the table EmployeeRoles with the fields: id,idEmployee,idRole. idEmployee and idRole are foreign keys. I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
I would like to know if it's possible to return a single record by joining the tables below. [Persons] PersonID [int] | PageViewed [int] =============== ================= 1 10 2 5 3 2 4 12
[PersonNames] - PersonID JOINS Persons.PersonID PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int] =============== ============== ======================= =================== 1 1 Samantha Brown 5 1 2 Samantha Green 10 2 3 Richard T 10 3 4 Riko T 0 4 5 Sammie H 0
[AltNames] - backup for searches caused by common spelling mistakes AltNameID [int] | AltNames [nvarchar] ================ ============================= 1 Sam, Samantha, Sammie, Sammy 2 Riko, Rico
[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID] NameID [int] | AltNameID [int] ============= ================ 1 1 4 1 3 2 This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch ========= ============ ================= ================= 1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy 2 5 Richard T Richard T 3 2 Riko T Riko T, Riko, Rico 4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy
[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].
The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field. Any help would be greatly appreciated. Thanks, Pete.
I have tried joining several tables and the result displays duplicate rows of virtually every line/row. I have tried using distinct but this didn't work. I know it could because there's several columns from some of the tables named the same.
Terminology question:Is there a term for a set of records related directly or indirectly by keyvalue in several tables? For example, a single invoice record and its lineitem records -or- a single customer, the customer's orders, the order linesfor those orders, the customer's invoices, and the invoice lines for thoseinvoices.I'm thinking the term might be graph, but I'm not at all certain of this.Thanks,Steve J
Lets say we are executing this query below to retrieve each customer and the amount associated to a tableÂ
"INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END) AS AMOUNT from tblSaleStatementCustomer  WHERE CustomerType = 'Dealer' GROUP BY CustomerName"
Which display the data like below
DEALER Â Â Â Â Â Â Â Â AMOUNT ------------------------------------------------ ABC Â Â Â Â Â Â Â Â Â Â Â Â Â 2000 XYZ Â Â Â Â Â Â Â Â Â Â Â Â Â 1000 Â Â
However I have one more table TABLE2 which contains two columns
DEALER Â Â Â Â Â Â Â Â OPENING ------------------------------------------------------- ABC Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 5000 XYZ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 7000
I want to modify my query in such a way that AMOUNT column should also add
OPENING from TABLE2 So that I must get the result like below
DEALER Â Â Â Â Â Â Â Â AMOUNT ------------------------------------------------ ABC Â Â Â Â Â Â Â Â Â Â Â Â Â Â 7000 XYZ Â Â Â Â Â Â Â Â Â Â Â Â Â Â 8000 Â Â
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 want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table
UserInfo with as data as UserID=1, FName = John, LName=Abraham and Industry = 2. I have a Industry table with data as ID=1 and Name= Sports, ID =2 and Name= Film.
When I query view where UserID=1 it should return record as
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.
I need to find out which ones are in the second and third table that do not show up in the first one, which ones are in the first and third but not in the second, and which ones are in the first and second but not in the third.
I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.
Here is an example:
create table TBL1(PID int, info1 varchar(10) )
Create table TBL2(TID int,PID int)
Create table TBL3(XID int,PID int)
insert into TBL1
select '1','Someone' union all
select '2','Will ' union all
select '4','Have' union all
select '7','An' union all
select '8','Answer' union all
select '9','ForMe'
insert into TBL2
select '1','1' union all
select '2','1' union all
select '3','8' union all
select '4','2' union all
select '5','3' union all
select '6','3' union all
select '7','5' union all
select '8','9'
insert into TBL3
select '1','10' union all
select '2','10' union all
select '3','8' union all
select '4','6' union all
select '5','7' union all
select '6','3' union all
select '7','5' union all
select '8','9'
I need to find the PID and the table it is missing from. So the results should look like:
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link, 'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts JOIN tblProduct p ON ts.ProductID = p.ProductID JOIN tblTool t ON ts.ToolID = t.ToolID JOIN tblOperation o ON ts.OperationID = o.OperationID WHERE ts.ToolID=66 ') oq LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
I'm trying to do something very basic here but I'm totally new to MS SQL Server Manager and MS SQL in general. I'm using the Database Designer in MS SQL Server Manager. I created two tables with the following properties: Schedule (ScheduleID as UniqueIdentifier PK, Time as dateTime) Course (CourseID as UniqueIdentifier PK, Name as VarChar(50)) I create a relationship by dragging the PK from the first table over to the second and I link on ScheduleID to CourseID columns (I'm not certain what type of relationship is created here N:N?). It appears to work, I can do a Select * and join the two tables to get a joined query. The problem starts when I try to populate the tables: a course will have a schedule. I can't seem to get the rows to populate across both tables. I try to select the pk from the first table and insert it into the second but it complians about it not being a uniqueidentifier. I know this is very basic but I can't seem to find this very basic tutorial anywhere. I come from the Oracle world of doing DB's so if you have some examples that relate across that would be great or better yet if you can point me to a good reference for doing M$ DB stuff that would be great. Thanks.
