Generating Values As Part Of A Compound Key
Aug 30, 2005
BEGINNER QUESTION
I have a table which has a compound primary key consisting of two columns.
One of these columns is a foreign key which is generated in another table by
an identity.
I want to be able to generate the other primary key column value
automatically when an insert occurs but assume that I cannot use an identity
because it would have to be unique for this table.
There will be potentially more than one user accessing this table so I want
to avoid generating the key on the client side.
How can I do this? Will it require some hardcore T-SQL?
I hope this is clear (I suspect it isn't) I'd be happy to supply more info.
I would be extremely grateful for any help!
Mark.
View 4 Replies
ADVERTISEMENT
Apr 10, 2008
Hi all,
I've had this problem for a while now and I'm looking for a better solution.
I'm pulling through a dataset from a SQL Server 2005 database and populating it into a DataGridView. The end user updates information on the grid and I then want to updates the results in the SQL Server table. My table has a compound primary key made up of two fields.
I'm currently looping through the data grid and for each value identified to update I'm executing a stored procedure that takes the two fields as parameters. However I know that this method isn't very efficient, especially if the user wants to update a few hundred records as it will execute the stored procedure a few hundred times.
Is there a way I can pass the two parameters in an ArrayList or something like that? I need SQL Server to be able to take the parameter. Is XML the way to go or is there any additional support in the .NET framework for such problems?
Thanks for your help.
View 7 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Aug 8, 2007
Hi,
I am new to SQl Server, i have created a logical model for a database that i am creating for a project. Is there a way i can assign automatic values to my Primary Key (PK) Column other than using an identity?
For example i have a table called indicator for which the Primary Key (PK) is indicator_identifier; i want the primary key values to be
ind_0001
ind_0002
ind_0003
Please note that i may be populating the tables from a VB.NET form
Please can anybody help me?
View 2 Replies
View Related
Nov 25, 2003
I need to know how to create a compound primary key in Transact-SQL
It's really simple, it's a table with 3 column, two of those make the primary key Right now the CREATE TABLE look like this:/* SectionsContent Table */
CREATE TABLE SectionsContent (
SectionID Int NOT NULL
CONSTRAINT FK_SectionsContent_SectionID FOREIGN KEY
REFERENCES PsychoCMS.dbo.Sections(ID),
DocumentID Int NOT NULL
CONSTRAINT FK_SectionsContent_DocumentID FOREIGN KEY
REFERENCES PsychoCMS.dbo.Documents(ID),
Position int NOT NULL
)
Can anyone help me ?
View 1 Replies
View Related
Jul 20, 2005
Hello,How can I stop/prevent SQL server from running compound SQLstatements. I do not want the server to run multipleupdate/delete/insert/select statements as a batch. Is there an option?/Kafwww.afiouni.com
View 11 Replies
View Related
Jul 23, 2005
Is it possible to force the use of a compound index in a query?create table Test (ColOne int, ColTwo int)The compound index is ColOne + ColTwo.I'm interested in searching on ColTwo, but I also know the value ofColOne will always be the number "1".How do you structure the SQL statement to concatenate the two INTs anduse the index? Note that I don't have any control over the creation ofthese indexes.
View 3 Replies
View Related
Aug 1, 2000
I am trying to make up a SQL string which will be executed with the Exec command
I want to add a return column that is not in the table, and the table columns:
something like
Select @Ten As Ten, @Tablename.* From @Tablename (
where @Ten has an integer value. )
The statement was originally:
select @SQL = 'select * from ' + @TempName
exec (@SQL)
which had no problem. Then I needed to add an extra column of static data to all the returned rows, and confusion !!!!!
Thanks,
Judith
View 1 Replies
View Related
Apr 26, 2006
Hello,if you create this table:create table hello (int a, int bconstraint pk_hello primary key clustered ( a, b ))and then insert the following recordsa,b1,11,21,32,12,22,33,13,23,3and then doselect a,b from hellothe output seems to be:a,b1,12,13,11,22,23,21,32,33,3which is wrong and (i think) is reflecting the actual index orderand physical order on diskit should be:a,b1,11,21,32,12,22,33,13,23,3i have tested this on a table with 500,000 recordsand sure enough if you declare the clustered primary key fields inreverse order:constraint pk_hello primary key clustered ( b, a )two things happen:- the select with no order by returns the records in the expected order- queries relying on that order run MUCH FASTERhas anyone else seen / noticed this?
View 9 Replies
View Related
Nov 2, 2014
Looking to create a query, as simple as possible, that allows me to compound returns on a rolling daily basis. So far this this have I have:
DECLARE @stock_returns TABLE
(
stock_code VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
daily_return NUMERIC(10, 2) NOT NULL
);
[Code] ....
But I´m not getting what I need. If you run the above select, the output should be:
stock_codedate1daily_returnLAGCompound_return
stock12014-07-080.00510 0.00000 0.0051000000
stock12014-07-090.00300 0.00510 0.0081153000
stock12014-07-100.00500 0.00300 0.0080150000
stock12014-07-110.00600 0.00500 0.0110300000
stock12014-07-120.00200 0.00600 0.0080120000
stock12014-07-130.00700 0.00200 0.0090140000
stock12014-07-140.00240 0.00700 0.0094168000
stock12014-07-150.00240 0.00240 0.0048057600
stock12014-07-160.00250 0.00240 0.0049060000
The problem is with this column:
(lag(daily_return, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return
The (daily_return + 1) portion should be the accumulated compound return. So it should be something like
(lag(ACCUMULATED_COMPOUND RETURN, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return
And the output should be:
Date1Daily returnLAGCompound Return
08/07/20140,00510,00000,0051
09/07/20140,00300,00510,0081
10/07/20140,00500,00300,0132
11/07/20140,00600,00500,0192
12/07/20140,00200,00600,0213
13/07/20140,00700,00200,0284
14/07/20140,00240,00700,0309
15/07/20140,00240,00240,0334
16/07/20140,00250,00240,0359
View 10 Replies
View Related
Dec 19, 2007
Hi
I have a sql 2000 db which has a table that has a compound key, the problem is that I would like to create a Full Text Catalog for this table. However I noticed that i need a single primary key... but I dont have one.
I created another field on my table called "ftcID" as an int with identity set to Yes
However when I try and create a catalog it doesnt detect that this field is unique.
Does my unique field have to be a Primary Key, I cant remove the compound primary key as it will break my application.
Any help would be much appreciated
Many thanks in advance
View 3 Replies
View Related
Oct 29, 2007
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
Thanks
Shankar
View 1 Replies
View Related
Apr 28, 2006
Hai,
I have been working in DW for a while, but using SSIS as an ETL tool is new for me. I worked extensively on Informatica.
Coming to my question, right now I am trying to do SCD type 1. But my dimension table has a compound key. i.e more than 1 column makes a key for the table. But SCD wizard allows to select only 1 attribute as a business key. Does any one have any suggestions on how to implement SCD if the target table has compound key.
Thanks in advance for your suggestions and answers.
Venkat
View 5 Replies
View Related
Dec 7, 2007
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
View 3 Replies
View Related
Jan 28, 2015
I have made the following Scalar-valued function:
CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)
[Code] ....
What it does is convert numbers to times
E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)
So far so good (function works perfectly)
My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.
This means that, e.g. with a time like 3.23.40 the last zero must be deleted.
My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,
is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))
There are 5 codes: 0 1 2 3 and 4
View 9 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Apr 12, 2006
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config
The CCWW is my PC's name, normally I can connect to the database ASPNETDB.MDF correctly either Part 1 or Part 2 in a web page,After I open Database Explorer panel and browse ASPNETDB.MDF, I can't connect to database using Part 2 when I open a webpage in Microsoft Visual Web Developer 2005 Express Edition,but I can correctly open a webpage using Part 1 after I open Database Explorer panel.
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config?
I guess while I use Part 1 to connect, maybe it will be cancel exclusive method of the database ASPNETDB.MDF first, but when I connect to database using Part 2, maybe two programms both Part 2 and Database Explorer visit ASPNETDB.MDF at the same time!
--------------------------------------Part 1------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" /> --------------------------------------Part 1------------------------------------------------------------------------
--------------------------------------Part 2------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=CCWWSQLEXPRESS;Initial Catalog=ASPNETDB.MDF;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />--------------------------------------Part 2------------------------------------------------------------------------
View 2 Replies
View Related
Jan 19, 2004
hi,
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.
Any help would be highly appreciated.
thanks,
-sriram
View 7 Replies
View Related
May 31, 2001
Hi all,
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
Regards,
Celia
View 1 Replies
View Related
Oct 8, 1999
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.
Any help would be appreciated.
View 1 Replies
View Related
Jun 7, 1999
Is there a way to generate SQL scripts from other than SQL Enterprise Manager?
View 3 Replies
View Related
Oct 29, 2014
I must produce an XML file with this layout:
Code:
<root>
<dataroot>
<Professions>
<Profession>web designer</Profession>
<tags>
<tag>Ruby on Rails</tag>
<tag>Apache</tag>
<tag>HTML/CSS</tag>
[code]...
I can't get my head around it ..I've even tried to solve it by using FOR XML RAW.
View 3 Replies
View Related
May 13, 2008
Hi
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
SEED
----
80513000451
The output which i need is
SEED
----
080513000451
rather then
SEED
----
80513000451
regards
Hrishy
View 20 Replies
View Related
May 12, 2007
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.....
I have blank page syndrome!!!!
Thanks in advance
View 2 Replies
View Related
Feb 19, 2008
Can anyone suggest a case-study/reference links/video demos for creating a cube using Analysis Services and generation of reports using Reporting Services.
Thank You
View 2 Replies
View Related
Mar 15, 2008
Guys,
I have to generate sequence for distinct group of values for example
intially seq is set to 1 through out the table
categorydescidseq
__________________________________
AccountingAccounting61
AccountingAccounting72
AccountingFinal81
AccountingFinal92
AddendumAddendum 101
Is there any way to accomplish this?
Any suggestions and inputs would help
Thanks
View 4 Replies
View Related
Mar 31, 2008
hi
how can we generate the data in csv files of database tables by using sql query?
regards
View 3 Replies
View Related
Aug 24, 2006
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.
View 7 Replies
View Related
May 12, 2006
Hi,
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) ?
Regards,
Gopi
View 4 Replies
View Related
Mar 29, 2007
Hello,
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.
Thanks in advance,
Kobi
View 1 Replies
View Related
Jul 12, 2007
What does N indicate in the following script
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].xxxx' )
Plz can anyone explain
EXEC dbo.sp_executesql @statement = N'Create View xxxxx'
View 4 Replies
View Related
Oct 17, 2006
I've been given an excel file that lists:
userID's,
Names
Password
expiary dates.
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.
Many thanks in advance.
View 2 Replies
View Related
Sep 18, 2007
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?
Any advice is appreciated.
Regards
Sara
View 6 Replies
View Related
