Problem With Coping Primary Key Into Other Field In The Same Table
May 17, 2005
is there any way that i can copy primary key(PK) value to another field in the same table .. say my PK is MemberID i want to replicate that into other field say SortID at the same time when the primary key is incrementing. I'm a newbie in this field please pardon me if something is wrong in the way i'm asking ...please help me friends i'm struggling since a long time ... Thanks in advance ..
I'm trying to get a SQL 7 and 6.5 DB to interact, but while there is no problem in SQL7, I cannot create a table called "Public" or a field called "Primary"!! Does anyone know why this might be and if so where I might get a list of any other "invalid" names??
I have a question on autogenerating numbers for a primary key field, "studyID," in a table€”but with a few twists.
We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001).
Here€™s the range of values we want our StudyIDs to be (this is to be our validation rule as well):
I am designing a database. I want to define a automatic sequence on a table primary key field. what is the best solution for it?
I know I can enable identity property for a field, but it has some problems ( for example its seed jumps on restart and unsuccessful events)
I also can use some calculated sequences. for example I can calculate max of the filed values and after incrementing use it as key for new inserted record.
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails. I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record. And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
I would like to copy my msde database so I can deploy it together with my webpage on another pc. Do I simply just copy the .mdf file or is there another way to extract it?
I'm current copying data tables to other tables that don't, some which don't allow duplication of data, i'm dealing with about 40 tables. the insert errors when i try the full copy process, is there a way of saying copy the data if it's not there?
and if there is matching data ignore....... I have seen there is - if exist() would this work in this work in this case.
the only thing i can think of is to delete all the data from the table which in some case will lead to a loss of data- bad times :)
Hi,My questions is we want to make a copy of a database onto the sameserver while preserving the diagrams, stored procs, etc.We stopped the SQL service and made a copy of the data and log files.We attempted to ATTACH the file copies (after re-naming them), but theembedded file information tells SQL that the database already exists.Our database is a piece of junk, but we must use it. If we don't wantto use the IMPORT/EXPORT Wizard to Copy Objects (because we get someerrors during the transfer), how can we make an exact copy of theDatabase onto the same server while giving the "new" database adifferent name?Thanks:DHRUV
Does anyone has an idea on creating a new field in a table with more then say 10000 rows already in that table, and making that new field as primary key field. None of the fields in that table are unique.
Hey guys. I just wanted to ask you a question? we are having a field declared as BIT and we want that field to be unique..We are not able to declare as a primary key..Is there anyway to declare as unique from the database point of view?..please help us out in this issue and i'd appreciate that.
I want to find out the primary key field name of the table using system table information i.e. sysobjects, syscolumns etc. I tried to find it out but it seems to be very complicated. Could anybody help me in this regards?
I have an app that is connected to SQL 2005 express edition. In the DB there is a table that has an auto incremental primary key What I want is, I want to get a new value for that auto incremental field before sending the new record's date to the DB to be stored. So I can display the new record's ID in my app's interface
So is there something in SQL server like SEQUENCE in oracle.
I have and ASP.NET and VB.NET application that needs to copy selected stored procedures from one SQL Server to another SQL Server. The scenerio is that the user will select a source server from a drop down list then the source database from another dropdown listbox which then populates a listbox with the stored procedures in the database. They can select multiple stored procedures from the listbox.
Once selected, they select a target SQL Server from a dropdownlist and then a target database from another dropwdown list. I have a command button that they need to do the actual move but I am not sure how to get the selected source stored procedures copied to the target database. Any ideas would be greatly appreciated.
I have a table (table1) that has a bunch of fields....[field1][field2][field3][field4][field5]None of these fields are unique, but if I combine them, then they areunique.I know there is a way to make multi-field primary keys, but when I tryI get an that field1 is not unique, which I already know,How can I make a multi-field primary key?thanks
I want to use the Identity field (increment 1,1) as a primary key andhave a unique constraint on my other field which is of type char.I am worried that related data in other tables may lose referntialintegrity if records in the ID table get messed up and need to bere-entered.Can you please advice on best way to do this. I definitely need anumeric id field because it makes the joins and queries so muchfaster.
I'm trying to take data and push it into a new database.
This data was taken from a database where there were many contraints, FK etc. quite a complicated structure and now sent to us with just the data in the tables with none of these constraints, FK!!
This data now needs pushing into the original database structure, so all the data will live within the constraints, but using an insert SPROC throws some FK errors, what is the best way to push this data into it's structure!
somebody suggested DTS but this doesn't take into account the constraints, also we need to run this at anytime for a number different datasets. Can anybody help ?(if you can decipher my explantion)
Many thanks for reading, please indicate if you need more info.
Im using Sql server Express 2005, in the client place. i want to copy the database with all data and put it in my sql server express 2005 in office. so plz help me how can i do this how can i copy entire data from sql server express and put it in another sql server express
I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product.....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input.......I'm very sorry but I'm having a hard time putting this into words for some reason..umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?ThanksAdam.
I'm really puzzled with this. I am new to using MS SQL and need help with the primary key. When I was using Access, I would open the database table and just enter values for the various columns, each time a new column was begun, the computer would insert a unique number there. OK, so now I am using visual studio to work on a SQL database. I created a new table and the first field I specified was "ID", I then chose it as the primary key. For default value, I tried both auto number and auto increment. These don't seem to work, as when I type values into fields and try to move to the next row, having left the ID field blank, I get an error which reads "String or Binary data may be truncated". What is going on, what do I need to do so the field automatically populates with an ID number... Some research sort of led me to the concept of a "trigger" to do this. Is this the approach. I was able to r-click on the table and I saw an option for "create trigger" or "new trigger"... How do I learn to do this, is there a generic trigger to do an auto increment of a primary key ID field?
when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'. CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'. Could not create constraint. See previous errors. The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.
Hi guys, I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE JurnalTransMoves.SUF SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752 10 60
5752 20
5752 30
5753 400 3000
5753 500
5753 600
5753 700
5753 800
5754 7 15
5754 8
Is there any chance to do that? Thanks in advance, Aldo.
Code Snippet SELECT Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME', Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY', Accounts.FILTER AS 'ACCOUNTS.FILTER', Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP', AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME', Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode', CreditTerms.DETAILS AS 'CreditTerms.DETAILS' CreditTerms.CURRENF AS 'CreditTerms.CURRENF' CreditTerms.MONTH AS 'CreditTerms.MONTH', CreditTerms.DAYS AS 'CreditTerms.DAYS', CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC', CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x' WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y' ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID', DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE', JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION', JurnalTrans.REF2 AS 'JurnalTrans.REF2', JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF', JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF', JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM JURNALTRANSMOVES AS JurnalTransMoves_1 INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY LEFT OUTER JOIN ITEMS AS Items INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE Accounts.SORTGROUP Between '3001' And '3020' AND Accounts.ACCOUNTKEY IN ('123456')
First, I consider myself a novice in Mssql. I've recently began the revision of a website that uses MS Sql Server 2000. In a effort to set up a working environment on my pc, I asked to get a copy of the sql server database. The response was something like "I'm not going to do that!" or "that's too much effort" which left me baffled. Furthermore, the size of the database shouldn't be the issue.
In Access all that is needed is the .mdb file to copy a database. In Sql Server is it that more complicated? My questions are as follows
How does someone copy a sql server database to another pc? Were they expecting that I remotely test with Sql Server? Is that possible or practical? Isn't it prefered to have the db localized?
Trying to get the lowest unused value of CrewID from the below table in a efficient manner. I cant change the table structure as its from a 3rd party and it would break our support agreement.
CREATE TABLE [dbo].[Crews]( [CrewID] [nvarchar](10) NOT NULL, [CrewName] [nvarchar](200) NOT NULL, [CompanyID] [nvarchar](10) NOT NULL, [StartTime] [nvarchar](50) NULL,
I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
I have been testing my database application for a while now. As a result i have a lot of records already with their respective identity field values. What i want to do is to reset this field to 0 (zero) before i deploy the aplication. I have tried deleting all the records and entered one to see if the id will be 1 but it still starts from where it was before i deleted the records. Any ideas.
I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..