How Do I Use Outout Inserted With A Insert Into Query?
May 20, 2008I cannot find a query that works on google. I want to insert a record and get the identity key afterwards.
thanks
ADVERTISEMENT
INSERT INTO - Data Is Not Inserted
Jul 20, 2005hi thereCreated sproc - it stops dead in the first lineWhy ????Thanks in advanceCREATE PROCEDURE [dbo].[test] ASinsert into timesheet.dbo.table1 (RE_Code, PR_Code, AC_Code, WE_Date,SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,WWL_Number, CN_Number)SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_NumberFROM dbo.WWL_TimeSheetsWHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIME,'1999-12-03 00:00:00', 102))GO
View 6 Replies View RelatedInsert Row, Then Update Using Inserted Identity Issues..
Oct 25, 2007hey everyone, I have the following SQL:
CREATE PROCEDURE [dbo].[sp_InsertItem]
@item_channel_id INT, @item_title VARCHAR(75), @item_link VARCHAR(75),
@item_description VARCHAR(150), @item_long_description VARCHAR(1000),
@item_date DATETIME, @item_type VARCHAR(20)
AS
IF (@item_type = 'article')
BEGIN
INSERT INTO items (
item_channel_id, item_title, item_link, item_description, item_long_description,
item_date, item_type
) VALUES (
@item_channel_id, @item_title, @item_link, @item_description, @item_long_description,
@item_date, @item_type
)
END
IF (@item_type = 'mediaItem')
BEGIN
DECLARE @new_identity INT
DECLARE @new_link VARCHAR(100)
INSERT INTO items (
item_channel_id, item_title, item_link, item_description, item_long_description,
item_date, item_type
) VALUES (
@item_channel_id, @item_title, @item_link, @item_description, @item_long_description,
@item_date, @item_type
)
SET @new_identity = @@IDENTITY
SET @new_link = @item_link + @new_identity
UPDATE items
SET item_link = @new_link
WHERE item_id = @new_identity
END
GO
Basically, what I am trying to do is this...
IF the item type is article, insert normally... which works fine...
however, if the item time is mediaItem, insert part of the item_link... (everything minus id.. eg: site.com/items.aspx?item_id=)... then once the row has been inserted, update that row, to make the link site.com/items.aspx?item_id=<new id>
however, when the sql runs the mediaItem code, it leaves the item_link field blank.
Why is this doing this?
Thanks all!
Generate Insert Script Of Last Inserted Record
Apr 24, 2015How to generate insert script of last inserted record in SQL SERVER Table???.. I want use this code for log entry purpose..
View 1 Replies View RelatedDuplicate Records Are Being Inserted With One Insert Command.
Jul 20, 2005This is like the bug from hell. It is kind of hard to explain, soplease bear with me.Background Info: SQL Server 7.0, on an NT box, Active Server pageswith Javascript, using ADO objects.I'm inserting simple records into a table. But one insert command isplacing 2 or 3 records into the table. The 'extra' records, have thesame data as the previous insert incident, (except for the timestamp).Here is an example. Follow the values of the 'Search String' field:I inserted one record at a time, in the following order (And only oneinsert per item):airplanejetdogcatmousetigerAfter this, I should have had 6 records in the table. But, I endedup with 11!Here is what was recorded in the database:Vid DateTime Type ProductName SearchString NumResultscgcgGeorgeWeb3 Fri Sep 26 09:48:26 PDT 2003 i null airplane 112cgcgGeorgeWeb3 Fri Sep 26 09:49:37 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null cat 75cgcgGeorgeWeb3 Fri Sep 26 09:52:53 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null tiger 14cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64Look at the timestamps, and notice which ones are the same.I did one insert for 'dog' , but notice how 2 'jet' records wereinsertedat the same time. Then, when I inserted the 'cat' record, another'dog' record was inserted. I waited awhile, and inserted mouse, andonly the mouse was inserted. But soon after, I inserted 'tiger', and 2more mouse records were inserted.If I wait awhile between inserts, then no extra records are inserted.( Notice 'airplane', and the first 'mouse' entries. ) But if I insertrecords right after one another, then the second record insertion alsoinserts a record with data from the 1st insertion.Here is the complete function, in Javascript (The main code ofinterestmay start at the Query = "INSERT ... statement):----------------------------------------------------------------------//Write SearchTrack Record ------------------------------------Search.prototype.writeSearchTrackRec = function(){Response.Write ("<br>Calling function writeSearchTrack "); // fordebugvar Query;var vid;var type = "i"; // Type is imagevar Q = "', '";var datetime = "GETDATE()";//Get the Vid// First - try to get from the outVid var of Cookieinctry{vid = outVid;}catch(e){vid = Request.Cookies("CGIVid"); // Gets cookie id valuevid = ""+vid;if (vid == 'undefined' || vid == ""){vid = "ImageSearchNoVid";}}try{Query = "INSERT SearchTrack (Vid, Type, SearchString, DateTime,NumResults) ";Query += "VALUES ('"+vid+Q+type+Q+this.searchString+"',"+datetime+","+this.numResults+ ")";this.cmd.CommandText = Query;this.cmd.Execute();}catch(e){writeGenericErrLog("Insert SearchTrack failed", "Vid: "+vid+"- SearchString:: "+this.searchString+" - NumResults: "+this.numResults, e.description);}}//end-----------------------------------------------------------------I also wrote a non-object oriented function, and created the commandobject inside the function. But I had the same results.I know that the function is not getting called multiple timesbecause I print out a message each time it is called.This really stumps me. I'll really appreciate any help you canoffer.Thanks,George
View 2 Replies View RelatedHow To Make Sure In An Insert Statement That The Vendor Being Inserted Does Not Exist?
Dec 3, 2006I am using INSERT into Vendors (.....) VALUES (....) form of insert statement. How can I make sure that the insert fails if thie new vendor's vendorname exists?
I cannot create a unique index on 'VendorName' column since it is more than 900 bytes and SQL Server will not allow to create index on a column bigger than 900 bytes.
Can INSERT Statement Also Return All Columns Inserted As Result Set
Jun 2, 2014Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?
View 3 Replies View RelatedGet The Id Of Inserted Rows --&&> Insert Into Table1 Select * From Table 2 ?
Apr 17, 2008
hi
I want to do a "bulk" insert and then get the id (primary key) of the inseret rows:
insert into table1 select * from table2
If I get the value of the @@identity, I always get the last inserted record.
Any idea how to get the ids of all inserted values?
Thx
Olivier
How To Excute Stored Procedure That Insert Record And Return Last Inserted Value
Jul 18, 2007Dear all,
I am using C# , asp.net and sql server 2005.
Let me explain the situation.
I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can
Get last inserted variable values
Please help
thanks
SQL 2012 :: Can INSERT Statement Also Return All Columns Inserted As Result Set
Jun 2, 2014Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?
View 2 Replies View RelatedSQL Server 2012 :: Trigger Inserted Multiple Rows After Insert
Aug 6, 2014I create a Trigger that allows to create news row on other table.
ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX]
ON [dbo].[_IMPORT_FILES_CONTACTS]
AFTER INSERT
AS
[code]...
But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :
C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC001 - ACCOUNT 001
C003 - AD003 - AC001 - ACCOUNT 001
C004 - AD004 - AC001 - ACCOUNT 001
C005 - AD005 - AC001 - ACCOUNT 001
I search a means to have 50 lines differently on my CONTACT table.
C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC002 - ACCOUNT 002
C003 - AD003 - AC003 - ACCOUNT 003
C004 - AD004 - AC004 - ACCOUNT 004
C005 - AD005 - AC005 - ACCOUNT 005
SQL Server 2012 :: How To Get A Table Identity Inserted By Instead Of Insert Trigger
May 14, 2015I have a problem described as follows: I have a table with one instead of insert trigger:
create table TMessage (ID int identity(1,1), dscp varchar(50))
GO
Alter trigger tr_tmessage on tmessage
instead of insert
as
--Set NoCount On
insert into tmessage
[code]....
When I execute P1 it returns 0 for Id field of @T1.
How can I get the Identity in this case?
PS: I can not use Ident_Current or @@identity as the table insertion hit is very high and can be done concurrently.Also there are some more insertion into different tables in the trigger code, so can not use @@identity either.
SQL Server 2008 :: Trigger Fire On Each Inserted Row To Insert Same Record Into Remote Table
Sep 9, 2015I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET
i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.
The problem is when i call the stored procedure from trigger, i get an error message.
Stored Procedure:
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When i try to insert a new description value in the table i got the following error message:
No row was updated
the data in row 1 was not committed
Error source .Net SqlClient Data provider.
Error Message: the operation could not be performed because OLE DB
provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
correct the errors entry or press ESC to cancel the change(s).
How To Solve Tables Or Functions 'inserted' And 'inserted' Have The Same Exposed Names.
Jul 23, 2005Hi all!In a insert-trigger I have two joins on the table named inserted.Obviously this construction gives a name collition beetween the twojoins (since both joins starts from the same table)Ofcourse I thougt the usingbla JOIN bla ON bla bla bla AS a_different_name would work, but itdoes not. Is there a nice solution to this problem?Any help appriciated
View 1 Replies View RelatedGetting Identity Of Inserted Record Using Inserted Event????
Jun 5, 2006is there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
View 1 Replies View RelatedQuery For Find Data Last Inserted In A Table
Apr 9, 2015I need to find the history in SQl server.
I need to find the data, When was data last inserted/updated/deleted in a specific table?. is it possible?
Table name: Employee
INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table
Jul 20, 2005Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO
View 2 Replies View RelatedWants 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
Can I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net
Mar 1, 2007Can 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
OUTPUT INSERTED INTO Can't Get Column Value When That Column Is Not Inserted
Aug 22, 2013I'm doing a data migration job from our old to our new database. The idea is that we don't want to clutter our new tables with the id's of the old tables, so we are not going to add a column "old_system_id" in each and every table that needs to be migrated.
I have created a number of tables in a separate schema "dm" (for Data Migration) that will store the link between the old database table and the new database table. Suppose that the id of a migrated record in the old database is 'XRP002-89' and during the insert into the new table the IDENTITY column id gets the value 1, the link table will hold : old_d = 'XRP002-89', new_id = 1, and so on.
I knew I can get the value of IDENTITY columns with the OUTPUT INTO clause, although I have never actually used it. And now I can't get it to do what I need.
Below is some code to set up three tables: the old table, the new one, and the table that will hold the link between the id's of records in the old database table and the new database table.
Code:
CREATE TABLE DaOldTable(
pkCHAR(10)NOT NULL,
a_columnCHAR(10)
)
CREATE TABLE DaNewTable(
idINTNOT NULL IDENTITY,
[Code] ....
Below I tried to use the OUTPUT INSERTED INTO clause. Beside getting the generated IDENTITY value, I also need to capture the value of the old id that will not be migrated to the new table. When I use "OUTPUT DaOldTable.pk" the system gives me the error: "The multi-part identifier "DaOldTable.pk" could not be bound." Using INSERTED .id gives no problem.
Code:
INSERT INTO DaNewTable(a_column)
--OUTPUT DaOldTable.pk, INSERTED.id link_old2new_DaTable--(DaOld_id, DaNew_id)
SELECT a_column
FROM DaOldTable
[Code] ...
--but getting "The multi-part identifier "DaOldTable.pk" could not be bound."
DROP TABLE DaOldTable
DROP TABLE DaNewTable
DROP TABLE link_old2new_DaTable
How can I populate a table that must hold the link between the id's of records in the old database table and the new database table? The records are migrated with set-based inserts.
Help With Insert SQL Query
Jan 21, 2007i 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 RelatedInsert Query ?
Mar 15, 2007my 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 ....
INSERT INTO Query Help...
Jan 10, 2008Hi 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!
Insert Query
Apr 3, 2008can 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 RelatedSQL Insert Query
Jun 14, 2008Declare @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 RelatedIs This 'Insert Into' Query Possible?
Dec 1, 2005I 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?
Insert From Query
Dec 3, 2004Hi 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
Insert Query
Apr 28, 2008Hi 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
Best Way To Right This Insert Query
May 1, 2008Hi. 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.
INSERT QUERY
Sep 1, 2005Hello 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.
Sub Query Insert
Sep 20, 2005use 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
Insert From Query
Feb 8, 2006How 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 RelatedInsert Query
Nov 11, 2006Hello
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