If I could explain the problem..........

In SQL database we need to concatenate 2 fields to display in one and turn them into an email address in the following format

joe.bloggs@.company.co.uk

They are the following: forename & surname

The expression will require a . to be added between the forename & surname and @.company.co.uk at the end.

Anyone help...... ?This should work:

SELECT forename || '.' || surname || '@.company.co.uk' AS [name]
FROM [table]
[WHERE ...];

identity on / off

does anyone know syntext to turn on / off Identity field? I usually do this
via EnterpriseMgr but I want to do this via SQL.
You can't 'turn' it on and off.
You can add a new column, transfer data if appropriate, and remove the old
column.
Enterprise Mangler creates a new table and transfers the data from the old
table to the new table. (One of the many reasons that EM is NOT a good tool
to use to manage your databases.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"David Kwon" <tjk_guard-msnewsgp@.yahoo.com> wrote in message
news:e0MFUC86GHA.1188@.TK2MSFTNGP05.phx.gbl...
> does anyone know syntext to turn on / off Identity field? I usually do
> this
> via EnterpriseMgr but I want to do this via SQL.
>
>
|||Hi,
To add on, you can not switch of the Identity property using a TSQL. But you
could reseed the identity
value using DBCC CHECKIDENT command.While resseding ensure that primary key
violation will
not happen incase the identity column is associated with PKey.
Thanks
Hari
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23yU32r86GHA.4604@.TK2MSFTNGP03.phx.gbl...
> You can't 'turn' it on and off.
> You can add a new column, transfer data if appropriate, and remove the old
> column.
> Enterprise Mangler creates a new table and transfers the data from the old
> table to the new table. (One of the many reasons that EM is NOT a good
> tool to use to manage your databases.)
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> lue "David Kwon" <tjk_guard-msnewsgp@.yahoo.com> wrote in message
> news:e0MFUC86GHA.1188@.TK2MSFTNGP05.phx.gbl...
>
|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23yU32r86GHA.4604@.TK2MSFTNGP03.phx.gbl...
> You can't 'turn' it on and off.
Unless I'm misunderstanding the question, yes you can turn it on and off. I
do it this way:
set identity_insert MyTable on
insert into MyTable
(MyIdentityColumn, MyColumn2, etc... )
values
(Value1, etc... )
set identity_insert MyTable off
--Rob Roberts

Identity Insert

How do I turn on IDENTITY_INSERT for a table in SQL 2005?
--
Regards,
Fred Chateau
fchateauAtComcastDotNetSET IDENTITY_INSERT yourschema.yourtable ON
GO
eg
SET IDENTITY_INSERT dbo.Customers ON
GO
HTH,
Paul Ibison
(btw BOL has all of this in good detail).

Identity Insert

How do I turn on IDENTITY_INSERT for a table in SQL 2005?
Regards,
Fred Chateau
fchateauAtComcastDotNet
SET IDENTITY_INSERT yourschema.yourtable ON
GO
eg
SET IDENTITY_INSERT dbo.Customers ON
GO
HTH,
Paul Ibison
(btw BOL has all of this in good detail).

Identity Insert

How do I turn on IDENTITY_INSERT for a table in SQL 2005?
Regards,
Fred Chateau
fchateauAtComcastDotNetSET IDENTITY_INSERT yourschema.yourtable ON
GO
eg
SET IDENTITY_INSERT dbo.Customers ON
GO
HTH,
Paul Ibison
(btw BOL has all of this in good detail).

Identity column without script?

I have a database that is already created and I would like to change all the row id columns (which are currently bigint fields) and turn them into autonumbering Identity fields. Is there a way to do this through the enterprise manager or do I need to recreate all tables in the database using a script that creates identity columns in CREATE_TABLE and then import existing data into it?

Thanks in advance!USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 bigint, col2 char(2))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'
GO

-- This is what EM will Do

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_myTable99
(
Col1 int NOT NULL IDENTITY (1, 1),
col2 char(2) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 ON
GO
IF EXISTS(SELECT * FROM dbo.myTable99)
EXEC('INSERT INTO dbo.Tmp_myTable99 (Col1, col2)
SELECT CONVERT(int, Col1), col2 FROM dbo.myTable99 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 OFF
GO
DROP TABLE dbo.myTable99
GO
EXECUTE sp_rename N'dbo.Tmp_myTable99', N'myTable99', 'OBJECT'
GO
COMMIT
GO

SELECT * FROM myTable99
GO

sp_help myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Yes, but I wanted to do it through the enterprise manager (the database is already set up and populated, but not in production yet.). I was hoping maybe all I had to do was enter a formula in the design view for each table, and then do inserts in code without having to enter that field in my inserts.

Can this be done?|||if you are asking if you can add an identity to a column after the table has been created and the data exists, the answer is yes

open the enterprise manager
right click the table that you want to modify
Right click the table and select Design Table
in the grid at the top choose the column you want to add the identity to
and at the bottom select the following properties

identity = yes
seed = this is the initial value which in the case of existing data is the highest value placed in the column. os for example if your last value in the col was 234 the identityseed would be 234
increment the number that you want to increase the identity by. usually 1

when you insert another row to this table the identity will add 1 to the seed and give you 235 as your next value.

is this what you wanted? :eek:|||That's exactly what I wanted! Thanks!!

Identity Column

Hi,

I need to turn off Identity of a column which already has records, i have tried SET IDENTITY_INSERT TableName OFF but it is not working. can any one please help me.

Do you mean turn it off permanently or what exactly? What are your exact, particular circumstances?

|||
To turn off identity temporarily and allow explicit inserts to the table, you have to issue SET IDENTITY_INSERT TableName ON. Note that you can have only one table with this property set to ON in a session. This will be in effect until you issue
SET IDENTITY_INSERT TableName OFF to come back to the identity.

If you wish to permanently delete the identity property then you may have to go for other solution, like adding a new column without identity to the table, insert the values for the existing data, delete the old column with identity property and rename the column back to the original name. Or you can create a new table with almost same structute except the idenity and export the data, drop the old table, rename the table name.

As a sidenote, why do you want to remove the identity property. As you can see, it can be done but its pain to do it.