Best Way To Right This Insert Query
May 1, 2008
Hi. I have 2 tables, this structure (basically);
Code Title
1 AAAAA
2 BBBBB
3 CCCCC
4 DDDDD
Code Title
1 AAAAA
2 BBBBB
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.
Thanks so much for your help in advance.
View 3 Replies
ADVERTISEMENT
Apr 11, 2008
Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
View 3 Replies
View Related
Mar 1, 2007
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
View 2 Replies
View Related
Jan 21, 2007
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?
View 4 Replies
View Related
Mar 15, 2007
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 ....
View 1 Replies
View Related
Jan 10, 2008
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!
View 5 Replies
View Related
Apr 3, 2008
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
View 3 Replies
View Related
Jun 14, 2008
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
View 2 Replies
View Related
Dec 1, 2005
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?
View 1 Replies
View Related
Dec 3, 2004
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
View 5 Replies
View Related
Apr 28, 2008
Hi All,
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.
Any help or advice would be greatly appreciated.
Cheers,
Jon
View 11 Replies
View Related
Sep 1, 2005
Hello friends!
I want to know weather we can insert more than 1 records from a single INSERT query in query analyzer?
If yes please tell me how to..
Thankyou.
View 1 Replies
View Related
Sep 20, 2005
use Dacari
go
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
CREATE PROCEDURE dbo.InsITRRComments
@CommentId int,
@DacType varchar(25),
@DacTypeId int,
@TypeOfComment varchar(50),
@Comments varchar(4000),
@RiskId_External_ID varchar(16),
@UserId varchar(25),
@FullUserName varchar(50),
@AmendedDateTime datetime,
@ProcessedFlag char(1)
AS
BEGIN
/*************************************************************************
*
* Name: InsITRRComments
*
* Description: Insert new Term Type item.
*
* Inputs:
* @CommentId
* @DacType
* @DacTypeId
* @TypeOfComment
* @Comments
* @RiskId_External_ID
* @UserId
* @FullUserName
* @AmendedDateTime
* @ProcessedFlag
*
* Outputs: None
*
*
* Notes:
*
* History:
* Date Author Changes
* ------------------------ -------------------------------------------
* 16 Sept 05xxxxxxxxxxxxxx Original
*************************************************************************/
---------------------------------------------------------------------------
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
View 9 Replies
View Related
Feb 8, 2006
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.
View 15 Replies
View Related
Nov 11, 2006
Hello
I have a table consisting of Date,Particulars,Debit,Credit columns.I executed the following query to insert the values.
insert into balancesheet(Particulars,Credit) VALUES('Salary',4917.00)
Iam getting an error
String or binary data would be truncated.
The statement has been terminated.
Plz help me out
Thanks in advance
Poornima
View 4 Replies
View Related
Dec 21, 2006
i am running a pretty crazy query with a couple on inner joins
lets say it returns 20 results that i then want to insert into a different table
how do i do this -- is there a way to do it with out cursors?
View 15 Replies
View Related
Feb 26, 2007
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?"
View 1 Replies
View Related
Mar 23, 2007
I need three insert query into only one single query at a time .It's possible...
View 13 Replies
View Related
Apr 18, 2007
Hi
I want to create a temporary table from a select statement
but sql server 2005 give error - invalid object name?
like:
INSERT INTO #TempTable (SELECT f.code,c.name FROM FORMNAMEMASTER f inner join formname c on f.code=c.code)
Pls guide how to do this...
View 1 Replies
View Related
Dec 10, 2007
Hi i have a stored procedure, i need to find out if a record exsists then if not then insert so i have this code, just not sure of the syntax.
/****** MATCH UP ADDRESS ID TO THE F4 RENTAL ******/
SELECT COUNT(R_Web_Add_ID)AS NewR_Web_Add_ID
FROM TBL_RENTAL
WHERE R_Web_Add_ID = @R_Add_ID
/****** IF MATCH IS LT 1 THEN INSERT ADDRESS INTO RENTAL ******/
if NewR_Web_Add_ID < 1 then
insert statement here
end if
View 7 Replies
View Related
Dec 20, 2007
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.
View 20 Replies
View Related
Jul 23, 2005
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
View 5 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
May 17, 2006
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.
Thanks.
View 4 Replies
View Related
Jun 16, 2007
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.
View 7 Replies
View Related
Oct 8, 2007
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
View 5 Replies
View Related
Oct 10, 2007
Hello! I have this stored procedure:ALTER PROCEDURE [dbo].[spAddToFavorites]
@Userid uniqueidentifier OUTPUT,
@KeyID uniqueidentifier
ASINSERT INTO Associations (Userid,BusinessID) values (@Userid, (SELECT Userid from UserProfile WHERE KeyID= @KeyID))
RETURN
But it says that I can't do it like that. I need to select that userid based on the keyid I am gonna pass in. Any help on this appreciated.
View 4 Replies
View Related
Nov 15, 2007
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
View 2 Replies
View Related
Nov 15, 2007
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,
@intro varchar(max) = null
AS
if @title is null
begin
print 'Title Cannot Be Null'
return 0
end
if @intro is null
begin
print 'Intro Cannot Be Null'
return -1
end
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Issue
(Title, Intro)
VALUES
(@title, @intro)
return SCOPE_IDENTITY()
View 3 Replies
View Related
Apr 22, 2008
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
View 1 Replies
View Related
Jun 4, 2008
As i insert data in to table it doesn't give any error messages,
but when i check the table it doesn't reflect the new data or rows inserted. ????
View 6 Replies
View Related
Oct 13, 2004
Hi there !
I have a table called customer which got
id = int
name = varchar
address = varchar
email= varchar
can you please write the syntax that insert the below data in the table using query Analyzer
id = 1
name = fadil
Address = London
email = fadil1977@hotmail.com
Thank you for your time and help.
View 1 Replies
View Related
May 8, 2006
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?
View 9 Replies
View Related
