Compare Data And Populate In Same Table
Feb 10, 2015
I have the data as below,in which owner is assigning some task to another user.
INPUT
#########
OWNER ID TASK_ID TASK_ASSIGNEE
user1 11user2
user112user3
user1 13user4
PRIVILEGE table
#########
USER_DETAIL PRIVILEGE_ID
user110
user111
user112
user28
user35
user46
OWNER has one set of privilege details in privilege table. I need to check privilege for user2,user3 and user4 in privilege table, if privilege not matches with the user1 then i want to populate the data output as below
NEEDED OUTPUT
###########
OWNER ID TASK_ID TASK_ASSIGNEE
user1 11user2
user112user3
user1 13user4
user211user2
user312user3
user413user4
I am populating this data in the view.
View 5 Replies
ADVERTISEMENT
Jul 20, 2005
Hi 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 Related
Jan 7, 2004
Hi Guys,
My ASP.Net app is multilingual and all my translations are stored in seperate MSDE tables, for example, tblEN for English, tblES for Spanish and tblTH for Thai.
When I send the installation files to my clients, I get them to double click on 4 MS DOS batch files that use OSQL to run 4 scripts.....
1. DataBase&Tables.sql - creates the database and tables
2. Logins&Users.sql
3. StoredProcedures.sql
4. Permissions.sql
This worked great before my language table came along to spoil the party and I now need some way of getting the data stored in the language on my server into the table on the client.
If I export the data to a text file and then send it out with the rest of the installation files - what are my options for transferring the data into the table ?
Any suggestions appreciated.
Steve.
View 4 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 4, 2004
Hi all,
Newbie here. I was wondering if any of you gurus could answer a question for me. Here is what I need to do (and I stress need):
I have 2 tables.
Table A has 3 columns, column 1 is unique customer numbers, column 2 is ticket numbers, column 3 is empty records.
Table B has 2 columns, column 1 is unique customer numbers (same numbers, although not the same order as Table A) , column 2 is invoice numbers.
I need to compare Table A where records in column 1 match records in column 1 in Table B. Where the records do match, I need to copy the records from Table B, column 2 to Table A column 3.
Can anyone here help me with this, please? It would really get me out of a jam with this, since it is the last step I have to take to finally get this new app rolled out.
Thanks a lot.
Mark
View 3 Replies
View Related
Jun 13, 2006
Hi all,
i have a question regarding data comparison in two tables in same database with same table structure.
my table structure is like this
CREATE TABLE xxgfs_gen_text_lookups_new (
lookup_type VARCHAR2(200) NOT NULL,
region_code VARCHAR2(30),
nongfs_value1 VARCHAR2(200),
nongfs_value2 VARCHAR2(50),
nongfs_value3 VARCHAR2(50),
gfs_value1 VARCHAR2(200),
gfs_value2 VARCHAR2(50),
gfs_value3 VARCHAR2(50),
retain_nongfs_for_dflts VARCHAR2(1),
retain_gfs_for_dflts VARCHAR2(1),
comments VARCHAR2(500),
created_by NUMBER(15),
creation_date DATE,
last_updated_by NUMBER(15),
last_update_date DATE,
last_update_login NUMBER(15),
Source_description varchar2(300),
Oracle_description varchar2(300),
defaults varchar2(300)
)
ALTER TABLE xxgfs_gen_text_lookups ADD CONSTRAINT xxgfs_gen_text_lookups_uq_1
UNIQUE (lookup_type,region_code,nongfs_value1,nongfs_value2,nongfs_value3);
i have some data in excel which i have uploaded using sql*loader using control card.Now i want to compare the data in both tables having same table structure only
do any body having idea how to compare the data using storeprocedure.
thanks in advance
regs,
Rajnish kumar
View 2 Replies
View Related
Jul 22, 2007
Table MediaImportLog
column ↘ImportIndex ImportFileTime ImportSource
value ↘80507 20060506001100 815
80511 20061109120011 CRD � P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table BillerChain
column↘BillerInfoCode ChainCode
value ↘750 815
value ↘81162 CRD
Table Biller
column↘CompanyCode BillerCode
value ↘999 750
value ↘81162 516
TAble DataBackup
column↘CompanyCode Keepmonth
value ↘999 6
value ↘81162 12
---------------------------------------------------
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ?
thank you very much
View 3 Replies
View Related
Jul 23, 2005
I am debugging one of our programs and ran the fix in Test. I would liketo compare table 1 between Production and Test. I want the query to outputcolumn 1 if Production <> Test output.What is the best way to achieve this?jeff--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
May 28, 2014
i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'
i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06
select c.* from (
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join
)
View 4 Replies
View Related
Jun 18, 2015
I have two identical table structure with at least 60 or more columns!!
Table A
ID numeric(18, 0) Primary Key,
RefID numeric(18, 0),
CoNum Varchar(15),
PrevCode Varchar(15),
DispCode VArchar(15),
EffDate Date,
Sal numeric(18, 0),
SDeposit numeric(18, 0) Etc....
I need to compare Data between two table and provide result like this!
RefId ID, CoNum, PrevCode, DispCode, EffDate, Sal, SDeposit
1, ,No Match, ,20,5200,0
So condition like. If table A Column Data does not Equal Table B Column data then
If datatype is Date then display Difference of Days.
if Datatype is Numeric then display Difference between value.( it display 0 if doesn't have difference)
if Datatype is Varchar then display No Match word.
View 0 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
May 22, 2008
Does abyone know how to compare data-type xml in a temp/variable/physical table in MSSQL 2000?
I tried this works in MSSQL 2005,
Code Snippet
create Table #t1 ([c1] int identity(1,1) not null, [c2] text)
create Table #t2 ([c1] int identity(1,1) not null, [c2] text)
Insert into #t1
Values('This is a test')
Insert into #t2
Values('This is a test')
Select * from #t1
Select * from #t2
Select * from #t1 where [c2] LIKE (Select [c2] from #t2)
drop table #t1
drop table #t2
but not MSSQL 2000.
Server: Msg 279, Level 16, State 3, Line 12
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Is this true (from BOL)?
Code SnippetIn comparing these column values, if any of the columns to be compared are of type text, ntext, or image, FOR XML assumes that values are different (although they may be the same because Microsoft® SQL Server„¢ 2000 does not support comparing large objects); and elements are added to the result for each row selected.
View 1 Replies
View Related
Jun 6, 2005
Hello,
I am receiving the following error:
Column name or number of supplied values does not match table definition
I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:
SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp
INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'
The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
Thanks,
Greg.
View 2 Replies
View Related
Aug 28, 2007
Hello,
In a Data Flow Task, I have an OLE DB source that uses the following SQL Statement;
******************************************
--First create a table
DECLARE @CategoryTable TABLE
(ColID Int,
ColCategory varchar(60),
ColValue varchar(500)
)
--and fill it
INSERT INTO @CategoryTable
(ColID, ColCategory, ColValue)
SELECT
0,
LEFT(RawCollectionData,CHARINDEX(':',RawCollection Data)),
LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',Ra wCollectionData)+1,255))
FROM Collections_Staging
--Assign an ID to each block of data for each occurance of 'Reason:'
DECLARE @ID int
SET @ID = 1
UPDATE @CategoryTable
SET [ColID] = CASE WHEN ColCategory = 'Reason:' THEN @ID - 1 ELSE @ID END,
@ID = CASE WHEN ColCategory = 'Reason:' THEN @ID + 1 ELSE @ID END
--Then put the data together
SELECT --cast to Nvarchar for MSAccess
a.ColID,
CAST(a.ColValue as Nvarchar(30)) AS OrderID,
COALESCE(CAST(b.ColValue as Nvarchar(30)),'') AS SellerUserID,
COALESCE(CAST(c.ColValue as Nvarchar(100)),'') AS BusinessName,
COALESCE(CAST(d.ColValue as Nvarchar(15)),'') AS BankID,
COALESCE(CAST(e.ColValue as Nvarchar(15)),'') AS AccountID,
COALESCE(CAST(SUBSTRING(f.ColValue,CHARINDEX('$',f .ColValue)+1,500)AS DECIMAL(18,2)),0) AS CollectionAmount,
COALESCE(CAST(g.ColValue as Nvarchar(10)),'') AS TransactionType,
CASE
WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL
ELSE CAST(h.ColValue AS SmallDateTime)
END AS DisbursementDate,
--COALESCE(h.ColValue,'') AS DisbursementDate,
CASE
WHEN i.ColValue LIKE '%Matching Disbursements%' THEN NULL
WHEN CAST(LEFT(REVERSE(i.ColValue),4)AS INT) > 1000 THEN CAST(i.ColValue AS SmallDateTime)
WHEN LEFT(REVERSE(i.ColValue),4) = '1000' THEN NULL
END AS ReturnDate,
--COALESCE(i.ColValue,'') AS ReturnDate,
COALESCE(CAST(j.ColValue as Nvarchar(4)),'') AS Code,
COALESCE(CAST(k.ColValue as Nvarchar(255)),'') AS CollectionReason
FROM @CategoryTable a
LEFT JOIN @CategoryTable b ON b.ColID = a.ColID AND b.ColCategory = 'Seller UserId:'
LEFT JOIN @CategoryTable c ON c.ColID = a.ColID AND c.ColCategory = 'Business Name:'
LEFT JOIN @CategoryTable d ON d.ColID = a.ColID AND d.ColCategory = 'Bank ID:'
LEFT JOIN @CategoryTable e ON e.ColID = a.ColID AND e.ColCategory = 'Account ID:'
LEFT JOIN @CategoryTable f ON f.ColID = a.ColID AND f.ColCategory = 'Amount:'
LEFT JOIN @CategoryTable g ON g.ColID = a.ColID AND g.ColCategory = 'Transaction Type:'
LEFT JOIN @CategoryTable h ON h.ColID = a.ColID AND h.ColCategory = 'Disbursement Date:'
LEFT JOIN @CategoryTable i ON i.ColID = a.ColID AND i.ColCategory = 'Return Date:'
LEFT JOIN @CategoryTable j ON j.ColID = a.ColID AND j.ColCategory = 'Code:'
LEFT JOIN @CategoryTable k ON k.ColID = a.ColID AND k.ColCategory = 'Reason:'
WHERE a.ColCategory = 'Order ID:'
***************************************
This statement parses. I can preview the data. I've tried to set up both an OLE DB destination to a SQL Server table and an MS Access table destination (Jet). In either case, the data will not populate the tables. I set up a Data Viewer, and no data appears in the Viewer. With the Access destination, I have the package set up to run in 32 bit mode.
If data appears in the preview, then why doesn't the data appear in the data viewer, and why will the data not populate either of the destination tables?
Thank you for your help!
CSDunn
View 1 Replies
View Related
Jun 6, 2007
Hi!
can any body tell me how to populate the fact table from base tables.
View 4 Replies
View Related
Jun 6, 2007
databse name: bookorder
Realtions:
customer (customerid,l_name,f_name,city, district,country)
cust_order(orderid,orderdate,customerid)
order_detail(orderid,itemno,isbn,quantity)
book(isbn,title,edition_no,price)
author(authorid,name,surname)
book_author(authorid,isbn,authorseqno)
Now populate the datawarehouse name: book_orderdw
having fact table & three dimension tables given below from the above bookorder database
fact table
factsales(customerid,timeid,isbn,unit_price,discount_price,sales_quantity,sales_amount)
dimension tables
dimcustomer(customerid,l_name,f_name,city,district,country)
dimtime(timeid,orderdate,dayofweek, month, year)
dimbook(isbn,title,edition_no,price)
Now I have to populate the fact & dimension tables by writing sql scripts.
Now I have already populated the dimension tables by writing sql script,
But I have to populate the fact table taking into account, here I am facing problem in wriring sql script
(i) unit_price is taken from the book base table with reference to the isbn
(ii) sales_quantity is taken from the order_detail.quantity with reference to table cust_order(via orderid & orderdate)
(iii) discount_price is determined dependent on the quantity. if the quantity > 20 then discount 20 %(i.e discount_price = 0.8 * unit_price). if quantity < 10, no discount i.e normal price. if quantity between 10 and 20, discount 10%. Note that the quantity is determined based on each order of each customer, thus if the same book appears at multiple positions in an order, those positions shall be grouped together. This could happen because the pk of the order_detail table is order_id + item no, not order_id + isbn
(iv) sales_amount is sales_quantity * discount_price
View 1 Replies
View Related
Jul 20, 2005
Hi all,I have two tables tbl_A and tbl_B.Table Definitions:tbl_A (field_1, field_2, field_3)tbl_B (field_1, field_2)I would like to populate tbl_B when a record is inserted into tbl_A.Note that tbl_A.field_1 = tbl_B.field_1 and tbl_A.field_2 =tbl_B.field_2 .How do I write a trigger on tbl_A to get the corresponding recordinserted into tbl_B?RegardsJune.....
View 2 Replies
View Related
Jul 20, 2005
Hi.I have a problem I hope someone can help me with.I have a database of events. Each event has a date and a duration(days).What I need to do is to be able to display search for events on someweb pages. My problem is that if an event is on say 15 nov 2004 andhas a duration of 3 days, at the moment if someone searches for the16th nov 2004 that event wont show up. To solve this what I have triedto do is put a trigger on my events table that populates another tableeach time a record is inserted. In the other table (orderedevents) anentry is added for each day specified in the duration field. I can thequery this table for the search. For example if this is added into theevents tableTitle: Event1Date: 15/11/2004Duration: 3Then this would be added to the orderedevents tableTitle: Event1Date: 15/11/2004Duration: 3Title: Event1Date: 16/11/2004Duration: 3Title: Event1Date: 17/11/2004Duration: 3This is what I have got so far. Someone kindly helped me with thisbefore but they had developed it on MySql, I'm using Sql Server and itdoesn't seem to work. All it does is add a blank row to OrderedEvents.Can anyone suggest any amendments that will make it work on SqlServer?CREATE Trigger TriggerInsertOrderedEventsON eventsFOR INSERTASSET NOCOUNT ONDECLARE @CountDuration int,@FixedDuration int,@eventdate datetime,@EventTitle varcharSET @CountDuration = 0SET @EventTitle = (SELECT EventTitle FROM inserted)SET @FixedDuration = (SELECT EventDuration FROM inserted)SET @EventDate = (SELECT EventDate FROM inserted)WHILE (@CountDuration <= @FixedDuration - 1)BEGININSERT OrderedEvents (Eventdate,EventDuration,EventTitle)VALUES(dateadd(day,@CountDuration,@EventDate),@Fix edDuration,@EventTitle)SET @CountDuration = @CountDuration + 1ENDSET NOCOUNT OFFThanks in advance.
View 3 Replies
View Related
Aug 3, 2007
Hello all,
I would like to populate a calendar table similar to the one used in the foodmart2005 sample database. In this table the day of the week, month, and year are recorded as well as additional data which I do not require.
If possible I would like to populate the table with 10 years of data starting with 2007.
Secondly would 10 years of data make the table to large?
Thank you for any thoughts you may have.
View 1 Replies
View Related
Aug 20, 2007
I have 2 tables in a parent->child relationship. The primary key for the parent table is moving to a new column (that is an Identity). I'd like to relink the tables using the identity field.
Here's the structure:
Parent
refid int (Identity) - NEW PK
orderid char(9) - OLD PK
Child
orderdetailid int - PK
orderid char(9) - Old link to parent
refid int - New link to parent
I've populated refid in the parent. I just need to populate the refid column in the child. I have a VFP background. In VFP I would have looped through the child, found the matching record in the parent, then transfered the data over. I don' t have a clue how to do this in TSQL.
Brian
View 4 Replies
View Related
Mar 4, 2008
I created a table with 4 columns. I have a file on disk generated from an Excel spreadsheet. It is a comma-delimited CSV file. It has 4 columns corresponding to the ones I set up in the new table. How do I populate the table with data from the CSV file?
View 1 Replies
View Related
Jul 20, 2006
Here is what I have and (somewhat understand). I’m using Visual Web
Developer 2005 Express Edition and have setup my application to use form authentication
(which automatically creates the ASPNETDB.MDF file with several default tables
and views). I’m using the CreateUserWizard
which is fine…but I need to collect additional information like (firstname,
lastname, address…and on..). What I’ve
done. I’ve created a tabled named
UserProfile and set UserId as the primary key (uniqueidentifier).
I then setup a 1-to-1 relationship
between aspnet_Users and UserProfile (which I think is correct). On my UpdateContactInfo.aspx page (where
users go to update their personal information) I use a hidden label control
(UserValue) to receive the UserId during the page_load event as below:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load UserValue.Text = Membership.GetUser().ProviderUserKey().ToString() End Sub Now with the UserID available I need to populate the
UserProfile table with the UserId, firstname, lastname, address of the
currently logged in user. How can I do this and am I on the
right track..?
View 6 Replies
View Related
Oct 16, 2006
We have stored large text data in "Image" data type in SQL Server. Can someone tell me how to get that vlaue from image datafield. Thanks
View 3 Replies
View Related
Oct 18, 2005
Hi,I have table (tbl_a) that get it values form three Dropdown List and the PK of this table is FK in different table(tbl_b).Since tbl_a get its values from three Dropdown List the number of rows in this table is limited to the number of combinations the three Dropdown List is offer (in any case the number of raws in that table will be final).Assuming tbl_a and tbl_b are both empty.
The problem is that if i want to insert row to tbl_b i have to check first if there is a row that holds the values (or combination of values) i am getting from the Dropdown List in tbl_a .Then, if such row is exist i have to get the PK of that raw and insert it to tbl_b (as FK),if such row does not exist, i have to insert it and then get the PK of this row and insert it to tbl_b (as FK).
In the end, tbl_a will have all the combination the three Dropdown List can offer so checking if raw exist in this table will not be necessary.
In my opinion this is not effective way to do that.Can someone offer me better way to do it?
Thanks
View 6 Replies
View Related
Oct 18, 2005
Hi,
I have table (tbl_a) that get it values form three Dropdown List and the PK of this table is FK in different table(tbl_b).
Since tbl_a get its values from three Dropdown List the number of rows in this table
is limited to the number of combinations the three Dropdown List is offer (in any case the number of raws in that table will be final).
Assuming tbl_a and tbl_b are both empty.
The problem is that if i want to insert row to tbl_b i have to check first if there is a row that holds the
values (or combination of values) i am getting from the Dropdown List in tbl_a .
Then, if such row is exist i have to get the PK of that raw and insert it to tbl_b (as FK),
if such row does not exist, i have to insert it and then get the PK of this row and insert it to tbl_b (as FK).
In the end, tbl_a will have all the combination the three Dropdown List can offer so checking if raw exist in this table will not be necessary.
In my opinion this is not effective way to do that.
Can someone offer me better way to do it?
Thanks
View 5 Replies
View Related
Sep 23, 2014
I have requirement to populate a combobox in excel from a table in SQL when the user opens the excel workbook. how I can do that.
View 1 Replies
View Related
Jun 3, 2006
Im a newbie with a sql table in a MSDE database that contains fields ofCustName, BrokerName, Type, Status.I am trying to write a trigger that does this:1. When a new record in a table is added, I would like the selectedfields to be copied to another table (call it Master).2. If that Master table already contains a record where the CustName =CustName, then simply ignore. If CustName does not match anything, thenadd the record to the Master TableIn other words, I am trying to create a table (or even a new database)that has unique records in it - making a master customer list.Any hints on how to get started on this?
View 6 Replies
View Related
Jul 19, 2006
hi all, can anyone help me?I am a relative newbie to sql server and I am more familiar withEnterprise Manager than QA. I have made many many access databasesthough. I am making an asp.net application where by there are a setnumber of users, about 80, each one logs in and manages informationwithin their department.To get them started a manager has written 10 different hazards thatwill apply to all of the departments, and he has written consequencesand controls for the hazards. Each department must have thisinformation as each will manage and deal with them differentlyThe hazard information is stored in a main 'hazards' table, and theconsequences and controls are stored in related tables linked by the'hazardID' from the main table to a foreign key 'hazardID' in therelated tablesWhat i want to know is if there is a relatively simple way of using aquery to populate the 10 hazards to each department, and to alsoinclude the related table links, i dont mind renaming the departmentsnames to match each hazard, but i do not want to have to relink therelated tables manuallyIf anyone can give me any advice to get me started i will be incrediblygratefulthank youTable information is belowHazards------------HazardID - identity key fieldHazard - varcharDepartment - varcharConsequences----------------------ConsequenceID - identity key fieldHazardID - FKConsequence - varcharControls------------ControlID - identity key fieldHazardID - FKControl - varchardwight
View 3 Replies
View Related
Apr 24, 2007
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off, for a fewweeks in advance. Anysuggestions?
View 4 Replies
View Related
Apr 25, 2007
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off. Anysuggestions?
View 2 Replies
View Related
Sep 8, 2006
I have a page that has about 8 dropdown boxes that need to be populated from sql tables. What is the best way to populate these boxes. Here is how I have it nowconn = New SqlConnection(ConfigurationManager.AppSettings("SQLString")) ''''''''''''' Fill in DropDownList Status '''''''''''''''''''strSelect = "SELECT * FROM Requests_Status"cmdSelect = New SqlCommand(strSelect, conn) conn.Open()dtrSearch = cmdSelect.ExecuteReader()ddlRequestStatus.DataSource = dtrSearchddlRequestStatus.DataTextField = "RequestStatusName"ddlRequestStatus.DataValueField = "RequestStatus"ddlRequestStatus.DataBind()ddlRequestStatus.Items.Insert(0, New ListItem("-- Select Below --", -1)) cmdSelect.Cancel()dtrSearch.Close()conn.Close()''''''''''''' Fill in DropDownList Container '''''''''''''''''''strSelect = "SELECT * FROM Containers"cmdSelect = New SqlCommand(strSelect, conn) conn.Open()dtrSearch = cmdSelect.ExecuteReader()ddlContainer.DataSource = dtrSearchddlContainer.DataTextField = "ContainerName"ddlContainer.DataValueField = "ContainerID"ddlContainer.DataBind()ddlContainer.Items.Insert(0, New ListItem("-- Select Below --", -1)) cmdSelect.Cancel()dtrSearch.Close()conn.Close()'''''''''''''''''''''''''''''I then repeat the same commands as above for the other 6 dropdowns. This seems like a bad way to have to do all this.ThanksCraig
View 3 Replies
View Related
Dec 16, 2003
How can I populate options in a dropdown (combo?) box based on data pulled from a SQL database compared against certain paramaters (if it is not equal to x, equal to y, etc)?
Thanks.
View 2 Replies
View Related
Dec 16, 2003
I apologize about the newbie-ness of these questions, but I am a total newbie (I have used VB for a while but this is my first 5 hours with ASP ever).
I just asked how to populate dropdowns, which I have found out how to do, but this question is quite a bit more difficult for me.
I have a SQL database, and in that database is a table, with a few columns. The first columnis a list of user id's, and the second is list of "options".
I want to make it on my webpage so that if the userid matches the column1 data, then it will display a checkbox for the column 2 option. Ie, if its "USERID1,OPTION1" in the SQL table, then if userID = "USERID1" then it will make an checkbox with caption/name "OPTION1". It has to loop through all the rows in the SQL table and make all the checkboxes for these options that match.
I have no idea how to do this, any help would be great.
View 1 Replies
View Related
