apply unique constraint in MS

I want to apply a unique constraint to an existing field in the database.
The constraints dialog box asks for a "constraint expression". I have went
through bol and google without finding a solution. I know how to create a
column with a unique constraint but no where can I find how to write the
"expression" to make an existing column unique in Management Studio.
How?
Thanks,
TCREATE TABLE Banana (Peal char(5) NOT NULL);
ALTER TABLE Banana
ADD Constraint Demonstrate UNIQUE(Peal);
INSERT Banana values('a')
INSERT Banana values('b')
INSERT Banana values('b')
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'Demonstrate'. Cannot insert
duplicate key in object 'dbo.Banana'.
The statement has been terminated.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 17:06:59 -0700, "Tina"
<TinaMSeaburn@.nospamexcite.com> wrote:

>I want to apply a unique constraint to an existing field in the database.
>The constraints dialog box asks for a "constraint expression". I have went
>through bol and google without finding a solution. I know how to create a
>column with a unique constraint but no where can I find how to write the
>"expression" to make an existing column unique in Management Studio.
>How?
>Thanks,
>T
>

apply unique constraint in MS

I want to apply a unique constraint to an existing field in the database.
The constraints dialog box asks for a "constraint expression". I have went
through bol and google without finding a solution. I know how to create a
column with a unique constraint but no where can I find how to write the
"expression" to make an existing column unique in Management Studio.
How?
Thanks,
T
CREATE TABLE Banana (Peal char(5) NOT NULL);
ALTER TABLE Banana
ADD Constraint Demonstrate UNIQUE(Peal);
INSERT Banana values('a')
INSERT Banana values('b')
INSERT Banana values('b')
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'Demonstrate'. Cannot insert
duplicate key in object 'dbo.Banana'.
The statement has been terminated.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 17:06:59 -0700, "Tina"
<TinaMSeaburn@.nospamexcite.com> wrote:

>I want to apply a unique constraint to an existing field in the database.
>The constraints dialog box asks for a "constraint expression". I have went
>through bol and google without finding a solution. I know how to create a
>column with a unique constraint but no where can I find how to write the
>"expression" to make an existing column unique in Management Studio.
>How?
>Thanks,
>T
>

apply unique constraint in MS

I want to apply a unique constraint to an existing field in the database.
The constraints dialog box asks for a "constraint expression". I have went
through bol and google without finding a solution. I know how to create a
column with a unique constraint but no where can I find how to write the
"expression" to make an existing column unique in Management Studio.
How?
Thanks,
TCREATE TABLE Banana (Peal char(5) NOT NULL);
ALTER TABLE Banana
ADD Constraint Demonstrate UNIQUE(Peal);
INSERT Banana values('a')
INSERT Banana values('b')
INSERT Banana values('b')
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'Demonstrate'. Cannot insert
duplicate key in object 'dbo.Banana'.
The statement has been terminated.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 17:06:59 -0700, "Tina"
<TinaMSeaburn@.nospamexcite.com> wrote:
>I want to apply a unique constraint to an existing field in the database.
>The constraints dialog box asks for a "constraint expression". I have went
>through bol and google without finding a solution. I know how to create a
>column with a unique constraint but no where can I find how to write the
>"expression" to make an existing column unique in Management Studio.
>How?
>Thanks,
>T
>

Application Role status

Is there a way to determine if an existing connection has invoked a specific
application role? I need to know so that I don't re-invoke it and get an
error.Hi
If you are doing this within your application then why don't you set a
boolean (class attribute) ?
If you try to set the application role a second time you will get an error
return code and Msg 2762
John
"Stefano Nicolini" wrote:

> Is there a way to determine if an existing connection has invoked a specif
ic
> application role? I need to know so that I don't re-invoke it and get an
> error.
>
>|||Yes, you can check this using the USER_NAME() function. The following
T-SQL activates the approle on the connection if it is not already
activated:
IF (SELECT USER_NAME()) <> 'ApproleName'
EXEC sp_setapprole 'ApproleName', 'password'
--Mary
On Mon, 18 Apr 2005 15:12:27 -0400, "Stefano Nicolini"
<StefanoN@.infotronics.com> wrote:

>Is there a way to determine if an existing connection has invoked a specifi
c
>application role? I need to know so that I don't re-invoke it and get an
>error.
>

Appending to a text field using UPDATE

I would like to update a field that already has data in it and I dont' want to overwrite the existing text. Here is my existing statement

UPDATE wr SET cf_notes = " + tmp_array(24) + " WHERE wr_id = " + data_temp(0)

I would like to add cf_notes + tmp_array(24) to cf_notes. Is this possible in SQL? If so, what is the correct syntax. I have tried 6 different statements and I get a compile error on every statement.

Thanks,

SBRcreate procedure spU_update_table_with_text (
@.key_field_value int,
@.text text = null)
as
declare @.txtptr binary(16), @.insert_offset int
select @.txtptr = textptr(text_field),
@.insert_offset = datalength(text_field) + 1 --or 2 if a space is needed
from your_table
where key_field = @.key_field_value
updatetext your_table.text_field @.insert_offset 0 @.text
return

Appending Records to the Existing MS SQL EXPRESS SERVER Table.

Dear All,

I am Using MS SQL EXPRESS SERVER .I have installed all tools available to Express Edition site.

Now I have created my database on this .I have imported a table from my MS ACCESS database (Using ODBC Datasource).This table contains 10,000 records ,

Now I want to append 1 more access Table(5500 records) to the existing table having same fields.

How to do this.Can any body tell me?

Thanks and Regards

mukesh

Hi mukesh,

You can append the imported records into the existing table using SQL Server import & Export Wizard.

1.Select Your database from SQL Server Management Studio

2.Right Click on Database and go to the Task->Import Data menu Item

3. SQL Server Import & Export Wizard will be open., choose ur data source. as MicrosoftAccess, and select the MDB file

4.Press Next to Move onChoose a Destination Page, and select your Database Name from dropdown

5.Press Next to Move onSpecity Table Copy or QueryPage, and selectcopy data from one or more tables or viewradio button.

6. Press Next to Move onSelect Source Table and View , Select your Source Table , and Destination Table and then pressEdit button,Column Mappings dialog box will be open, chooseAppend rows to the destination tabelradion button option ( it will append the new records with existing records, in your case ur new 5500 records will be apended with existing 10,000 records )

7Press Next, and then Press Finish.Import Process will be started.

Thanks

Best Regards,

Muhammad AKhtar Shiekh

SQL Server Import & Export Wizard

|||

Hi mukesh,

The way you imported the first table just load the second table but with a different name i.e Table2 to the same database and then you can use the query

Lets Table_1 is having 10000

Table_2 is having 5500

----------------

Insert into Table_1

Select * from Table_2

----------------

the simplest way to do the stuff...

Satya

|||

Thanks Mr.Akhhttar

But sir in management studio I can't find "Import and Export option" there r these option,"detach","shrink","backup","restore"& generate scripts.

thanks and regards

mukesh

|||

Thanks a lot,Mr.Satya

Appending data to an existing record

I am a novice when it comes to using SQL. I would like to append text to existing data in a specified column, but I can not figure out how to do it. I was trying to use an update query, but as far as I can tell this only replaces the data within the column. Any help would be appreciatedHi

Use update statement .
eg.

table1 (tablename)
id values
1 text1
2 text2
3 text3

Let the table1 contains two fields id and value. U need to append some text
to the existing values. is it ?
ok...use the following postgresql query .
update table1 set values = values || 'newtext' where id = 1

|| --> string concat operator

This will result in

table1
id values
1 text1newtext
2 text2
3 text3

If you want to update all the rows remove the 'where' condition
Try this and reply

-somaskarthic

Quote:

Originally Posted by srkartes

I am a novice when it comes to using SQL. I would like to append text to existing data in a specified column, but I can not figure out how to do it. I was trying to use an update query, but as far as I can tell this only replaces the data within the column. Any help would be appreciated

|||Hi

Here i mentioned is postgresql query , if u use some other database
please find the appropriate sting concat operator . In postgres || is the concat operator . If you use mysql or sql server , please find the correct concatenation operator.

-somaskarthic

Quote:

Originally Posted by somaskarthic

Hi

Use update statement .
eg.

table1 (tablename)
id values
1 text1
2 text2
3 text3

Let the table1 contains two fields id and value. U need to append some text
to the existing values. is it ?
ok...use the following postgresql query .
update table1 set values = values || 'newtext' where id = 1

|| --> string concat operator

This will result in

table1
id values
1 text1newtext
2 text2
3 text3

If you want to update all the rows remove the 'where' condition
Try this and reply

-somaskarthic

|||

Quote:

Originally Posted by somaskarthic

Hi

Here i mentioned is postgresql query , if u use some other database
please find the appropriate sting concat operator . In postgres || is the concat operator . If you use mysql or sql server , please find the correct concatenation operator.

-somaskarthic

This is the way:

update table1 set values = values + 'newtext' where id = 1|||Thanks guys! Worked perfectly.

Appending Backedup data During Restore Process

Dear All,

Can i append new database backup to the existing data, while restoring the backedup database? If so give me the solution. I had backups for 30 days backup and trying to restore all these bckups..

During the restore process, i had observed that the previsous data is getting deleted and new data is replaced on it (in the database). But i want to append the new data to the existing data.

Note : The backup format that i had taken for all these 30 days is of Full Backup (not differential backup)

Any solution(s) for the above stated...

Regards,Not possible using FULL BACKUP, you can use TLOG Backups to restore them on this server.

Append data to a column

I want to have a column in my table where we can append data to it without affecting the existing data. I have tried this using a column of text data type. since text is a LOB i am a bit concerned about the storage. Is there a better way to get this done? thanks in advance for your help.

use an (n)varchar(max) datatype. The LOB's are being depreciated in a future release.

Code Snippet

INSERT INTO table (id, value)

SELECT v.id, v.value + @.value

FROM table v

WHERE v.id = @.id

|||

Hi Cam,

Thanks for your reply...

I didn't understand the query you have replied.

Can you help me with another query

append data from a text file

How can I append data to an existing SQL Express table from a text file? The file contains over 50MBs of data. This will be a recurring task so naturally I would like to automate this. I am familiar with programming VC++ and VB but am not sure where to begin.

Since it's a larger file, you could use the BULK INSERT command:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp

Or you could use bcp from a command line:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp

Hope this helps,
Josh Lindenmuth