The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType and insert into #Request table along with the other data. How do I do this?
SAMPLE DATA
Code Block Insert into #CallDetail(12123,1) Insert into #CallDetail(53423,1) Insert into #CallDetail(6532,1) Insert into #CallDetail(62323,1) Insert into #CallDetail(124235,1) Insert into #CallDetail(65423,2) Insert into #CallDetail(56234,2) Insert into #CallDetail(2364,2) Insert into #CallDetail(34364,2) Insert into #CallDetail(85434,2)
Insert Into #Call(111,1,12123) Insert Into #Call(112,1,53423) Insert Into #Call(114,1,6532) Insert Into #Call(123,2,62323) Insert Into #Call(134,1,124235) Insert Into #Call(143,2,65423) Insert Into #Call(145,1,56234) Insert Into #Call(154,2,2364) Insert Into #Call(185,1,34364) Insert Into #Call(195,1,85434)
Insert Into #request Values('324234','Jack','SA023',12,111,0); Insert Into #request Values('223452','Tom','SA023',12,112,0); Insert Into #request Values('456456','Bobby','SA024',12,114,0); Insert Into #request Values('22322362','Guck','SA024',44,123,0); Insert Into #request Values('22654392','Luck','SA023',12,134,0); Insert Into #request Values('225652','Jim','SA055',67,143,0); Insert Into #request Values('126756','Jasm','SA055',67,145,0); Insert Into #request Values('786234','Chuck','SA055',67,154,0); Insert Into #request Values('66234','Mutuk','SA059',72,185,0); Insert Into #request Values('2232362','Buck','SA055',67,195,0);
EXPECTED OUTPUT will be (See the last column for unique nos). :
Code Block Insert Into #request Values('324234','Jack','SA023',12,111,1); Insert Into #request Values('223452','Tom','SA023',12,112,2); Insert Into #request Values('456456','Bobby','SA024',12,143,1); // Calltype = 1 empid= 1, but state is different, hence unique id is 1 Insert Into #request Values('22322362','Guck','SA024',44,114,1); Insert Into #request Values('22654392','Luck','SA023',12,123,3); Insert Into #request Values('225652','Jim','SA055',67,143,1); Insert Into #request Values('126756','Jasm','SA023',69,134,1); Insert Into #request Values('786234','Chuck','SA023',72,145,2); Insert Into #request Values('66234','Mutuk','SA059',72,185,1); Insert Into #request Values('2232362','Buck','SA055',67,195,2);
Please note that this will not be run as a batch query, but the no. has to be generated and inserted into #record table in realtime. I have given bulk of records for understanding of the problem
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
Hi everyone, I need urgent help to resolve this issue... As far as the performance goes which one is better.. Unique Index(col1, col2) OR Unique constraint(col1, col2) ? Unique constraint automatically adds a unique index and unique index takes care of uniqueness then whats the use of unique constraint ?
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
hi team, .Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today. Here is the code
Code Snippet create procedure SearchPartKeyword ( @Keyword varchar(250) = null, @Universal_Id varchar(10) = null ) as select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p inner join notes n on p.PartNumber = n.Identifier where n.Universal_ID = @Universal_ID and p.Description like @Keyword union select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p where p.Description like @Keyword
and the results: PartNo Description SO Notes LA DMM Legacy Folder Printer de90008 MAIN BOARD 1 DGF1 114688 0 0 0 de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
create table Test ( [recId] [int] identity(1, 1) not null, [code] [varchar](50) not null, [prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)), constraint [PK_Test] primary key clustered ( [recId] ) with fillfactor = 90 on [primary] ) on [primary] go
insert into Test (code, prime) values ('AVA', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('CAR', cast(1 as bit)) insert into Test (code, prime) values ('CAR', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(1 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit))
select * from Test
I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:
-- This should fail insert into Test (code, prime) values ('RLW', cast(1 as bit))
I am developing a ASP.NET application using SQL Server as my database. I want to create a number that is unique.
The number will have 8 digits.
first 2 digits correspond to year. ex:04 or 03
the next 6 digits start with 000001 and it should get added for each new entry of data.
i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.
I have generated an SQL script such that when it is run, it will automatically create tables if not existing or drop the existing tables. The problem I have is that, is there any way I can also make the script create not only the tables but also transfer the data from the old database to the new database?
I would really appreciate if any of you could point me to any existing programs, scripts etc that can do that. Thanks in advance
Is there a way to generate SQL Scripts from a stored procedure?
For disaster recovery and documenation reasons, we'd like to create scripts of our databases periodically. I, being somewhat lazy, would like to automate this so I really don't have to remember to run this every so often.
I am unable to see how to generate a leading zero.
Table A
declare @TableA table ( ID numeric , Fruits varchar(10) ) insert @TableA select 1,'Oranges' union all select 2,'Mangoes' union all select 3,'Apricots'
ID Table A 1 Apples 2 Oranges 3 Grapes 4 Apricots
declare @TableB table ( seed numeric , ) insert @TableB select 080513000448 union all select 080513000449 union all select 080513000450
Table B seed 080513000448 080513000449 080513000450
I wrote the following query but i need generate a leading zero not sure which function can help maybe the right function but i am not sure how to use it in this case
SELECTconvert(varchar(10), getdate(), 12) + (SELECT CASE WHEN SUBSTRING(ISNULL(max(seed),'00000'),1,6) = convert(varchar(10), getdate(), 12) THEN SUBSTRING(ISNULL(max(seed),'00000'),7,12) ELSE '000000' END AS SEED FROM B) + (row_number() over (order by Id)) as SEED FROM A
A simple question! I would like to create a database that creates say serial numbers and saves them to a table. If poss I would like to create them on demend and not duplicate.....
Can anyone suggest a case-study/reference links/video demos for creating a cube using Analysis Services and generation of reports using Reporting Services.
Help again please,I need to insert rows into a table from another table. The tables areidentical column wise except the table im inserting from does not havea primary key value. On insert I need to generate a primary key thatis consecutive based on the table im inserting into. Also the table iminserting into does not have a identity column. Much appreciated.
we would like to generate SSIS packages(connections, data flow tasks, etc..) programatically using C#. Is it possible to do so?
At present we are creating packages by dragging and dropping required componets from toolbox and configuring them manually. We have a requirement of creating around 300 packages every week. Manual creation of packages is really a nightmare.
I heard from one of friends that using c# we can create packages. is it true? if so, can we view/edit the packages generated using c# using VS 2005 IDE(if required) ?
SRS 2005 provides functionality for loading and rendering reports on fly using LoadReportDefinition and Render methods defined in ReportExecutionService webservice.
I was wondering if the same/similar behaviour can be accomplished in Reporting Services 2000.
I need to convert this list into sql server logins. Can this be done via a tsql statement? if so how, and if it cant be done via a statement then what other way can it be done.
I'm having problems generating the primary key for a sql server table. I use a slowly changing dimension to discriminate modified and new records. The primary key in the SQL Server table is a combo number/letter incremental (ex. 0000A, 0001A...9999A, 0000B...). I tried creating Instead of insert and For insert trigger for a table but this doesn't seem to do the work.
What are my other options? How can I generate a primary key for every new row?
Hello all, I have a few questions relating to scripts and I'm looking for some help, or at least a point in the right direction. 1) I'm generating CREATE scripts to move a number of tables/procedures/functions from my testing database to my production database. The issue I have is the script doesn't seem to be generated in a particular order, so I'm getting errors when a table/procedure/function is created that references something that hasn't been created yet. Is there a setting available to create the script in a specific order to prevent these errors? 2) This is somewhat related to the first question. I need to drop all the tables/procedures/functions before I re-create them. Can I set all the DROPs at the beginning of the script, and then run all the CREATEs? 3) Eventually the database will be ready for production and in use. And eventually I'll be asked to make changes, without erasing existing data. Can I accomplish this using the generate scripts feature? What topics should I be investigating when looking into conducting these types of updates? "Updating database" is too general and I'm not sure the technical term. I've done very simple DB-related tasks so I'm trying to figure out what I'm looking for so I can educate myself. Basically, I don't know what I don't know. Any help is appreciated. Thanks.
hi i am trying to gernerate some tables ( first with a script and then with a stored procedure),, in ms server management studio exrress 2005 using the followingcreate table ip( id int IDENTITY NOT NULL, ip varchar(15) NOT NULL, hostname varchar(128) NOT NULL, primary key(id)) ; i store this in a new query (assume :right click associates the query to the current database ?) and run it and nothing happens its been a while since i did sql but i think the sql is ok....how or more specifically where is the appropriate place to run this code thankssimo stored as a new query
