How Can I Programmatically Remove Identity from a Table?

I am copying a view to another location as a table. SQL 2000 is automaticall
y
setting the tables ID as an Indentity column. I do not want this to happen.
It may be because the original table from whence the view works off has
identity turned on. However the copy of the table I do not wish to have the
indentity turned on as I cannot do blanket inserts with this option on.
I already know what to do from the SQL Manager wizard however I require to
do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
kinds examples on how to create an indentity insert column but not one
example on how to remove the indentity insert from a table.I dont find remove ID property.
But I traced using Profiler, SQL Server copy a table, create a table remove
ID
,Insert data using INSERT SELECT and last sp_rename.
"Jamie Carper"?? ??? ??:

> I am copying a view to another location as a table. SQL 2000 is automatica
lly
> setting the tables ID as an Indentity column. I do not want this to happen
.
> It may be because the original table from whence the view works off has
> identity turned on. However the copy of the table I do not wish to have th
e
> indentity turned on as I cannot do blanket inserts with this option on.
> I already know what to do from the SQL Manager wizard however I require to
> do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
> kinds examples on how to create an indentity insert column but not one
> example on how to remove the indentity insert from a table.|||You might consider changing your view to CAST the IDENTITY column so that
the IDENTITY property won't propagate to new tables:
ALTER VIEW View1
AS
SELECT
CAST(Col1 AS int) AS Col1
FROM MyTable
GO
You can't remove IDENTITY from an existing column or can add IDENTITY to an
existing one. If you don't want to recreate the table, you can use a script
like the one below to migrate data to a new non-identity column.
ALTER TABLE MyTable
ADD Col1_new int NOT NULL
--default constraint needed of NOT NULL
CONSTRAINT DF_MyTable_col1 DEFAULT 0
UPDATE MyTable
SET Col1_new = Col1
ALTER TABLE MyTable
DROP COLUMN Col1
EXEC sp_rename 'MyTable.Col1_new', 'Col1'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jamie Carper" <JamieCarper@.discussions.microsoft.com> wrote in message
news:196AC5D9-C450-4BB7-B057-E1F1C6B8628D@.microsoft.com...
>I am copying a view to another location as a table. SQL 2000 is
>automatically
> setting the tables ID as an Indentity column. I do not want this to
> happen.
> It may be because the original table from whence the view works off has
> identity turned on. However the copy of the table I do not wish to have
> the
> indentity turned on as I cannot do blanket inserts with this option on.
> I already know what to do from the SQL Manager wizard however I require to
> do the same thing using an ALTER TABLE ALTER COLUMN approach. I find all
> kinds examples on how to create an indentity insert column but not one
> example on how to remove the indentity insert from a table.|||if I understood correctly, and you want to remove identity insert
then you should be using this switch.
SET ENABLE_IDENTITY_INSERT tbl_name,1
--don't remember the syntax exactly.
or if I have completely misunderstood the question, forgive me :)|||Hey Dan,
Thanks for the tip!
CASTing the Identity column did the trick.
"Dan Guzman" wrote:

> You might consider changing your view to CAST the IDENTITY column so that
> the IDENTITY property won't propagate to new tables:
>
Thanks again,
Jamie|||wrong syntax.. this is the one :)
SET IDENTITY_INSERT table1 ON
"Omnibuzz" wrote:

> if I understood correctly, and you want to remove identity insert
> then you should be using this switch.
> SET ENABLE_IDENTITY_INSERT tbl_name,1
> --don't remember the syntax exactly.
> or if I have completely misunderstood the question, forgive me :)

How Can I Link to a Table in a Different Database?

Hi all,

We have lookup tables we share on 6 database servers. We keep copying these very large tables to every database we use on each server, obviously a great waste of space. We don't mind copying these tables onto each of the servers but we want to be able to link to these tables from each database from within our servers. How is this done? Thanks in advance.

ddavesp_addlinkedserver?|||Brett,

The Sr. Analyst here mentioned that if we used a function to go across the network he was concerned about increased network traffic. Does what you suggest play into that concern?

ddave|||Brett,

The Sr. Analyst here mentioned that if we used a function to go across the network he was concerned about increased network traffic. Does what you suggest play into that concern?

ddave

You could always test it with one...and set up profiler...

I guess it all depends on how the sprocs are written...

Ask him to check out the Network when he's copying all that data to the 6 servers...I would think that's a lot of traffic...

And what about data syncronicity? (Is that a real word?)

I guess you could set up replication and/or build your own with triggers...

One of the databases would need to be a publisher and the rest subscribers...

I've never implemented a production level linked server strategy...but from my tests, I haven't seen a problem...

6 servers...you must be getting a lot of hits a day....

how big are the code tables and the db in general?|||This looks like what I need to do as well. Joins on two tables in different DBs on different servers. Will this work?|||I have one caution for all of this. I do not think you can set up an alias for a linked server, unless maybe in DNS. If you move a database from one server to another, you could end up with a large amount of re-coding. This may not be a problem for your shop, but it is something to watch for.|||The databases are anywhere from 400MB to 22,000 MB. I mean I look under properties and I see 400 MB, a couple at about 1,000 MB, another at 7,000 MB and so forth. It is not for a web site. It is an internal system for medical data. I would say an average table we read off of is 600,000 records with 90-110 fields. We don't have an in-house DBA as you may have guessed.

ddave

"6 servers...you must be getting a lot of hits a day....

how big are the code tables and the db in general?"|||Actually,

this seemed to work:

SELECT TOP 100 *
FROM pfc_Premera_BC_Rx..pfc pfc1
LEFT JOIN SQL_Server_Training_DB..pfc
on pfc1.pfc_pkid = pfc.pfc_pkid
ORDER BY pfc1.pfc_pkid

ddave