Trigger To Populate Another Table.

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 some
web pages. My problem is that if an event is on say 15 nov 2004 and
has a duration of 3 days, at the moment if someone searches for the
16th nov 2004 that event wont show up. To solve this what I have tried
to do is put a trigger on my events table that populates another table
each time a record is inserted. In the other table (orderedevents) an
entry is added for each day specified in the duration field. I can the
query this table for the search. For example if this is added into the
events table

Title: Event1
Date: 15/11/2004
Duration: 3

Then this would be added to the orderedevents table

Title: Event1
Date: 15/11/2004
Duration: 3

Title: Event1
Date: 16/11/2004
Duration: 3

Title: Event1
Date: 17/11/2004
Duration: 3

This is what I have got so far. Someone kindly helped me with this
before but they had developed it on MySql, I'm using Sql Server and it
doesn'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 Sql
Server?

CREATE Trigger TriggerInsertOrderedEvents

ON events

FOR INSERT

AS

SET NOCOUNT ON

DECLARE @CountDuration int,
@FixedDuration int,
@eventdate datetime,
@EventTitle varchar

SET @CountDuration = 0
SET @EventTitle = (SELECT EventTitle FROM inserted)
SET @FixedDuration = (SELECT EventDuration FROM inserted)
SET @EventDate = (SELECT EventDate FROM inserted)

WHILE (@CountDuration <= @FixedDuration - 1)
BEGIN

INSERT OrderedEvents (Eventdate,EventDuration,EventTitle)
VALUES(dateadd(day,@CountDuration,@EventDate),@Fix edDuration,@EventTitle)
SET @CountDuration = @CountDuration + 1
END

SET NOCOUNT OFF


Thanks in advance.

View 3 Replies


ADVERTISEMENT

Using Trigger To Populate Another Table

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

Trigger To Populate Table Or Database

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

Trigger Fires In One Db And Populate Another Db

Apr 12, 2007

hi,

I want a trigger in db aaa to fire when table a_aaa is updated in server a and table b_bbb in db bbb in server b to be populated with data. I know how to write a trigger if fired and the result stays in one server with one db. But I don't know how to do it if between two servers and two db.

a
win server 2003 standard Edition
sql Server 2000
db:aaa
table: a_aaa
column:a_aaa_a

b
win server 2003 standard Edition
sql Server 2000
db:bbb
table:b_bbb
column: b_bbb_b

It's not working because the symtax is incorrect and because I am not sure how to do it between two servers. If it is not correct, where am I wrong? Where should each line be located?? et cetera......
Can anyone help?

Thanks in advance.




CREATE TRIGGER [enddate_changed_on_alert] ON [dbo].[USER_DATA]

FOR INSERT, UPDATE
AS

BEGIN
SET NOCOUNT ON;

DECLARE @EndDate as DATE
DECLARE @CompCode as VARCHAR(15)
DECLARE @PhoneGMSM as VARCHAR(10)
DECLARE @PhoneALERT as VARCHAR(10)

SELECT @PhoneALERT=phone1 from AUSSMEDEVIQ1.QDB_Test.USER_DATA;
SELECT @PhoneGMSM=PHONE1 from AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;

UPDATE AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ SET EXT4 = "111"
WHERE AUSSMEDEVIQ1.QDB_Test.USER_DATA.@Phone = AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;


WHERE:

USER_DATA_DEVIQ = b_bbb_b
AUSADFORMULA02 = bbb
QDB_KS = b_bbb
USER_DATA = a_aaa_a
AUSSMEDEVIQ1 = aaa
QDB_Test = a_aaa

END
GO

View 9 Replies View Related

Insert Trigger To Populate Other Columns In Same Row

Sep 22, 2006

I'm looking for an efficient way to populate derived columns when Iinsert data into a table in SQL Server. In Informix and PostgreSQLthis is easily done using the "for each row..." syntax, but all I'vebeen able to come up with for SQL Server is the following:create table testtrigger(id integer unique, b integer, c integer)gocreate trigger testtrigger_ins on testtriggerfor insert asupdate testtrigger set c = (select ...some_function_of_b... fromtesttrigger t1,inserted t2where t1.id = t2.id)where id in (select id from inserted);gowhere id is testrigger's unique id field, and c is a field derived fromb.This seems terribly inefficient since each insert results in an extraselect and update. And if the table is large and unindexed (which itcould be if we are bulk loading) then I would imagine this would bevery slow.Are there any better ways of doing this?Many thanks,...Mike Dunham-Wilkie

View 2 Replies View Related

INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table

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

Column Name Or Number Of Supplied Values Does Not Match Table Definition When Trying To Populate Temp Table

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

Trigger Doesn't Log Into The Audit Table If The Column Of Table Has Trigger On Is Null

Jan 23, 2008



Hi,

I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out

and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up

Any thoughts?


Here is my t-sql


Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)

select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d

where i.req_guid = d.req_guid

and i.req_fname <> d.req_fname



Thanks,
leo

View 7 Replies View Related

How To Populate A Table From A Sql Statement

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

Populate The Fact Table

Jun 6, 2007

Hi!

can any body tell me how to populate the fact table from base tables.

View 4 Replies View Related

How To Populate The Fact Table

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

Populate Calendar Table

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

Populate Field From Another Table

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

How To Populate A Table From A CSV File

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

How Do I Populate A Table With Data On Setup

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

How To Populate Table From Dropdown List?

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

How To Populate Table From Dropdown List?

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

Populate Combobox In Excel From Table

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

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 View Related

Query To Populate Child Table

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

Populate Table With Stored Proc

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

Populate A Table With Stored Proc.

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

Select Satement To Populate A Custom Table

Jan 7, 2004

I would like to run a simple select statement that pulls records from a table and returns a dataset. It is a dataset of teams. Then I want to populate a highly customized table with that dataset. The table will be a tournament bracket full of teams. Can I do something like this or do I have to display the dataset in a datagrid? Oh and this is a webmatrix project using MSDE. Anybody have any suggestions?

View 2 Replies View Related

Newbie How To Create Temp Table And Populate

May 8, 2006

Sorry guys I know this is easy but I've been looking for about an hour for a straight forward explanation.
I want to store a user's wish list while they browse the site, then they can send me an enquiry populated with their choices.
Basically, a shopping cart!
I thought of using session variables and string manipulations but I am more comfortable with DB queries.
a simple 4 column table would cover everything.
SQL server and VBScript
Thanks
M

View 4 Replies View Related

SQL Server 2008 :: Populate Table With Some Gaps In Between

Mar 28, 2015

I need to populate a table with numbers with some gaps in between with logic below.

first row -1110
last row - 9550

1110
1120
1130
1140
1150

[Code] .....

View 8 Replies View Related

SSRS : Populate Dynamic Column In Table

Feb 14, 2008

Hello,

I am having hard time to populate dynamic column based on parameters.For eg.
Parameter-->Dates : Date(1) ,Date(2),Date(3)....Date (n)
(Multiple-Parameters is selected by drop down list )
Report format:
Id | Currency |Country |District |Date(1)- transaction |Date(2)-transaction |Date(n)
1 | $$ |USA |123 |523238 |643763573 |62376264

Now requirement is If user enter 1 date then only single column date(1) populate if enter (n) dates then n column Date(1) ,Date(2),Date(3)....Date (n) populate.
kindly suggest me any solution requirement.

I tried to bind Visibility property of column with Parameters passed.So that user can see only those column for which they pass values.I am getting error "Index was outside the bounds of the array"

I write this expression under Column(Date)-->Property-->visibility --> hidden--> expression
=iif(len(Parameters!VALUEDATE.Value(1)) > "0",false,true)
Kindly suggest any working solution .Thank you

View 5 Replies View Related

Algorithm To Populate A Table With Finite Value Combinations

Jul 2, 2007

I need to populate a table which have 10 columns with four values. Each row should be a different combination of these four values and the columns can be null too. In other words how can I get all the different combinations for the 4 values that can be in 10 buckets. The final result column based on these values will be generated manually.

For example , I have for grades (P,F, WP, WF) and I have 8 terms and two exams. 8 terms and two exams can have any of the above four values. Based on these grades and terms and exams I need to generate a table which wil be used to determine the student final status Pass/Fail.

What will be the best way to do this and how is it possible. Is there a T-SQL or C# program for this.

If I need to submit this in another forum please let me know.

View 3 Replies View Related

Web Scrapping - Populate Price Of Product Match With Name In Table

Sep 19, 2013

web scrapping from the web details, to populate the price of product match with name in the Table?

View 2 Replies View Related

Loop Through A Recordset To Populate Columns In A Temp Table

Jul 23, 2005

I want to take the contents from a table of appointments and insert theappointments for any given month into a temp table where all theappointments for each day are inserted into a single row with a columnfor each day of the month.Is there a simple way to do this?I have a recordset that looks like:SELECTa.Date,a.Client --contents: Joe, Frank, Fred, Pete, OscarFROMdbo.tblAppointments aWHEREa.date between ...(first and last day of the selected month)What I want to do is to create a temp table that has 31 columnsto hold appointments and insert into each column any appointments forthe date...CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2nvarchar(500), Day3 nvarchar(500), etc...)Then loop through the recordset above to insert into Day1, Day 2, Day3,etc. all the appointments for that day, with multiple appointmentsseparated by a comma.INSERT INTO#Appointments(Day1)SELECTa.ClientFROMdbo.tblAppointments aWHEREa.date = (...first day of the month)(LOOP to Day31)The results would look likeDay1 Day2 Day3 ...Row1 Joe, PeteFrank,FredMaybe there's an even better way to handle this sort of situation?Thanks,lq

View 11 Replies View Related

Transact SQL :: Populate Column On Insert Based On Value From Another Table

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

Suspect Database Attempting To Populate Large Table

May 2, 2007

I am trying to load in to a new application's database to see if my load procedures will be able to handle the volume and how long I should plan on when I perform our cut over for production. Unfortunately, trying to copy 5.4M rows from one staging table into a production processing table consistently leaves the database in a suspect status, usually stating to perform some type of disk consistency check utility. It happens time after time, is there anything obvious that I could look at to see why this is happening?

View 3 Replies View Related

CLR-Based Trigger? Recursive Trigger? Common Table Expression?

Nov 14, 2006

Hey,

I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.

A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.

A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.

Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.

If you need the design or create script (table layout), please let me know.

Thanks.

View 4 Replies View Related

Trouble With Update Trigger Modifying Table Which Fired Trigger

Jul 20, 2005

Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'

View 1 Replies View Related







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