December 18, 2012 at 5:14 pm
I have two tables (TRIP and TRIP_DETAIL both have the same primary key called TRIP. There is no FK relationship. I have data in both tables. I need to create a Master/Detail form in VB.NET Pro 2010.
I need to add a new column in the TRIP_DETAIL table called TD_NUM. Then sort the table by TRIP column, and Then fill the new column TD_NUM with int value in ssequencestarting at 1. Then after filling the TD_NUM column make it the primary key and make the TRIP column a FK. I am very new at sql could someone give me an example on how to do this . I know how to add columns. I just need the sql query to do the sort and fill the New TD_NUM.
Thanks
December 18, 2012 at 7:59 pm
lonhanner (12/18/2012)
I have two tables (TRIP and TRIP_DETAIL both have the same primary key called TRIP. There is no FK relationship. I have data in both tables. I need to create a Master/Detail form in VB.NET Pro 2010.I need to add a new column in the TRIP_DETAIL table called TD_NUM. Then sort the table by TRIP column, and Then fill the new column TD_NUM with int value in ssequencestarting at 1. Then after filling the TD_NUM column make it the primary key and make the TRIP column a FK. I am very new at sql could someone give me an example on how to do this . I know how to add columns. I just need the sql query to do the sort and fill the New TD_NUM.
There's no FK relationship? Make one! It's a Master/Detail form... that's what FKs are for!!!
You need to add the column for the foreign key... I'll give you a hint, because I'm not doing your homework. Look up ALTER TABLE... adding columns and foreign keys are in there. Read Books Online. Hiding under the F1 key.
December 18, 2012 at 8:40 pm
Thank you kindly for the info I will see what I can do with it. NOW I am not doing home work. I volinteered for the program I am writting. I am 59 yrs of age. I will explain to you what I or we are trying to do. We and we all are volinteers. We take disabled persons to and from the hospitals or DR. apptments. All are funding comes from donations and fund raisers to pay for van, car, and bus maintenance and fuel. Most of our people who we take to apptments or whereever are VETS, but not all, everyone who meets the standards all ride. Due to all the liabiliy and reports, and data we have moved from Access database to Sql database. I have already created the sql database and tables, and also imported the data from access to sql. I guess your right I am in school. I am learning as I go. I have already written the code in VB.NET 2010 express to INSERT/UPDATE/DELETE Several tables. This problem is kick'en my butt.
Thanks Again
December 18, 2012 at 9:28 pm
One method you could use is create a new table with the new column as an identity column, then run an insert statement, not populating the identity column, with an order by in the insert. SQL will then handle the incrementation of the identity column.
December 18, 2012 at 10:53 pm
lonhanner (12/18/2012)
I just need the sql query to do the sort and fill the New TD_NUM.
create table TRIP_NEW
(
TD_NUM int identity not null,
other columns list
)
insert into TRIP_NEW (columns list)
select columns list from TRIP
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 19, 2012 at 2:22 pm
Please Check This query for me.
CREATE TABLE NEW_DETAILS
(
TD_NUM int identity not null,
TRIP int not null,
RIDER_NO int not null,
LNAME nvarchar(20) null,
FNAME nvarchar(20) null,
PROV_CON int null,
APT_TIME time(7) null,
PKUP_TIME time(7) null,
TD_REMARKS nvarchar(20) null,
NEW_RIDER nvarchar(5) null,
SORT nvarchar(4) null,
);
insert into NEW_DETALIS
(TRIP, RIDER_NO, LNAME, FNAME, PROV_CON, APT_TIME, PKUP_TIME, TD_REMARKS, NEW_RIDER, SORT)
select TRIP, RIDER_NO, LNAME, FNAME, PROV_CON, APT_TIME, PKUP_TIME, TD_REMARKS, NEW_RIDER, SORT
FROM TRIP_DETAILS
If the query is correct and all goes well. That still leaves the problem with geting the sequance numbers in the new column TD_NUM. I have over 80,000 records in the TRIP_DETAILS TABLE. In other words after I exeicute the query I will end up with data in all the column except the new TD_NUM column. How do I get get the data in numeric sequnce in the TD_NUM COLUMN so I will have the FK DATA.
December 19, 2012 at 3:13 pm
lonhanner (12/19/2012)
Please Check This query for me.CREATE TABLE NEW_DETAILS
(
TD_NUM int identity not null,
TRIP int not null,
RIDER_NO int not null,
LNAME nvarchar(20) null,
FNAME nvarchar(20) null,
PROV_CON int null,
APT_TIME time(7) null,
PKUP_TIME time(7) null,
TD_REMARKS nvarchar(20) null,
NEW_RIDER nvarchar(5) null,
SORT nvarchar(4) null,
);
insert into NEW_DETALIS
(TRIP, RIDER_NO, LNAME, FNAME, PROV_CON, APT_TIME, PKUP_TIME, TD_REMARKS, NEW_RIDER, SORT)
select TRIP, RIDER_NO, LNAME, FNAME, PROV_CON, APT_TIME, PKUP_TIME, TD_REMARKS, NEW_RIDER, SORT
FROM TRIP_DETAILS
If the query is correct and all goes well. That still leaves the problem with geting the sequance numbers in the new column TD_NUM. I have over 80,000 records in the TRIP_DETAILS TABLE. In other words after I exeicute the query I will end up with data in all the column except the new TD_NUM column. How do I get get the data in numeric sequnce in the TD_NUM COLUMN so I will have the FK DATA.
You will have a value in TD_NUM it is an identity column that does not allow NULL.
I am pretty sure we can help you with what you are trying to do but you need to provide enough details so that we aren't guessing. What we need is ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. Please see the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 19, 2012 at 5:11 pm
THANKS FOR YOUR HELP. Got what I needed. You taught me well.
I love this forum
December 19, 2012 at 7:25 pm
OK I am back. After geting my TRIP_DETAIL table created now I am having problems creating the relationships, and FK's, and by the way I am using sql express 2008. I reading about Primary Key, and Foreign keys I still can not get what I need. So I removed all Primary Keys from my TRIP and TRIP_DETAIL tables. Here is my tables:
So I need a one to many relationship. Just a reminder I have lots of data in each table, and I am learning.
TRIPS(Table Columns): ONE TABLE
TRIP (int, not null) "THIS NEED TO BE PRIMARY KEY"
TRIP_DATE (data null)
MONTH (nvarchar(8), null)
BOOK (nvarchar(10), null)
R_T_F (nvarchar(5), null)
TRIP_DESC(nvarchar(50), null)
TRIP_COMT(nvarchar(50), null)
LIFT(nvarchar(5), null)
TCC(nvarchar(5), null)
LEG(nvarchar(5), null)
PASS((int, null)
CRN((int, null)
WCP(nvarchar(3), null
TRIP_NO(nvarchar(12, null)
CODE(nvarchar(10, null)
NEW_RIDER(nvarchar(5, null)
TRIP_DETAILS(Table Columns): MANY TABLE
TD_NUM (int, not null) "DOSE THIS NEED TO BE PRIMARY KEY ?"
TRIP (int, not null) "DOSE THIS NEEDS TO BE FK FROM TRIPS TABLE ?"
RIDER_NO (int, not null)
LNAME (nvarchar(20), null)
FNAME (nvarchar(20, null)
PROV_CON(int, null)
APT_TIME(time(7), null)
PKUP_TIME(time(7), null)
TD_REMARKS(nvarchar(20), null)
NEW_RIDER(nvarchar(5), null)
SORT(nvarchar(4), null)
HOW DO I DO THIS I AM SO LOST. I AM USING SQL SERVER EXPRESS 2008
December 19, 2012 at 10:43 pm
lonhanner (12/19/2012)
OK I am back. After geting my TRIP_DETAIL table created now I am having problems creating the relationships, and FK's
For PK
ALTER TABLE TRIP
ADD CONSTRAINT pk_TRIP_trip PRIMARY KEY (trip)
for FK
ALTER TABLE TRIP_DETAILS
ADD CONSTRAINT fk_TRIP_DETAILS_trip
FOREIGN KEY (trip )
REFERENCES TRIP(trip)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 20, 2012 at 10:31 pm
Yeah!!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply