Monday, June 17, 2013

[SQL Server] Table 1 New Record Autocounter field value copied to Table 2 and creates new record

[SQL Server] Table 1 New Record Autocounter field value copied to Table 2 and creates new record

Table 1 New Record Autocounter field value copied to Table 2 and creates new record

Posted: 17 Jun 2013 09:49 AM PDT

When the user selects an Add New record - Table1 uses the Autocounter field to generate a PrimaryKey and creates the new record for data entry.Table2 is part of a Rules Engine. It needs to have a one-to-one relationship of the primary keys.The trigger must fire after the primary key (autocounter) is assigned to Table1. Use the AddNew record (or append) on Table2 - and include the same Primary key that is on Table1.Note: Table1 never has a record deleted. It has an Active / Inactive field to determine what is live or historical. Table2 will constantly have dozens of new columns added as the rules engine is developed. Table2 has metadata. Table2 will not use an autocounter. Since the PK from the Table1 autocounter is unique, and in sequence, it will just be a 1:1 relationship between Table1 <--> Table2. At least that is the goal. The front-end interface is Access 2010 using SQL Server Native 11.0 client and DSN-Less linked tables.

Trigger to set a value for a column

Posted: 17 Jun 2013 07:21 AM PDT

I have a column that has to be set with a certain value as A,B,C or D when a certain column has a value i.e. is not null. The columns are in various tables in the databases and the select is show below[quote]select A.decription, B.Decsription,C.Description,D.Decription from Audit A left join A.Id = B.ID left join on B.Id =C.ID left join on C.ID = D.ID [/quote]and the column X in Audit table has to be updated with A,B,C,D as when if there is value in A.description the value for X will be B, when B.Description has value the value on X will be C, when c.description is there the value for X will be D how do i form a trigger that does this thanks

SQL Server scheduled jobs

Posted: 17 Jun 2013 07:01 AM PDT

I have a SQL Server job that checks for a file in an FTP server folder. If the file is there the file is processed and the job completes. If there is nothing there (the vast majority of the time), the job fails and an error is reported. I am checking every minute from 8 am to 4 pm Monday through Friday. What I would like to do is check if the file is in the FTP server folder, if it is there process the remainder of the 9 steps. If it is not, simply return nothing and wait until the next minute comes up. I have looked for something like count to determine if a file exists or not. I am checking for the validity of the file in the first step of the processing, but I think that there should be an easier and less error producing way to check.I am using SQL Server 2008 R2.

Replace the values from the lookup tables

Posted: 17 Jun 2013 07:16 AM PDT

I have 2 lookup tables. I need to get and replace the values from the lookup tables for the below data.[u][b]Lookup Table 1[/b][/u][b]LT_ID Desc[/b]Cat_Code [u]Category[/u]Prod_Code [u]Product[/u][u][b]Lookup Table 2[/b][/u][b]LT_ID[/b] [b]Desc[/b]Drama__Q__M [u]Drama[/u]Comedy__Q__M [u]Comedy[/u]Blue__Q_Ray [u]BlueRay[/u]I have another table with the below information1 Changed Cat_code to Drama__Q__M From Null2 Changed Product_code to Blue__Q__Ray From Null3 Changed Cat_code to Comedy__Q__M From Drama__Q__Mfrom the above I need an output like below1 Changed Category to Drama From Null2 Changed Product to BlueRay From Null3 Changed Category to Comedy From DramaCan anyone please provide the solution for this?Thank you in advance

Extracting Excel files from Multiple subfolders and load them into sql server table basing on city name

Posted: 17 Jun 2013 03:23 AM PDT

Hi, I have a folder which contain's Sub folders(City Names) and in those sub folders I have excel files. I have to take all the excel files from the sub folder and load them into a SQL Server table basing on the city name. The files are structured as below.Dallas Folder contain's these files DALLAS_Cars_2011.xlsDALLAS_Trucks_2011.xlsDALLAS_Bikes_2011.xls my requirement is I have to load the cars data into cars table and Trucks data into Trucks tables, basing on their names and all the city file's should go into same table like cars's, trucks.. And I have to create an ID column basing on the city name it can be based on the alphabetical order can any of the guys help me in this...

files and filegroups

Posted: 16 Jun 2013 09:55 PM PDT

Hi I have a database with 3 filegroups primary,secondary and third.secondary marked as default.now i have created a table on third filegroup,and inserted some data into the tablemy question is where will the data get stored? secondary or thirdwhile secondary is still marked as defaultthanks

How to get this trigger on insert/update working

Posted: 16 Jun 2013 10:52 PM PDT

Hi all!I need to do something upon update/insert of a new record, and as it has to be automatical, i think a trigger is the answer.As a start, I wrote this:CREATE TRIGGER [dbo].[SetOtherWorkAccount] ON [dbo].[timeaccountmovement]AFTER INSERT, UPDATEASIF UPDATE (starttime)BEGIN UPDATE timeaccountmovement SET timeaccountid = 22 WHERE ID IN (SELECT ID FROM inserted)ENDThe syntax is ok, its created on table timeaccountmovement.First problem here is, that i do not know which field is updated - it could be any field in the table, the trigger should fire anyway.OK, I tried to change the value of starttime on a single record - It should then have a timeaccountid of 22 after that happened.But nothing happened.What am I doing wrong?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog