SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ADD VALUES TO A NEW COLUMN


ADD VALUES TO A NEW COLUMN

Author
Message
lonhanner
lonhanner
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4849 Visits: 13164
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.
lonhanner
lonhanner
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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
dogramone
dogramone
Right there with Babe
Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)

Group: General Forum Members
Points: 743 Visits: 3737
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.



Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5218 Visits: 4076
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;-)
lonhanner
lonhanner
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25969 Visits: 17525
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.

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)
lonhanner
lonhanner
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 144
THANKS FOR YOUR HELP. Got what I needed. You taught me well.

I love this forum
lonhanner
lonhanner
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5218 Visits: 4076
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;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search