SQL Server 2012 :: Extract Old Record Value

Jun 3, 2014

I have a 2table like

create table test1
(col1 identity(1,1),
col2 varchar(20)
)

create table test2
(old int,
new int)

Existing record in test1 table
(1,'test')

Now I want to insert a new record into test1 and then capture the old and new identity columsn in test 2 table

i.e. after insert test1 table will be
1,test
2,test

and test2 table will be like
1,2 -- old and new identity columns are captured during insert and stored in test2 table.

If the repeat the process again test2 table will be like
1, test
2,test
3, test

and test2 table will be
2,3 --- test2 table will always have only one record. old records will be deleted.

Using output clause i can get the new identity value.. but how can i get the old identity value and insert that into test2 table..

I am not willing to do a self join after the record is inserted into test2. I wanted to get the old ident value during insert of new ident value.

View 2 Replies


ADVERTISEMENT

SSIS: Multi-Record File Extract With 9 Record Types

Feb 26, 2008

I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx

I have been able to create a sample file with two of my record types.

I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.

does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?

Thanks in advance for any help you might be able to provide.


View 3 Replies View Related

SQL Server 2012 :: Extract Text From Numbers

Oct 20, 2014

I have a field which contains something like prj(5616) .

I have been assigned to display the actual name and not the text with the number.

Example: if prj(8616) is called Soccer , then I want display Soccer instead of prj(8616).

View 9 Replies View Related

SQL Server 2012 :: Extract Ispac File From SSISDB?

Jul 14, 2014

I need to extract the .ispac file from the SSISDB. I can retrieve the stream with catalog.get_project sp. However, the file I end up with cannot be unzipped, giving an error message. My guess is that it is meta-data on the zip/ispac file that has a problem, because I can actually unzip it with Winrar, but not with any of those I (programmatically) need to unzip it with.

Below is the code for my stored procedure - My own suspicion is in the BCP usage to turn the stream into a file.

USE [SSISDB]
GO
ALTER PROCEDURE [dbo].[spGetIspacFile]
@project VARCHAR(255) ,
@environmentFolder VARCHAR(50) ,
@ispacTempFolder VARCHAR(100) ,
@ispacFilePath VARCHAR(200) OUTPUT

[code]....

View 5 Replies View Related

SQL Server 2012 :: Query If Extract Failed And / Or Succeed The Same Day

Nov 19, 2014

We extract 10k tables every night and I have a table that keeps track of ETL tables that fail or succeed. I would like to know if a table fails during the night and nobody kicks off another job to fix it during the day.

The table structure looks like this:

| Table_Name | Time_Start | Status | Duration | Time_End |

Table_Name = varchar(20)
Time_Start = DateTime
Status varchar(7) = Success or Error
Duration = Number
Time_End = DateTime

Select Table_Name into #MyTempTable
From ETL.STATS_Table
Where Status = 'Error'
AND Cast(Time_Start as Date) = GetDate()

How do I take the table names from #MyTempTable and find out if they where successful for the same date? Duration time and Time_End fields aren't needed.

View 5 Replies View Related

SQL Server 2012 :: How To Extract One More Piece Of Data From The Column

Dec 17, 2014

I have a database table that was designed by someone else, and I'm trying to see if I can normalize the pattern. Here's the dummy table:

CREATE TABLE [dbo].[BadTox](
[PatientID] [int] NULL,
[Cycle] [tinyint] NULL,
[ALOPECIA] [tinyint] NULL,
[Causality1] [tinyint] NULL,

[Code] ....

All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:

CREATE TABLE Symptom (
PatientID INT NOT NULL,
Cycle TINYINT NOT NULL,
SymptomName VARCHAR(20) NOT NULL, -- from the source column *name*
Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase
PRIMARY KEY (PatientID, Cycle, SymptomName));

I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...

I can retrieve all the column names that are in uppercase using this:

DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'SCRIDB');
SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox');
SELECT name AS column_name
, column_id AS col_order
FROM sys.all_columns
WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS
AND object_id = @object_id;

but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):

ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply]
AS
SELECT PatientID
, Toxicity
, MAX(Grade) AS MaxGrade

[code]....

The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.

So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?

View 5 Replies View Related

SQL Server 2012 :: Extract Locking Information In Database

Jul 16, 2015

I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?

SELECT DISTINCT
ES.login_name AS LoginName,
L.request_session_id AS BlockedBy_SPID,
DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec,
DB_NAME(L.resource_database_id) AS DatabaseName,

[Code] ....

View 3 Replies View Related

SQL Server 2012 :: Derived Table To Extract Value Of Account For Comparison

Nov 2, 2014

In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)

This is my script:

SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)

[Code] ....

My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.

View 7 Replies View Related

SQL Server 2012 :: Patindex Function - Cannot Retrieve Or Extract Single Numeric Value

Jul 17, 2015

I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.

"2 8 7"
"2 8"
"2"
"22"
"3 2 8"

I have tried the following

Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,

Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,

Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),

View 5 Replies View Related

How To Extract Only 1 Record For Each Staff?

Oct 30, 2005

Hi,
I have more than 1 record for each staff. This is the sample of record,

StaffNo Field1 Field2 Field3
50424 Data1 Data2 Data3
80336 Data1 Data2 Data3
50424 Data4 Data5 Data6
50424 Data7 Data8 Data9
80336 Data4 Data5 Data6

Is it possible for me to extract only 1 record for each staff? The result should be like this..

StaffNo Field1 Field2 Field3
50424 Data1 Data2 Data3
80336 Data1 Data2 Data3

Any idea? I used DISTINCT in sql statement but it's not working because the record is unique.
Thanx in advance..

View 1 Replies View Related

How To Extract A Particular Record From A Table

Apr 7, 2008

View 3 Replies View Related

How To Extract Duplicate Record Using Ssis

Nov 16, 2006



ex. from source the file you want to split the record into two, the one with a clean record and the other one with duplicate records

View 3 Replies View Related

SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables

Oct 7, 2015

I am using the following select statement to get the row count from SQL linked server table.

SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')

MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?

View 1 Replies View Related

SQL Server 2012 :: Update Record In Table?

Nov 7, 2014

To see where is the problem I am trying to count rows in the database.First I create a table A with 2 columns namely tablename, rowbefore and rowafter and I insert records in it as below.

INSERT INTO A
SELECT TableName = o.name, '', Rows = max(i.rows) FROM sysobjects o
INNER JOIN sysindexes i ON o.id = i.id
WHERE xtype = 'u' AND OBJECTPROPERTY(o.id,N'IsUserTable') = 1
GROUP BY o.name
ORDER BY o.name
Then I update rowbefore with rowafter as below.
UPDATE A SET rowbefore = rowafter

Now I launch my application with update records in the database.Then I am trying to update rowafter with new records as below.

UPDATE A
SET rowafter = (SELECT max(sysindexes.rows) FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE xtype = 'u' AND OBJECTPROPERTY(sysobjects.id,N'IsUserTable') = 1 AND A.tablename = sysobjects.name)

Does this update really update my column rowafter or not?

View 2 Replies View Related

SQL Server 2012 :: Get One Record Where StartDates Are Identical

Feb 10, 2015

CREATE TABLE TEMPS(MEMBERNBR VARCHAR(11), STARTDATE DATETIME, ENDDATE DATETIME)

INSERT INTO TEMPS(MEMBERNBR, STARTDATE, ENDDATE) VALUES('12345678901', '01/01/2015', '01'01'2015')

INSERT INTO TEMPS(MEMBERNBR, STARTDATE, ENDDATE) VALUES('12345678901', '01/01/2015', '12'31'2099')

I need to retrieve only the record where the STARTDATE is '01/01/2015' and the ENDDATE is '12/31/2099'

View 2 Replies View Related

SQL 2012 :: BCP Extract Files Error

Feb 24, 2015

I run this code on several environments successfully but when on run it on a different environment it fails.

Its very strange as the user has database SA and OS administrator permissions.

Code...

declare @Command varchar (250)
set @Command = 'bcp "SELECT * FROM QAProcess.dbo.ReadMe_Table" queryout "THOMSONS-SQL01TempReadMe.txt" -c -T'
EXEC master..xp_cmdshell @Command

Error....

SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'T-BXSQLServerAccount'.
NULL

View 2 Replies View Related

SQL 2012 :: Extract Data From Given Formula

Jun 30, 2015

I need to extract the information from the following Formula:

prj(10517)(a*w/100)+prj(10742)(a*w/100)+prj(10804)(a*w/100)+prj(10808)(a*w/100)+prj(10809)(a*w/100)+prj(10810)(a*w/100)

What would be the simplest and fastest way to do this...

I did write a fetch statement but i want to do something simpler as it is a bit hectic...

Basically i require the information from prj(10517) etc.

View 2 Replies View Related

SQL Server 2012 :: Selecting Unique Record From A Table

Feb 10, 2014

I have a table (Billing Table) having multiple records for people having one record per person per each month.

How to get a list of the guys having record just for one month (say feb) and doesn't have any other months.

View 4 Replies View Related

SQL Server 2012 :: Record Count Of Column Field

Oct 30, 2014

I have created table in which there are four columns (id, date, parcelname, parcelnumber) and 10 rows. I want to count record of the column parcelnumber but condition that, in between two different dates the record should be counted.

View 9 Replies View Related

SQL Server 2012 :: Joining Three Tables With Multiple Record?

Dec 9, 2014

We have Three Tables in sqlserver2012

Master Table

OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz

Child1 Table

OrderID ControlName
1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table))
1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table))
1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table))
2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table))
2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))

Child2 Table

PackageID Product Color1 Color2 Color3
1 Pant Red Green Blue
1 Shirt Blue Pink Purple
1 Gown Blue Black Yellow
1 T Shirt Red Green White
2 Tie Red Green White
2 Socks Red Green White
2 Bow Red Green White

We want to have result like

OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow

1 1 ABC Red Blue White x x
Blue
2 2 Bcd x x x Green Red

I have tried

;with mycte as (
select ms.OrderID,ms.PackageID
,ms.CustomerName
, Replace(stuff([ControlName], charindex('Column',ControlName),len(ControlName),''),'Row','') rowNum
,Replace(stuff([ControlName], 1, charindex('Column',ControlName)-1 ,''),'Column','') columnNum
From child1 c inner join MasterTable ms on c.Orderid=ms.orderid)

[code]....

it works if we have a product in one color only. like if we have pant in red and blue then its showing just first record

View 2 Replies View Related

SQL Server 2012 :: Find Record(s) That Exceed Budget Value

Mar 3, 2015

I have the following tables:

--acts as a transaction table
CREATE TABLE #TestData (
id int not null identity(1,1) primary key,
account varchar(10) not null,
deposit int not null

[Code] ....

--desired results
-- within each account group, when a individual record causes the groups running total to exceed the group's budget, show the difference that causes the groups running total to exceed the budget as unbudgeted. The amount of that transaction upto the budget amount show as renewal. For any succeeding individual records in the group, the amount of that transaction is Unbudgeted.

Here's the SQL I have thus far...

SELECT a.id
,a.account
,a.deposit
,a.total
,a.budget
,case when a.total <= a.budget

[Code] ....

View 4 Replies View Related

SQL Server 2012 :: Trigger For Updates On A Row Using Previous Record Value?

Mar 9, 2015

I am looking to update a record from a previous row. So if there is a value of total goods in week 1, i want that value to carry forward to the value of goods in week 2. Is there any SQL as an example of the best way to accomplish this? I can query it using lag() which works great but i need the source data itself to update as the end-users are accessing the data via lightswitch, so when they save a change, i want the trigger (or whatever you recommend) to update the source table.

View 9 Replies View Related

SQL Server 2012 :: Query For Single Entry Record Only?

May 26, 2015

I have records like below, single query to get result below, basically records that has single entry only, which has type '0'

table : temp_test
idtype
c10
c25
c30
c40
c47
c59
c64
c60
c77
c80
c90

Result out of query

idtype
c10
c30
c80
c90

View 4 Replies View Related

SQL Server 2012 :: Check To See If Columns Are NOT NULL In A Record

Jun 25, 2015

I am looking for the best way to check to see if any columns are still NULL in a record. I have a form that gets filled out by users and the values entered into TableA. There are 6 columns in the table, 5 are responses and column6 indicates if the record is complete. So I want a way to see if all of the first 5 columns are NOT NULL and, if so, mark column6 with a 1.

I am thinking this would be a good thing for a trigger on INSERT or UPDATE to check to see if the first 5 columns are filled in and then mark the record as complete.

View 5 Replies View Related

SQL Server 2012 :: How To Hide Specific Record(s) From Results

Nov 2, 2015

I have written a SQL statement.There is a table called customer.It contains all customer data with customerid as PK.There is another table called logs and it contains customerid as foreign key and it contains a field to keep more than 90 days older user accounts.That field name is "Checked"

What I need get all records from these 2 tables and remove/hide more than 90 days older customers from record set.See my illustration.

I have written this code but I dont understand how to remove more than 90 days older user from result (because customer table doesnt contain a record called "Checked")

SELECT *
FROM [dbo].[Customers],[dbo].[VIESLog] WHERE [dbo].[VIESLog].[Checked] < DATEADD(day, -90, GETDATE())

View 6 Replies View Related

SQL Server 2012 :: Multiple Records Queried Into Single Record?

Jan 27, 2014

I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.

People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801

[code]....

Results

PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom

View 8 Replies View Related

SQL Server 2012 :: How To Remove Next And Previous Record Amount Equal To 0

Jun 2, 2014

Having below data. SHCOMP (Customer ID), CBLNAM ( Customer name), SHDESC (service description), SHAMT (service charge), SHTYPE (F-> From T-> To)

CREATE TABLE #Temp(
[SHCOMP] [numeric](7, 0) NOT NULL,
[SHDESC] [char](35) NOT NULL,
[SHTYPE] [char](1) NOT NULL,
[SHAMT] [numeric](9, 2) NOT NULL,

[Code] ....

I need to remove records that have the same ID, Name, Description and Amount summary = 0, For example

SHCOMP CBLNAM SHDESC SHAMT SHTYPE

123 cust1 desc1 45 F remove
123 cust1 desc1 -45 T remove
123 cust1 desc1 45 F remove
123 cust1 desc1 -45 T remove
123 cust1 desc1 45 F

[Code] ....

Results

SHCOMP CBLNAM SHDESC SHAMT SHTYPE

123 cust1 desc1 45 F

123 cust1 desc1 -35 T

234 cust3 desc2 30 F

here is what I did but didn't work because sum is <> from 0 so is leaving all records

select SHCUST, SHDESC, ABS(SHAMT) SHAMT
into #t1
FROM #Temp
group by SHCUST, SHDESC, ABS(SHAMT)
having SUM(SHAMT)=0
order by SHCUST

delete S from #Temp S
join #t1 T on T.SHCUST = S.SHCUST and T.SHDESC = S.SHDESC

View 5 Replies View Related

SQL Server 2012 :: Inserting Record In Table - Trigger Error

Aug 6, 2014

I am inserting a record in XYZ table(DB1). Through trigger it will update ABC table(DB2).

I am getting error when doing above thing. What are the roles to be set to user to avoid above problem.

View 3 Replies View Related

SQL Server 2012 :: Find Most Recent Record Of Consecutive Dates

Feb 23, 2015

I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.

I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:

FromDate ToDate Customer Number Contract Number
02/01/2015 07/31/2015 2555 456
01/15/2015 01/31/2015 2555 456
04/01/2013 09/30/2015 2555 123
03/13/2013 03/31/2013 2555 123

From this table, I would like a query that would give me this result:

01/15/2015 07/31/2015 2555 456
03/13/2013 09/30/2015 2555 123

There will likely be more than just 2 consecutive records per contract number.

View 4 Replies View Related

SQL Server 2012 :: Ways To Improve Record Deletion Speed

Oct 13, 2015

I have a table (F_POLICY_TRANSACTION).This table has a couple of million rows in it.I am using a column named POLICY_TRANSACTION_BKEY to select records to delete (approximately 750k using the code below)This column has a non-clustered index applied..This is the code I have used:

WHILE 1 = 1
BEGIN
DELETE TOP(50000)
FROM F_POLICY_TRANSACTION with (tablockx)

[code]....

Problem is, it takes around 10 minutes to run.Is there any way it can be made more efficient?I have tried varying the rowcount with no success

View 9 Replies View Related

SQL Server 2012 :: Finding Duplicates And Show Original / Duplicate Record

Nov 3, 2014

There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.

How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.

SELECT
[groom_first_name]
,[groom_last_name]
,[bride_first_name]
,[bride_last_name]

[code]....

View 5 Replies View Related

SQL Server 2012 :: How To Return User-defined Row When A Record Doesn't Exists

Dec 29, 2014

What I want to do is return a row of data when my query doesn't return a record. I have two tables:

CREATE TABLE dbo.abc(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
) ON PRIMARY

[Code] ....

So when I run the following query:

SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
UNION
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
abc-1-200.00
abc-1-500.00

As you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as

abc-1-200.00
abc-1-500.00
def-0-0.00

View 4 Replies View Related

SQL Server 2012 :: How To Join Tables To Get Only Record With Specific Field Value In A Table

Feb 6, 2015

I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.

There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).

All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”

I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.

That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved