Does A Database Table Need An Id Column?

Dec 14, 2005

I always create database tables with an id column with an autoincrementing number.

Does a table actually need this or can I remove this field if I don't refer to it?

thanks

Jon

View 5 Replies


ADVERTISEMENT

How To Copy A Column(or Colums) From A Table In One Database To Another Table In A Different Databas

Oct 1, 2001

How do I copy a column(or colums) from a table in one database to another table in a different database

View 1 Replies View Related

How To Put URL Column In Server Database And Table

Feb 13, 2015

I have researched how to put a URL column in my SQl Server 2008 database and table. I have tried varchar(max) and varchar(1000) and then done what I found online by inserting into the url with ' ' around it and then I ODBC into Access and it does not look like a link. Cannot link on it and have it go where it needs to. I have also tried this in the front-end application and it does not look like a link. It does not respond either.

View 4 Replies View Related

Database/Table/Column Information

Jan 17, 2007

Hi,
I need to provide a report that lists the databases in each instance, then the tablenames and the number of rows in each table.

Is there any easy way to do this in SQL 2005?

Cheers
Gregg

View 8 Replies View Related

How To Update One Column To Be The Same For All Records In Database Table?

May 6, 2007

Hi,
I have a set of records in database table and I want to update one column to be the same for all of them.
Can you suggest code solution?

View 1 Replies View Related

Adding A Column To A Table Dynamically Against A Database?

Oct 3, 2007

I have the folowing databases DB1,DB2,DB3,D4,DB5........
I have to loop through each of the databases and find out if the database has a tablename with the word 'Documents'( like 'tbdocuments' or 'tbemployeedocuments' and so on......)
If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in  that database and there can be more than 1 'Documents' table in a database.
can someone show me the script to do it?
Thanks.
 
 

View 6 Replies View Related

How Can Setup Constraint For The Column In Table In Database

May 22, 2008

how can setup constraint for the column in table in database ??
and what will sould write in the (BLOCKED EXPRESSION field ??

View 5 Replies View Related

Checking Column Name From Multi Database's Table

Aug 18, 2006

hi
i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail.
if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks

View 2 Replies View Related

List Table Names In A Database Having A Particular Column.

Aug 3, 2007

Can anybody tell me how to find out whether a particular column exists in any of the tables of a database and if it does, display the table names?


Thanks

View 3 Replies View Related

Cann I Add New Column In Subscription Database Table

Aug 13, 2007

Hi

I am learning Sql server 2005 replication.

My requirement is.

1.) Subscribe a Master database
2.) I have to add a timestamp column in every table of subscriber database ( Note: the column will not be in publication database)

I have two sql server

server 1 : I made Merger publication

server 2 : I made subscription of the same

But when i tried to create a column in the Subscriber database table its giving the following error

Validation warnings

warnings were encountered during the pre-save validation process, and might
result in a failute during save. Do you want to continue attempt to save?

''Table1' table
- Error validating the default for column 'rowguid'.

I clicked "Yes"


Post -Save Notication
Errors were encountered during the save process. Some database objects were not saved.

'Table1' table
- Unable to modify table.
The DDL statement cannot be performed at the Subscriber or Republisher.
The DDL operation failed inside merge DDL replication manipulation.
The transaction ended in the trigger. The batch has been aborted.


clicked ok

User canceled out of save diaglog

Can i add a new column to subscriber database table if so please guide me?

View 5 Replies View Related

Why SELECT AVG(column) From Table Is Not Working In Jet Database?

Feb 10, 2007

Hello,

XPSP2
VB 2005 Express
Jet/Access database

One of the Forms of these application display a Jet database table. Why querries Min, Max, Avg do not work?

Thanks,

View 8 Replies View Related

Not Able To Create An Identity Column For An Existing Database Table

Feb 1, 2008

I am working with a table in SQL server. I have a column that I want to designateas an identity column. I am not able to do this, because the field for "Identity Specification" is not editiable.
What I did was I went to sql server, right clicked and selected "Modify".The column properties dialog box/edit grid is then displayed with attributesthat I can modify.
There are two major nodes in this dialog box. One is named "General" and the otheris named "Table Designer". I expand the "Table Designer" node and then go to the node labeled "Identity Specification" It is here where I would like to edit thevalues.
The values that are listed for edit are listed below. BUT, the problem is thatI can place my cursor in those fields, but I am not able to change/edit them.Can anyone tell me what the problem is here? and how I can fix it?
 +Identity Specification   (Is Identity)   Identity Increment   Identity Seed

View 3 Replies View Related

Counter For Max Tickets - Refer To A Database Table Row/column Etc

Feb 7, 2008

I posted a thread in the Getting Started forum about how to make a counter for maximum tickets : http://forums.asp.net/t/1215258.aspx
but maybe this is a more appropriate forum for this subject.
In a school project, we are making a website for a fictional concert/festival (using Visual Studio 2005, C#). On that site users can register and order tickets.
We have access to an SQL-database, by the way, where we can create tables etc. 
We want the maximum amount of tickets to be 10000 per day. The festival is supposed to last from friday to sunday.
What would be the best way to do this programatically? The counter should maybe be in an own database table?

View 3 Replies View Related

Exporting Table And Column Descriptions From Database To Cube

May 22, 2008



Can somebody tell me how to transfer table and column descriptions from SQL 2005 database to dimensions and attributes in Analysis Services?

Thanks!

View 2 Replies View Related

SQL 2012 :: Query Based On Column Name / Setting Up Database Table

Oct 30, 2015

I have a set of data spread across a number of tables regarding stock market data. An example of this follows:

Market Capitalization...

Date CompA CompB
01/01/11 100 5
02/01/11 102 4

Share Price....

Date CompA CompB
01/01/11 100 100
02/01/11 101 99

Event Data...

Date Company
01/01/11 CompA
02/01/11 CompB

Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.

I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.

For instance:
SELECT Event.Date, Event.Company
FROM Event

how do I now say.....

SELECT MarketCapitalisation.Column
WHERE Column = Event.Company
AND MarketCapitalisation.Date = Event.Date.

I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).

I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.

View 1 Replies View Related

SSIS Database Table To Flat File Destination With Column Headings

Sep 8, 2006

Hi,

I have a simple enough task to complete that I can€™t seem to find the answer to.

The task is this €“

Select table x from the database and write it to a flat file complete with that tables column headings.

Now I€™ve managed to set up an ole db datasource and selected the table and I€™ve also linked it to the flat file output.  So now I can generate a flat file from the database.  However no column headings appear in the flat file. 

I can€™t seem to find anywhere (like a checkbox) that will also output the column headings to the flat file. 

Now I can add in Headings manually in the properties of the Flat File Destination object but the columns that appear in the flat file don€™t appear to be in the order that I requested them in the SQL.

So the question is how do I automatically have the column headings appear for flat file output (ideally without me having to manually add them in).

If it can€™t be done and I have to use a vb.net script instead then would anyone have an example script of how to do it?

Thanks in advance for anyone who manages to answer this.

Matt.

View 6 Replies View Related

How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?

Sep 27, 2006

For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View 2 Replies View Related

A Table/column To Table/column Data Check (was Help Please, SQL Something Simple)

Sep 15, 2006

Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

Easy Peasy for you guys, any help would be appreciated.

View 6 Replies View Related

How To Create Key Time Column And Key Column For A Case Table And A Nested Table For Time Series Algorithm?

Jun 18, 2007

Hi, all experts here,



Thanks for your kind attention.



I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,



View 6 Replies View Related

Questions On Key Column In Case Table And Key Time Column In Nested Table Using Time Series Algorithm

Jun 4, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am confused on key column of case table and key time column of nested table by using Time Series algorithm.

In my case, the case table structure is as below:

Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),

While the nested table structure is as below:

Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)

So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?

Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?



Hope it is clear for your advices and help.

And I am looking forward to hearing from you shortly.



With best regards,



Yours sincerely,





View 1 Replies View Related

Adding New Table In Replication And Changing One Column Replication Database

Jan 17, 2002

Hi,

In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don

View 1 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

ALTER TABLE DROP COLUMN LastUpdated Failed Because One Or More Objects Access This Column.

Mar 7, 2008

Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
 

View 1 Replies View Related

Exclude Records From A Table Where ID Column Is Same But Mail Code Column Is Multi-valued

Nov 12, 2012

I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)

ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC

So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.

View 3 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

Cannot Insert The Value NULL Into Column SnapshotDataID, Table ReportServerTempDB.dbo.SessionData; Column Does Not Allow Nul

May 3, 2007

I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks

View 1 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

SQL Server 2012 :: Format Value Of A Column And Insert It Into Another Column Of The Same Table?

Sep 18, 2014

A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.

I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.

The text before ;# (35106) should be inserted in to another column of the same table.

Below is the table structure:

create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)

View 2 Replies View Related

SQL Server 2008 :: Create Table / Set Default Column Value To Value Of Another Column?

Mar 11, 2015

when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?

View 5 Replies View Related

Nested Table Key Column Is Not Bound To An Input Rowset Column

Jan 11, 2008

Hi!
I have a "little" problem with nested case model:



-- "normal" database:

DROP TABLE [unitInfo] ;

GO

CREATE TABLE unitInfo (

unitID INT PRIMARY KEY

, beginDate SMALLDATETIME

, area VARCHAR(10)

, partSize INT

, y2predict MONEY

) ;

go

INSERT INTO unitInfo

VALUES (1, '2007-02-01', 'home', 42, 10.0) ;

INSERT INTO unitInfo

VALUES (2, '2007-03-05', 'home', 43, 11.0) ;

INSERT INTO unitInfo

VALUES (3, '2007-02-02', 'office', 11, 11.4) ;

INSERT INTO unitInfo

VALUES (4, '2007-02-01', 'office', 10, 33.6) ;

INSERT INTO unitInfo

VALUES (5, '2007-02-01', 'office', 42, 44.1) ;



CREATE TABLE unitLog (

id INT IDENTITY(1, 1)

PRIMARY KEY

, logtime SMALLDATETIME

, -- combination of logtime/unitID is unique

unitID INT

, -- "FK" on unitInfo

m1 FLOAT

, m2 FLOAT

)





INSERT INTO [unitLog]

VALUES ('2007-01-01', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-01', 2, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-01', 3, 63.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-02', 4, 432.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-02', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-03', 1, 423.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-04', 1, 432.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-05', 2, 43.0, 441.0)

INSERT INTO [unitLog]

VALUES ('2007-01-06', 2, 43.0, 4.0)

INSERT INTO [unitLog]

VALUES ('2007-01-06', 3, 43.0, 4.0)

INSERT INTO [unitLog]

VALUES ('2007-01-07', 1, 4.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 3.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-09', 2, 143.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-10', 3, 143.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-11', 4, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-11', 5, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-12', 2, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-13', 4, 413.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-14', 4, 43.0, 414.0)

INSERT INTO [unitLog]

VALUES ('2007-01-14', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-20', 1, 43.0, 414.0)

INSERT INTO [unitLog]

VALUES ('2007-01-22', 1, 43.0, 414.0)



-- SSAS:

CREATE MINING STRUCTURE NestedStructure

( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE

, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS

, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS

, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS

)

)

ALTER MINING STRUCTURE NestedStructure

ADD MINING MODEL nestedModel ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR

,y2predict REGRESSOR PREDICT_ONLY

, logdata ([id] , unitID

, m1, m2

)

) USING Microsoft_Decision_Trees

/* version 1*/

insert into NestedStructure ( unitID, beginDate, area, partSize, y2predict

, logdata(skip,unitID, m1, m2))

openrowset('sqloledb', Server=myserver;Trusted_Connection=yes;,

'Shape {select * FROM mydb.dbo.unitInfo }

Append ( { select id, unitID, m1, m2 from mydb.dbo.unitLog }

Relate unitID to unitID ) as logdata ')

Parsing the query ...

OLE DB error: OLE DB or ODBC error: Syntax error or access violation; 42000.

Parsing complete

Where is the error?



/*version 2*/

CREATE MINING STRUCTURE NestedStructure1

( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE

, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS

, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS

, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS

)

)

ALTER MINING STRUCTURE NestedStructure1

ADD MINING MODEL nestedModel1 ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR

,y2predict REGRESSOR PREDICT_ONLY

, logdata ([id] , unitID

, m1, m2

)

) USING Microsoft_Decision_Trees



insert into mining structure NestedStructure1 ( unitID, beginDate, area, partSize, y2predict

, logdata(skip,unitID, m1, m2))

Shape {openquery(dsnDB,'select * FROM mydb.dbo.unitInfo') }

Append ( { openquery(dsnDB,'select id, unitID, m1, m2 from mydb.dbo.unitLog') }

Relate unitID to unitID ) as logdata



Parsing the query ...

Error (Data mining):

INSERT INTO error: The '[logdata].[id]' nested table key column is not bound to an input rowset column.

Parsing complete








Remark that combination logtime/unitID is the natural key in unitLog.
"ID" is the surrugate key.

What is wrong here...?

View 6 Replies View Related

Copy Database With Encrypted Column To New Server And Decrypt Column There

Aug 17, 2006

To do this successfully do I need to backup the Service master, Database master, and database itself from the the Source server, then restore all three of them on the destination server?

(I'm concerned that restoring the source Service Master key to a new target server with an existing sql 2005 install will screw things up big time.)

TIA,

Barkingdog

View 1 Replies View Related

Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?

Mar 25, 2008


Table structure as follows



Employee

Empno empname salary
commission



I want to have an other employee table named employee_modified

Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)

Is that possible to do it.. or am I doing something wrong.



something like



Select empno , empname , salary , commission,

(salary + commission) as derived_colum1 ,

(derived_colum1 + xxxxx) as derived_colum2 ,

(derived_colum2 + xxxxx) as derived_colum3

into employee_modified from employee

View 3 Replies View Related

Table Column Names = Dataset Column Values?!

Apr 28, 2008



I need to create the following table in reporting services



PRODUCT April March Feb

2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6





My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???


Thanks in advance!!!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved