Transact SQL :: Insert Records And Write Back Identity Value
Jun 19, 2015
I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.
So for example I want to send
BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.
No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions.
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
My Requirement is Update Table 1 set Column::No=Table 2.ID
based on Exact Match of
Table1.Name=Table2.Name and
Table1.Add=Table2.Add
It means Get back the Id for Source Table 1
2nd Data flow Source(Table1:Name, Add,No) |
--LOOKUP(Table2:Name, Add::Matched Look Columns Name, Add and Tick Mark on ID) |(Match)
-->OLEDB Command: update Table1 set N0=? where RowID=?(Here Param_0= NO ,Param_1=RowID)
Here My Issue is if Table 1 had Duplicates(same Name, Add, but Row Id is different it is Updating Same ID for Table 1.No It means Get Back ID correctly not updating Result::
Table 1:
------- ----- ---- ---- Name Add No RowID ------- ----- ---- ------- aa #a-1,India 1 10 bb #a-1,India 2 11 aa #a-1,India 1 12
I have a table with 1 million records. I want to update only 400 records. The update statement is provided by a 3rd party vendor. Once i run the update statement it will update all the 400 records. Once the table is updated the users will validate the table
if the update is successful or not. What i'm looking for is:
1) Is there a way to identify what records were updated. 2) If the update done is not what the users wanted i need to undo and bring back the 400 records to their previous values.
I am writing an Instead of Insert trigger. I would like to fire an error when inserting into an 'Identity' column. Since UPDATE([ColumnName]) always returns TRUE for insert statements, is there an easy/fast way around this? I don't want to use:
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NULL)) here is my pseudo-code... CREATE VIEW [org].[Assets] WITH SCHEMABINDING
[Code] .....
-- How does this statement need to be written to throw the error? --UPDATE([AS_ID]) always returns TRUE
IF(UPDATE([AS_ID])) RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Is there a faster/better method than this? IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NOT NULL)) RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Do Stuff END;
-- Should error for inserting into [AS_ID] field (which is an identity field) INSERT INTO [org].[Assets]([AS_ID], [Tag], [Name]) VALUES(1, 'f451', 'Paper burns'), (2, 'k505.928', 'Paper burns in Chemistry');
-- No error should occur INSERT INTO [org].[Assets]([Tag], [Name]) VALUES('f451', 'Paper burns'), ('k505.928', 'Paper burns in Chemistry');
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"
Declare @StartDt date = '2015-03-15' Declare @EndDt date = DATEADD(M, 1, @StartDt) declare @Days int = DATEDIFF(d, @StartDt, @EndDt) declare @TBLSales as table(SaleDate date, Value money) DECLARE @Today date declare @TBLSalesCounts as table( StatusDesc varchar(100), Value money)
[Code] ....
I end up with the following result :
How would I alter my while loop to only insert the sum total of each day, instead of creating duplicates for each day.
E.g. 2015-04-22 1150.00 2015-04-21 785.00 2015-04-20 750.00
Hello everybody, I have a dataset that i read from an xml file. I need to import this database back in to sql.. Is there a easy way.. Or do i need to loop thorugh each record in the dataset to import it.
I have a cube with a partition configures in write back.
Users in Excel need to see the totals of the line. Unfornately they have the bad idea to write in this cell sometimes and not in the leaf cells.
As there is some MDX code behind in the weight expression field, we got some weird values : one is negative and others ARe 10 times the initial value in the total. So it's very dangerous.
How can we block the writing in this totals cells ?
Is there no way to pass identity info back through SqlDataSource? You can only do that with ADO.NET code? In other words, if I want to run a complex INSERT statement to a table that uses Identity, I can't take that key value back through something like SCOPE_IDENTITY() and use it? I know how to do this with ADO code, but I can't figure out how to do it purely with SqlDataSource. I was hoping to do this without having to write a new Insert statement -- just using the one that's already in the SqlDataSource control. But there doesn't seem to be any facility for Identity in there. I tried embedding a separate select statement after the insert statement and a semi-colon, but that didn't seem to do anything. Thanks!
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.
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
I'm written a query to pull adjustments made on customer accounts, and I am attempting to pull in the payment associated with the adjustments. I'm using a single account to test my query and somewhere in my final output select statement I'm doing something that is bringing me back more records than I want. The code I have written is:
IF OBJECT_ID('TEMPDB..#TMP1O') IS NOT NULL DROP TABLE #TMP1O
I need to return a computed value and have no idea how to do it.
I have a table of dates: Column DataType RowID int Person nvarchar(20) Percent real Date smalldatetime
What I need is the Annual Fiscal Year Average. The fiscal year starts and ends in June. I need it to add all Percent values between the months of June 2001 - June 2002, June 2002 - June 2003, and so on and on....
A result set like this...
Person - FiscalYear - FiscalYearResult John D - 2001 - .58 John D - 2002 - .52 John D - 2003 - .50 Jane D - 2001 - .58 Jane D - 2002 - .52 Jane D - 2003 - .50
so on and so on...
How do I write this in Transact-SQL to get this result set...
I have a table holding transaction data: money owed / money paid. The vast majority link together, however, a small % do not. I need to apply money paid to money owed. I want to give the client the benefit of the doubt, while using some logic in the application of funds.
I know when the transactions occurred, so I decided to work from newest to oldest, applying available funds from any money paid that is newer than the money owed. When the process completes the look back for an account, there may be money paid left over. This is okay, as it could apply to money owed transactions prior to the look back period.
I have code in place to accomplish this. My problem is the amount of time the process takes to run. At the current pace, it will take well over a day to complete. Here is an example of what the data may look like for one account:
I have 3 requests to do and all 3 are below in SQL , i am planning to keep them as job and run on regular intervals as per request ..
1.Poll the contract approval table for no prepared new contracts. Check every 30 minutes from 9am to 10pm – 7 days a week.
IF NOT EXISTS (SELECT * FROM ContractApproval WITH (NOLOCK) WHERE ContractPrePared>DATEADD(MINUTE, -30, GETDATE())) BEGIN SEND DBMAIL ---i have this part working already END
2.Poll the contract approval table for no new signed contracts. Check every 30 minutes from 9am to 10pm – 7 days a week.
IF NOT EXISTS (SELECT * FROM ContractApproval WITH (NOLOCK) WHERE DateSigned>DATEADD(MINUTE, -30, GETDATE()) BEGIN SEND DBMAIL ---i have this part working already END
3.Poll the lead table for no new leads. Check every 5 minutes. 24/7.
IF NOT EXISTS (SELECT * FROM Lead WITH ( NOLOCK ) WHERE DateCreated > DATEADD(MINUTE, -5, GETDATE())) BEGIN SEND DBMAIL ---i have this part working already END
I am planning to keep 1,2,3, in seperate sp's and run them as JOBS....how can i achieve below a,b ?
a.The procedures should accept a parameter that indicates the interval, in minutes, that they should look back b.Each step in 3 jobs should invoke the procedures, specifying the interval with which the job runs at
I have a table which table has :Identity Column (Identity), Schema name and Table_Name.
So I would like to write a cursor For each Table Count(*) from Table--@Cnt Int , Schemaname and Tablename need to store another table.
Implement a USP, using a cursor that scan the table, generate select count statement from configuration table and fire the select count statement and record the result of the query in the log table :
how can I write a cursor and Import Those results into to Another table.
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario: -------------- I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
What's the best way to write key values of records processed in my SSIS 2012 package to the log provider chosen?My SSIS package deactivates widgets as well as thingies. It was just released into production this week, runs daily, and we'd like to keep a close eye on what it's doing for a couple of weeks, by that I mean on any day be able to quickly see which thingies and widgets were deactivated that morning. It typically deactivates less than 5 widgets and thingies per day.
We could dig through the database to see which were deactivated, but that only works if somebody hasn't manually reactivated it since it was deactivated. We need a log. This is a temporary watch we're doing, so we don't want to write to a table or make make any significant package changes, such as adding new tasks.It seems like writing the 5-or-so deactivated thingy and widget key values to the log is the best way to watch this package. What's the most efficient way to do this? I'm hoping to avoid a new loop and script component with "Dts.Log" calls, but I don't know any other way.
