Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ADD VALUES TO A NEW COLUMN Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 5:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:25 AM
Points: 39, Visits: 144
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
Post #1398110
Posted Tuesday, December 18, 2012 7:59 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 710, Visits: 4,531
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.
Post #1398115
Posted Tuesday, December 18, 2012 8:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:25 AM
Points: 39, Visits: 144
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
Post #1398125
Posted Tuesday, December 18, 2012 9:28 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 587, Visits: 2,838
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.


Post #1398139
Posted Tuesday, December 18, 2012 10:53 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 2,837, Visits: 3,955
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
Post #1398170
Posted Wednesday, December 19, 2012 2:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:25 AM
Points: 39, Visits: 144
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.
Post #1398671
Posted Wednesday, December 19, 2012 3:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 13,126, Visits: 11,964
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1398688
Posted Wednesday, December 19, 2012 5:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:25 AM
Points: 39, Visits: 144
THANKS FOR YOUR HELP. Got what I needed. You taught me well.

I love this forum
Post #1398710
Posted Wednesday, December 19, 2012 7:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:25 AM
Points: 39, Visits: 144
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
Post #1398734
Posted Wednesday, December 19, 2012 10:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 2,837, Visits: 3,955
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
Post #1398776
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse