Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » ADD VALUES TO A NEW COLUMN
Add to briefcase
11 posts, Page 1 of 2
12»»

ADD VALUES TO A NEW COLUMN Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
lonhanner
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, February 22, 2013 11:14 AM
Points: 39, Visits: 141
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
pietlinden
Posted Tuesday, December 18, 2012 7:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:03 PM
Points: 191, Visits: 1,655
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
lonhanner
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, February 22, 2013 11:14 AM
Points: 39, Visits: 141
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
dogramone
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: 2 days ago @ 5:35 PM
Points: 510, Visits: 2,086
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
Bhuvnesh
Posted Tuesday, December 18, 2012 10:53 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1398170
lonhanner
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, February 22, 2013 11:14 AM
Points: 39, Visits: 141
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
Sean Lange
Posted Wednesday, December 19, 2012 3:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 8,980, Visits: 8,540
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
Post #1398688
lonhanner
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, February 22, 2013 11:14 AM
Points: 39, Visits: 141
THANKS FOR YOUR HELP. Got what I needed. You taught me well.

I love this forum
Post #1398710
lonhanner
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, February 22, 2013 11:14 AM
Points: 39, Visits: 141
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
Bhuvnesh
Posted Wednesday, December 19, 2012 10:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1398776
« Prev Topic | Next Topic »

Add to briefcase
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.