Create Table with a Concatenated Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create Table with a Concatenated Field

Author

Topic

markjrouse
Starting Member

2 Posts

Posted - 2009-10-29 : 07:54:50

Hi,

I was wondering if it was possible to have a script that would concatenate fields together. For example, on the ExceptionID field I have a concatenagation.

CREATE TABLE [CPCP].[Exceptions](
[TableID] [int] IDENTITY (1, 1) NOT NULL,
[ModuleID] [int] NOT NULL,
[ReportID] [nvarchar](30) NOT NULL,
[ExceptionID] AS ([TableID]+'-'+[ModuleID]+'-'+[ReportID]),
[Fieldx] [nvarchar](30) NOT NULL,
[Fieldy] [nvarchar](30) NOT NULL,
) ON [PRIMARY];
GO

I don't know if this will work. What ever data is inserted into this table, ExceptionID automatically concatenates the three fields. Would I also have to use the convert() function so that they were all the same data type?

Is this possible in SQL Server 2005/2008, or would I have to use some other means of doing this, such as have SSIS create this computed field when it pulls in data?

Regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-29 : 08:23:52

It should be

CREATE TABLE Exceptions(
[TableID] [int] IDENTITY (1, 1) NOT NULL,
[ModuleID] [int] NOT NULL,
[ReportID] [nvarchar](30) NOT NULL,
[ExceptionID] AS (cast([TableID] as varchar(10))+'-'+cast([ModuleID] as varchar(10))+'-'+[ReportID]),
[Fieldx] [nvarchar](30) NOT NULL,
[Fieldy] [nvarchar](30) NOT NULL,
) ON [PRIMARY];
GO

Madhivanan

Failing to plan is Planning to fail

markjrouse
Starting Member

2 Posts

Posted - 2009-10-29 : 09:02:44

Thanks for that. Can I now use that ExceptionsID as my primary key? If so how would I script that.

I now wanted to take my computed fields and use it to link to another table

Regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 02:32:50

CREATE TABLE Exceptions(
[TableID] [int] IDENTITY (1, 1) NOT NULL,
[ModuleID] [int] NOT NULL,
[ReportID] [nvarchar](30) NOT NULL,
[ExceptionID] AS (cast([TableID] as varchar(10))+'-'+cast([ModuleID] as varchar(10))+'-'+[ReportID]) persisted not null primary key ,
[Fieldx] [nvarchar](30) NOT NULL,
[Fieldy] [nvarchar](30) NOT NULL,
) ON [PRIMARY];
GO

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources