How To Compare Value Of Two Fileds And Based On That Insert Value Into Third Fileds
Jan 30, 2007
Hi,
I have a database with table name as test in that i have 6 colums
they are
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
please suggest query in sql which i can run to do this .
Regards
View 8 Replies
ADVERTISEMENT
Jun 7, 2004
How Can I Find Fileds type (for example Bit or nvarchar or ntext ,...) in Sql Server;
with how sql parametr or query ?
View 2 Replies
View Related
Sep 13, 2007
Hi Guys
I am developing some charts using 'Business Intelligence' in Sql server 2005. I need to include an additional 'page' filed in the chart the same way we do it in pivot table graphs in excel where there is a page field right on top which has a dropdown of options and you can select those options one by one to see the selected results...
Thanks
View 1 Replies
View Related
Jun 27, 2007
Hello,I currently have Table1 and View1.View1 is a query from 2 or 3 tables that works fine on its own.However in my current query if I try to use it...something like...SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3FROM View1 a JOIN Table1 b on a.col1 = b.col1WHERE a.col2 <b.col2 OR a.col3 <b.col3It throws an error "Server: Msg 446, Level 16, State 9, Line 1 Cannotresolve collation conflict for not equal to operation."Clearly I need to use collation between Table1 and View1, But I dontknow where I need to use "COLLATE SQL_Latin1_General_CP850_CI_AI" andhow? this is the collation set on Table1.Thank you!Yas
View 1 Replies
View Related
Sep 10, 2007
HiBelow is a table with sample data to explain what I want to achieve.trackID member markerallele1------- ------ -------------------------------------------------------------------------------------------------------4734 4577 01-D8S1179 13.54734 4577 02-D21S11 12.64734 4577 03-D7S820 2.04734 4577 04-CSF1PO 3.04734 4577 06-TH01 4.04734 4577 07-D13S317 5.04734 4577 08-D16S539 9.04734 4577 11-vWA 6.04734 4577 12-TPOX 7.04734 4577 13-D18S51 1.04734 4578 01-D8S1179 13.04734 4578 02-D21S11 12.64734 4578 03-D7S820 2.04734 4578 04-CSF1PO 4.04734 4578 06-TH01 3.04734 4578 07-D13S317 5.04734 4578 08-D16S539 9.04734 4578 11-vWA 7.04734 4578 12-TPOX 6.04734 4578 13-D18S51 1.0Okay I am trying to compare allele1 values for differences for two orthree different member having the same trackID. The list of markerswould always be same for all members belonging to a particulartrackID.So in the above sample data. The trackID is same which is 4734. Thereare two members 4577 and 4578. The markers are same for each membersbut the corresponding allele1 values are different.I want to compare these two and state whether they are same or not. Iwould like to do this on a form containing subforms.Feel free to ask for clarification.Thank you.
View 2 Replies
View Related
Mar 15, 2014
-- My first Data
create table #myfirst (id int, city varchar(20))
insert into #myfirst values (500,'Newyork')
insert into #myfirst values (100,'Ediosn')
insert into #myfirst values (200,'Atlanta')
insert into #myfirst values (300,'Greenwoods')
insert into #myfirst values (400,'Hitchcok')
insert into #myfirst values (700,'Walmart')
insert into #myfirst values (800,'Madida')
-- My Second Data
create table #mySecond (id int, city varchar(20),Sector varchar(2))
insert into #mySecond values (1500,'Newyork','MK')
insert into #mySecond values (5500,'Ediosn','HH')
insert into #mySecond values (5060,'The Atlanta','JK')
insert into #mySecond values (7500,'The Greenwoods','DF')
insert into #mySecond values (9500,'Metro','KK')
insert into #mySecond values (3300,'Kilapr','MK')
insert into #mySecond values (9500,'Metro','NH')
--Third Second Data
create table #myThird (id int, city varchar(20),Sector varchar(2))
insert into #myThird values (33,'Walmart','PP')
insert into #myThird values (20,'Ediosn','DD')
select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city
/*
idcitySector
500NewyorkMK
100EdiosnHH
*/
i have doubt on two things
1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second
2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found
--i tried below to solve first doubt, it is working but want to know any other wasys to do it
select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')
--Expected results wull be
create table #ExpectResults (id int, city varchar(20),Sector varchar(2))
insert into #ExpectResults values (200,'Atlanta','JK')
insert into #ExpectResults values (100,'Ediosn','HH')
insert into #ExpectResults values (300,'Greenwoods','DF')
insert into #ExpectResults values (500,'Newyork','MK')
insert into #ExpectResults values (700, 'Walmart','PP')
insert into #ExpectResults values (800, 'Madidar','')
[code]....
View 1 Replies
View Related
Apr 26, 2006
Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT vs INSERT? Performance speaking, which operations is generally faster given the same amout of data inserted.
Sincerely,
-Lawrence
View 3 Replies
View Related
Jan 21, 2007
Hi !
for MS SQL 2000
I need an UsersHistory Table where i cannot INSERT 2 rows one after the other with the same user.name
I can get
Bob
David
Bob
but not
Bob
Bob
David
something like an INDEX on rows
or
INSERT INTO UsersHistory (name) VALUES ('bob') IF MAX(name) <> 'bob' ?
what is the best way to do it ?
thank you
View 3 Replies
View Related
Apr 29, 2008
Hi everybody...
looking for a way to compare and insert data on two tables..
I have two tables
Tbl_email1
emailID email
1 info@sample1.com
2 info@sample2.com
3 info@sample3.com
tbl_email2
emailID email
1 info@sample1.com
2 info@sample4.com
3 info@sample5.com
I'm trying to compare tbl_email2 (email filed) with tbl_email1 (email field)
if the record exist it it does nothing if not it adds the email field in tbl_email1
the result would be
Tbl_email1
emailID email
1 info@sample1.com
2 info@sample2.com
3 info@sample3.com
2 info@sample4.com
3 info@sample5.com
thanks
View 4 Replies
View Related
Oct 15, 2014
I am having below two tables:
1) TableA : Which contains 5 columns(Column1,..........Column5)
2)TableB : Which contains 10 columns(Column1,..........Column10)
TableB contains millions of data.Now I want select all 5 columns from tableA but combination of Column1,Column2,Column3 if present in tableB, then i want exclude that records.I am doing as below:
select * from TableA a join TableB b a.column1!=b.column1 and a.column2!=b.column2 and a.column3!=b.column3 )
But query is taking almost 5 minutes. Is there is another approach?
View 4 Replies
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 1 Replies
View Related
Sep 21, 2015
I have three tables:
"PaymentsLog"
"DatePeriod"
"PaidOrders"
As per below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](
[Code] ....
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
View 3 Replies
View Related
Feb 15, 2007
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
-------------------------------------------------------
My solution is like this. Is it correct?
// CoCreateInstance(...);
// Data source
// Create session
m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;
DBPROP rgProps[1];
DBPROPSET PropSet[1];
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";
PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;
if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}
View 6 Replies
View Related
Nov 2, 2004
Hi everyone -
I have a simple query that will return a result set that
has a unique value in it (primary key) -
I would like to cycle through each of these values
returned from the query, and create a new row
in another table.
I do not want to use a cursor -
The first query is pretty simple....
select p_id from table1 where active=0
the query i need to execute for each of the p_id that
is returned in the first example is....
insert into table2 (p_id_field, text_field) values (@p_id, 'text')
the @p_id is filled in from the primary query...
please advise
thanks
tony
View 1 Replies
View Related
Jun 4, 2008
Good Afternoon,
I am new to MSSQL and am trying to write a complicated SQL statement that I'm having trouble with. Any help that anyone can offer is much appreciated!
Here is the problem I am tackling:
I have a list of about 5,000 members of our organization stored in the MemberList MSSQL table. I have a separate MSSQL table (CityList) that has approximately 500,000 resident of a city.
I am trying to find matches between MemberList and CityList for the purposes of figuring out which of our members are registered voters.
The tricky part of this problem, is that there is no unique ID (such as a social security number) that is present in each list. Accordingly, I have decided to created several types of matches:
1. NameDOBMatch: Where the FirstName, LastName & DOB fields in MemberList table match the same fields in CityList table.
2. NameAddressMatch: Where the LastName, FirstName & Address fields in the MemberList table match the same fields in the CityList table.
3. DoubleMatch: A combination of the first two matches (i.e. where the LastName, FirstName, DOB, & Address fields in the MemberList table match the same fields in the CityList table).
My goal is to "loop" through the MemberList and CityList tables and to add a new row to a third MSSQL table (MemberMatch) each time one of the aforementioned matches is found. The MemberMatch table has the following fields:
1. MatchID (key)
2. MemberID (Unique ID of member from MemberList table)
3. ResidentID (Unqiue ID of matching member from CityList table).
4. MatchType (value of NameDOBMatch, NameAddressMatch or DoubleMatch, depending on match type).
If anyone could help me create an SQL statement that would accomplish that, I would very much appreciate it!
Thanks,
Bryan
View 9 Replies
View Related
Sep 20, 2002
Using SQL Server 7 w/SP4.
I need to insert into another table the results of a query. Thought about using a temp table for the first query results, then querying those for the insert, but can't figure out how to pass the search results as a value.
Example:
SELECT ID, NAME FROM tbl2 WHERE ID BETWEEN 1 AND 50
IF @@ROWCOUNT > 0 BEGIN
INSERT INTO tblAudit
(ID, NAME)
VALUES
(@ID, @NAME)
END
Took a look at using SELECT INTO and INSERT INTO, but counldn't get those to work, either.
Thanks for any help.
View 3 Replies
View Related
Sep 20, 2005
Hi,I have a SQL query:select products.name, products.notes, products.purchase_date,products.serial_no, products.total_value, products.product_code,products.quantity, products.product_group, products.lhs1_name,lhs1.department, lhs1.address1, lhs1.city, lhs1.countryfrom products, lhs1where products.lhs1_id = lhs1.idI want to insert the results of this query into a table called 'temp' in thedatabase. I used to copy and paste this into excel then import it but itdoesn't always work.Is there a way to do it all in a SQL script. Please be aware that myknowledge of SQL is fairly basic so please explain things clearly.Thanks,Darren
View 4 Replies
View Related
Dec 15, 2005
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
View 6 Replies
View Related
Aug 15, 2015
I have a requirement to Insert Column 1 and Column 2 based on below condition only. Looking for a Store procedure or query
Condition : Allow Insert when column 1 and Column 2 have same values on 2nd row insert. But should not allow insert when Column 2 value is different.
ALLOW INSERT:
Column1 Column2
A0007 12-Aug
A0007 12-Aug
A0007 12-Aug
DONOT ALLOW INSERT: (COLUMN1 ID should not allow different dates)
Column1 Column2
A0007 23-Mar
A0007 02-Feb
FINAL OUTPUT Should be
Column1Column2
A000712-Aug
A000712-Aug
A000712-Aug
B000220-Jun
B000220-Jun
C000330-Sep
Discard Insert when Column1 ID's comes with Different dates.
View 4 Replies
View Related
May 16, 2008
Hi i am trying to create an insert statement that will insert rows into a table based on the information in the table already.
the table looks like this
Groupid field1 field2
-1 100 200
-1 100 300
-1 300 500
-1 300 600
-1 400 100
the insert looks like this
INSERT Into table1(groupid,field1,field2)
select -1,@passedvalue,field2
from table1
where field1 = @passedvalue1
assume @passedvalue = 700, @passwedvalue1 = 100
Now this is fine however i cannot have a duplicate key (key is comibantion of all 3 fields) thus the first time this runs it works however if it runs again it fails - how can i change the where clause to ignore rows that already exist?
eg if @passedvalue = 300 and passedvalue1 = 500
View 1 Replies
View Related
Jan 30, 2015
My requirement is below.enhancing the T- sql query as I was told not to use SSIS.
Insert data from Table A to Table B with conditions:
1. Truncate gender to one character if necessary.
2. Convert character fields to uppercase as necessary.
3. For systems that supply both residential and mailing addresses, use the residential address if available (both street_address and zip fields have value), mailing address otherwise.
In SSIS I took conditional split with 'ISNULL(res_street_address) == TRUE || ISNULL(res_zip) == TRUE '
default outputname :Consider Res Address; Outputname:Consider mail address.
and mapped as:
(Table A) mail_street_address---street address(Table B)
(Table A) mail_city----------------City(Table B)
(Table A) mail_Zip----------------Zip(Table B)
(Table A) mail_state-------------state(Table B)
(Table A) res_street_address--street address(Table B)
(Table A) res_city---------------City(Table B)
(Table A) res_Zip----------------Zip(Table B)
(Table A) res_state--------------state(Table B)
I want to do the same with T-sql code too:
I came up with below T-SQl but unable to pick(street,city,state,zip columns as I have take combination of street and zip from Table A not individual columns as I wrote in below query) based on above condition(3):
Insert into TABLE B
SELECT
Stats_ID
,UPPER(first_name) first_name
,UPPER(middle_name )middle_name
,UPPER(last_name) last_name
,UPPER(name_suffix) name_suffix
[code]....
View 2 Replies
View Related
Dec 15, 2005
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
$3.99/yr .COM!
http://www.greatdomains4less.com
View 3 Replies
View Related
Apr 12, 2006
hello all,this might be simple:I populate a temp table based on a condition from another table:select @condition = condition from table1 where id=1 [this will giveme either 0 or 1]in my stored procedure I want to do this:if @condition = 0beginselect * into #tmp_tablefrom products pinner joinsales s on p.p_data = s.p_dataendelsebeginselect * into #tmp_tablefrom products pleft joinsales s on p.p_data = s.p_dataendTha above query would not work since SQL thinks I am trying to use thesame temp table twice.As you can see the major thing that gets effected with the condictionbeing 0/1 is the join (inner or outer). The actual SQL is much biggerwith other joins but the only thing changing in the 2 sql's is the joinbetween products and sales tables.any ideas gurus on how to use different sql's into temp table based onthe condition?thanksadi
View 5 Replies
View Related
Jun 7, 2006
I am new to triggers and surely could use some help.
I can create a trigger to insert related records based on the main tables ID and insert that value into other related tables fine... but...
How do I create a trigger that can insert a record into one table for a columns given value and then insert a record into another table for another given value?
For instance:
New row...
Table1, Column1 (PK) has a value of 101
Table1, Column2 has a value of 'Blue'.
// When a new row is created in Table1 and Column2 has a value of 'Blue'...
I want to insert a new row into Table2 - with Table1 Column1's value.
// Now if Table1, Column2 has a value of 'Red' when the new row was created...
I want to insert a new row into Table3 - with Table1 Column1's value. Not Table2
This has to be inserted into one or the other tables based on column2's value, not both.
Then I want to populate the other related tables (Table4, Table5) with the regular insert statements based on Table1 Column1's value.
This (the conditional part above) has to work with an update to Table1 also.
So if someone came back to that record and changed Column2's value from 'Blue' to 'Red', it would have to delete the appropriate record in Table2 and then insert the new row into Table3 and visa-versa.
Can I do this with one trigger?
Thanks
View 8 Replies
View Related
Aug 12, 2014
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies
View Related
Mar 12, 2008
I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database.
I would like know which method is faster:
Use Execute SQL Task to insert the result set to the target table
Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination)
Could you tell me why then other is slower?
Thanks.
View 7 Replies
View Related
Mar 19, 2007
Hello,Would it be possible to open a socket connection and send few data based on an insert TRIGGER?My client would basically like to send the ID of a product via socket connection when a new product is inserted into the SQL database.Thanks for your helpArnold
View 13 Replies
View Related
Jul 18, 2014
I would like to change a value after the table updates inserts a new row based on the value of that column
Table Name is: MPanel
Col Names: RID, FPID.
I want to:
If column RID='16'
Then UPDATE FPID='1'
How can I trigger that update? Would this work:???
CREATE TRIGGER dbo.MyTrigLI
ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
UPDATE [MPanel]
SET
FPID='1'
WHERE (UPDATE(RID='16')
END
View 9 Replies
View Related
Jul 22, 2014
I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below
CLASS Days
A sun
A Mon
A wed
View 6 Replies
View Related
Jul 31, 2014
I have a query to insert records into a table based on a request but the query can only read one record at a time. How do i change the query such that it is able to read multiple records. In the below query i was able to input only 1 request which is 149906.
Query
declare @num_of_times int
declare @Count INT
DECLARE @newrequestid varchar(50)
DECLARE @Frequency VARCHAR(25), @RequestId INT, @x INT, @Max INT, @RptDesc INT
SET @RequestId = 149906
SET @x = 1
[Code] ....
View 1 Replies
View Related
Sep 29, 2015
How do I get the below scenario:
EmpID DepID
12 2
17 3
17 2
13 3
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
Desired Output:
EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2
View 5 Replies
View Related
Jul 26, 2015
I have the following 2 tables:
Table: classes Columns: classID, hp
Table: char_active Columns: name, classID, hp
The classes table is already populated.
What I want to do is insert a new row into char_active using the name and classID column, and have the HP column auto populate based on the corresponding value in the classes table. This is the trigger I wrote but I'm getting the error
Incorrect syntax near 'inserted'.
I'm new to sql, this is actually the first trigger I've tried writing.
create trigger new_hp on curr_chars.char_active
instead of insert
as
declare @hp tinyint
select @hp=lists.classes.hp from lists.classes where lists.classes.classID=inserted.classID
insert into curr_chars.char_active (name, classID, hp) inserted.name, inserted.classID, @hp
go
View 4 Replies
View Related
Aug 25, 2006
I am having issues getting this to work. I have the user login to a page to
put in a request for vacation. When they login, I have a label that isn't
visible that is equal to their User.Identity.Name.
I select the user from the employee table where the username = the label User
Identity Name and pull in the emp_id which is the primary key that identifies
the user.
I need to insert the request into the request table with the emp_id from the
select statement, without showing the em_id on the screen. I tried using a
hidden field and assigning the emp_id as the value, but it isn't working.
Not sure if this is the best way to do this. Really new to ASP.NET 2.0 so I
really appreciate any help.
Thank you!
View 12 Replies
View Related
