Basically, what is the best method, ie. not in, left join, etc to write this insert query? I feel like I know this, but mostly I want to make sure I am using the most efficient way. There are 1.1M records in the first table, named Complete_Products, and 979K in tblProducts. I already ran the update query that selected on the ProductCode column in both that updated tblProduct for the records that were also in Complete_Products. That ran fine, a bit long, but it worked and updated the 805,273 records they had in common. Now I need to insert the new records that are in Complete_Products that are not in tblProducts.
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end) scenario In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table. I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
i want to implement something like let say i have 2 table...customer table and order table....order table has a foreign key of customer table (maybe the customer_id)...is there any way that let say, i want to insert a particular customer_id in the customer table. Then, it will insert the particular customer_id in the order table also. I want to make one statement query that can solve that situation?
my application will add and delete and update records in db my problem is when to insert I have one text box and one dropdownbox one to write the name of db and the dropdownbox to choose the holding server ..
this is the structure of each table >> servers_tbl : SRV_ID,Server_Name DB_tbl : DB_ID,DB_Name srvdb_tbl : DB_ID,SRV_ID(forign keys from the previous tables) so >>> I want to add a new db to a server so I am writing the new db name in the textbox and choose the server from the dropdownbox and press a button to add the db name in the DB_tbl.DB_Name and add the db id in the DB_tbl.DB_ID to the srvdb_tbl.DB_ID and server id in the Servers_tbl.SRV_ID any one can help me ....
Hi All! I really need to pick your brains for a moment. I am attempting to create a INSERT INTO query that will select recrods from another existing table. This part can figure out... INSERT INTO "table1" ("column1", "column2")SELECT "column1", "column2"FROM "table2" Easy enough, but my table1 contains additional fields that the select statement does not retrieve from table2 (lets say "column3" and "column4"). I want to statically assign values to column3 and 4 at the same time. "column3" = Yes "column4" = No is there a way to do this within the same insert statement? Thanks for all your help!
can somebody help me in this plz m new to .net...m using visual studio 2005 how do we write this query actually...Dim con As SqlConnection = New SqlConnection("Server=;uid=;pwd=;Database=") Dim ra As Integer con.Open() myCommand = New SqlCommand("Insert into Telecheck(DateCheck,Hospital,Ward,PhoneNumber,StaffName,StatusOfStaff,StatusOfPhone)values(getdate(),'" & ComboBox1.SelectedItem.text & "','" & ComboBox3.SelectedItem.text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & ComboBox4.SelectedItem.text & "','" & ComboBox2.SelectedItem.text & "') ", con) ra = myCommand.ExecuteNonQuery() MessageBox.Show("Record entered") con.Close() the above is the code for windows here i am getting an error which says "{"Public member 'text' on type 'String' not found."}"the type of database is sqlserver 2000 and the type of column are nvarchar,only date is datetime
Declare @UID intDeclare @FID varchar(50)set @UID = 1set @FID = '1,2,3'insert into table_name (UID,FID) values(@UID,'+@FID+') Can we have an insert query like above or is their another way to get the result like below the result should beUID FID 1 1 1 2 1 3
I am needing to insert two values from tables, one would be from a query, which is easy, one needs to be static, easy. The not easiness (not a word) comes from combining the two. Here would be an example of what I am trying to run...
Code:
insert into MEMBER_SUBSCRIPTION_CHANNEL_FORMAT (MEMBER_ID,SUBSCRIPTION_CHANNEL_FORMAT_ID) (select m.MEMBER_ID from MEMBER m,MEMBER_SUBSCRIPTION ms where m.MEMBER_ID = ms.MEMBER_ID and m.SUBSCRIPTION_FORMAT_ID = 1 and ms.SUBSCRIPTION_ID = 1), 1
the last part of the code ', 1' would be the static part. Any ideas?
Hi guys I need to insert some updated Terms from another table but it keeps telling me that it cant because it would be creating duplicates. The funny thing about it is I did a comparison with the two tables one table is called UpdateForTerms Table and the other is called TERMINATION
SELECT UpdateForTerms.EMPLOYEE, UpdateForTerms.EMP_STATUS, UpdateForTerms.TERM_DATE, UpdateForTerms.DEPARTMENT, UpdateForTerms.LAST_NAME, UpdateForTerms.FIRST_NAME, UpdateForTerms.DESCRIPTION FROM UpdateForTerms LEFT JOIN TERMINATION ON UpdateForTerms.EMPLOYEE = TERMINATION.[TM #] WHERE (((TERMINATION.[TM #]) Is Null));
I made the results of this into a query so I could update the Termination table. The reason I created this query is so that I could weed out those records the Terminations table already had. Kind of a futile attempt it seems
I've been going around in circles and was wondering if anyone could help with this insert query. (I'm using SQL Server 2005).
I have two tables aspnet_Users and aspnet_Draws. They have a 1 to many relationship, with UserID being the PK in aspnet_Users and the FK in aspnet_Draws. Table structure is:
aspnet_Draws ID (PK) UserId DrawDate NumberOfEntries
aspnet_Users UserId (PK) UserName
I want to insert a row into aspnet_Draws. UserID needs to be taken from aspnet_Users where UserName is a particular value, e.g. NameXXX
DrawDate and NumberOfEntries do not allow nulls and also need to be updated, though these values will be taken from a webForm and not from aspnet_Users. ID is automatically incremented.
IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL BEGIN DROP PROCEDURE dbo.InsITRRComments IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE dbo.InsITRRComments >>' ELSE PRINT '<<< DROPPED PROCEDURE dbo.InsITRRComments >>' END go SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
INSERT INTO ITRRComments VALUES ( @DacType, @DacTypeId, @TypeOfComment, @Comments, @RiskId_External_ID, @UserId, (select FullUserName from SecurityUsers where UserId=@UserId) @AmendedDateTime, 'N' ) END
GO
IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL PRINT '<<< CREATED PROCEDURE dbo.InsITRRComments>>>' ELSE PRINT '<<< FAILED CREATING PROCEDURE dbo.InsITRRComments>>>' go
GRANT EXECUTE ON dbo.InsITRRComments TO AppWrite go
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Subqueries are not allowed in this context. Only scalar expressions are allowed
This is the error that I'm getting, how do I get round the issue of a sub query problem
How do I use Enterprise manager to insert records from one table into a blank table ? I open my table (with data) as a query and change it to an Insert From query but it doesn't seem to build the query correctly.
Shiva writes "Hi I want to insert a new row in a table after any specified row and when i open the table in enterprise manage that row must be physically located on specified position. i want to do it only through the query. no stored procedures or triggers. Can u help me out plz?"
hello sir In my project i have one table in that table i have feild UserId,StockName,status,CreatedBy,CreateDate,AssignHistoryCode .... i want to edit one record in that table and insert new same record only CreatedBy,CreateDate,and status are different. so how is it possible pls help me its urgent.
Hello all,I'm trying combine 2 tables data into a single table. My tables looklike thisTable1IDint4filenamenvarchar25incidentnvarchar50dreportnvarchar10treportnvarchar7doccurnvarchar10DateOccureddatetime8toccurnvarchar7locnvarchar55ucodenvarchar20rwbynvarchar30dispnvarchar55Narrativentext16Table2IDint4nincidentnvarchar50ncomplnvarchar50nfilenamenvarchar25narrntext16I used these 2 statements to narrow each table down to matchingfilenames:Select *INTO TABLE1bfrom table1where exists(select * from table2 wheretable1.filename = table2.nfilename)SELECT *INTO TABLE2bFROM table2WHERE exists(select * from table1 wheretable1.filename = table2.nfilename)My issue now is how to copy the narr field in table2 and insert it inthe Narrative field in table1 where table1.filename = table2.nfilename.For every matching filename and nfilename(both are the keys) I need topull the narr field from table2 and insert it into Narrative intable1. Does this make sense? Any assistance is greatly appreciated
Sorry for x-post. Using Access 2000, what is wrong with this syntax?INSERT INTO Activity (IDType, ID, ProjectID, ComponentID, SubComponentID, ProcessDate )VALUES ('Employee', SELECT EmployeeID FROM tbl1104Mess , 6, 28,341, #11/02/04#)The desired result is to place 1 record into Activity for each 1 record intbl1104Mess.With all the literal values and the EmployeeID from tbl1104Mess. I get anerror when I execute this statement. Thanks for any feedback.Regards,Chris
I need to append records into a table with a two column primary key from a table that contains many records that already exist in the target table. How do I separate out the records in the source table that don't exist in the target?
When I used to do this in Access, I could write a simple append query that would automatically skip records in source that violated key constraints in the target. I'm trying to duplicate that funcionality.
i want to save date using inert query like insert into tablname(field1,f2) values('jan',"& format(system.date.now,"dd/MM/yyyy hh:mm ") so to give error that char will not be converted to date and time.plz help its urgent.the same problem is with select query toooooo.
Hi! create table testReturn(id int identity(100,1),name varchar(10)) How can I return the value of identity column after inserting the value. Dim objConn As SqlConnection Dim SQLCmd As SqlClient.SqlCommand Dim ds As New DataSet Dim strsql As String Try objConn = New SqlConnection objConn.ConnectionString = _ "Network Library=DBMSSOCN;" & _ "Data Source=localhost;" & _ "Initial Catalog=mydb;" & _ "User ID=userid;" & _ "Password=pass" objConn.Open() strsql = "insert into testReturn values ('a')" SQLCmd = New SqlClient.SqlCommand(strsql, objConn) Dim rowsAffected As Integer = 0 rowsAffected = SQLCmd.ExecuteNonQuery Dim rv As String rv = SQLCmd.Parameters(0).Value.ToString() Response.Write(rv) Catch ex As Exception Response.Write(ex.ToString) End Try
Shortly why this worksinsert dbo.t_Shopping_cart (Product_code,Name,Price,Group,Quantity,Total)select Product_code,Name,Price,Group,Quantity=1,Total=1FROM dbo.t_ProductWHERE Group=6 But this doesn'tinsert dbo.t_Shopping_cart (Product_code,Name,Price,Group,Quantity,Total)select Product_code,Name,Price,Group,Quantity=1,Total=Quantity*Price --this is actually line 4FROM dbo.t_ProductWHERE Group=6It gives this errorMsg 207, Level 16, State 1, Line 4Invalid column name 'Quantity'. It does not help if Quantity*Price is in brackets. Probably a simple thing, but I don't know it. Is it even possible to do this kind of query? RegardsLeif
so I know how to use tsql to print a value in just the query, but when I insert a column, I want it to return the value of the ID that was just created. I don't know how to do this and send the value back to asp.net so that I can move on with that value. Can any body help me with this? this is what I have so far. thanks for the help!@title varchar(40) = null,
Hello,I am trying to determine the best way to do the following. For simplicity we have two tables Master and Awards. These share a common pk UFID. Master contains columns (UFID, AccountName...) Awards contains columns (ID, UFID, AwardingAgency, Amount) When a user submits a new award in Formview I would like to populate the UFID automatically so that the user does not have to enter this each time. I am currently using the logged in username to select records for that user only. The value of this username matches Master.AccountName. Since I am hoping to use this same logic across many tables, I would appreciate any suggestions as to how best handle this.Thanks,Ken
hi all i m trying to insert in my datbase this query but it is giving error at the line myCommand.ExecuteNonQuery(); myConnection.Open();String insertCmd = "insert into CRtable(@CR_NO, @Asset_Type, @PortIDNew, @SpecialRequest, @MovePortFrom, @MovePortTo, @MoveHardware, @MoveSoftware, @AllSelected, @SpecialSoftwarePort, @SpecialSoftwares, @OtherSoftwares, @FolderName, @FolderPurpose, @FolderUserIds, @FolderRetentionPeriod, @SpecialAccessOnExistFolder, @AcessType, @ExistFolderUserId, @SpecialAcessOnIntranet, @InternetUserId, @MailId, @HeadSet, @Requester, @RequestDateTime, @Dept, @PurposeOfCrForm, @RequestedPort, @ecode, @qty, @process, @cost_evaluation, @capital_exp, @tat)"; SqlCommand myCommand = new SqlCommand(insertCmd,myConnection);myCommand.Parameters.Add("@CR_NO",crm1);myCommand.Parameters.Add("@Asset_Type",StrAssetType2+StrAssetType);myCommand.Parameters.Add("@PortIdNew",StrPorts);myCommand.Parameters.Add("@SpecialRequest",StrSpecialRequest);myCommand.Parameters.Add("@MovePortFrom",txtAssetMovementFrom);myCommand.Parameters.Add("@MovePortTo",txtAssetMovementTo);myCommand.Parameters.Add("@MoveHardware",StrAssetHardware);myCommand.Parameters.Add("@MoveSoftware",StrAssetSoftware);myCommand.Parameters.Add("@AllSelected", StrAssetStatus);myCommand.Parameters.Add("@SpecialSoftwarePort",StrSpecialSoftwarePort);myCommand.Parameters.Add("@SpecialSoftwares",StrSpecialSoftware);myCommand.Parameters.Add("@OtherSoftwares",StrOtherSoftware);myCommand.Parameters.Add("@FolderName",StrFolderName);myCommand.Parameters.Add("@FolderPurpose","");//myCommand.Parameters.Add("@FolderUserIds",StrFolderUserId);myCommand.Parameters.Add("@FolderRetentionPeriod",StrFolderRetentionDays);myCommand.Parameters.Add("@SpecialAccessOnExistFolder","");//myCommand.Parameters.Add("@AcessType",StrAcessType);myCommand.Parameters.Add("@ExistFolderUserId",StrExistFolderUserId);myCommand.Parameters.Add("@SpecialAcessOnIntranet","");//myCommand.Parameters.Add("@InternetUserId","");//myCommand.Parameters.Add("@MailId","");//myCommand.Parameters.Add("@HeadSet","");//myCommand.Parameters.Add("@Requester",StrRequest);myCommand.Parameters.Add("@RequestDateTime",time);myCommand.Parameters.Add("@Dept",StrDept);myCommand.Parameters.Add("@PurposeOfCrForm",StrPurpose);myCommand.Parameters.Add("@RequestedPort"," ");//myCommand.Parameters.Add("@ecode",ecode);myCommand.Parameters.Add("@qty",quantity);myCommand.Parameters.Add("@process",proces);myCommand.Parameters.Add("@cost_evaluation",cost);myCommand.Parameters.Add("@capital_exp"," ");//myCommand.Parameters.Add("@tat",tat);try {myCommand.ExecuteNonQuery();}catch (SqlException er) {Response.Write(er.StackTrace);} myConnection.Close();} the error mwssage is following Line 1: Incorrect syntax near ')'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ')'. Source Error: Line 182:// try Line 183:// {Line 184: myCommand.ExecuteNonQuery();Line 185:// }Line 186:// catch (SqlException er) Source File: c:inetpubwwwrootcr request formcrpreview1.aspx.cs Line: 184 why this is occuring?
