May 10, 2011 at 3:03 am
We are moving our interbase database to a new MS sql 2008 server.
We used a tool to create the database en copy the data.
This worked.
But now we want to add an identity to the tables.
Before we had a field that was used like an identity in interbase. So we have to use the values again.
But how can we add the identities to +-600 tables.
So manual work is excluded 🙂
May 10, 2011 at 7:17 am
something like this may get you started in the direction you are heading.
select 'alter table ' + name + ' add ' + name + 'ID int identity not null;'
from sys.tables
order by name
You can add a where clause if there are specific tables you want to exclude. You can also change the name of the new column. Here it is just ID. This will create your identity column and populate them with values. Just run this in SSMS and the copy and paste output and paste it into a new query window.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 10, 2011 at 7:22 am
but this will create a new column in the table.
There is already an column wich was a "identity" in interbase.
As example, the column is named diskref, and the max value is 1000.
Now i want to add the identity, so the next insert diskref will be 1001.
May 10, 2011 at 7:24 am
Cool script but I think that's only 1% of the problem. Those new identities need to be linked to the child tables. That part will have to be done manually unless all the PK / FK are already created... and even then doing a script is a big chanllenge.
Maybe someone has a ready made elegant solution but I certainly don't.
May 10, 2011 at 7:33 am
Given the challenge of needing to keep the existing values it may actually be easier and quicker to just edit the tables in SSMS over writing a script.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 10, 2011 at 7:35 am
manually change more then 600 tables, multiple times
then we need to hire some monkeys 🙂
May 10, 2011 at 7:36 am
Upgrading is never an easy project... no matter how small.
May 10, 2011 at 7:39 am
LOL. Yeah if this is something you are going to have to do over and over then maybe you need to look at the process that is creating your new table initially. If it is a one time thing you will be surprised that it doesn't really take as long as it sounds like it might. Once you get going you kind of get a rhythm going and you can crank this out in a reasonably short time. Like Ninja said, upgrading is painful.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 10, 2011 at 8:11 am
You cannot change an existing column to identity.
You can create a new table with the identity attribute set on the column you want and copy the data from the old table to the new table.
If you have an existing database, it might be easier to create a new, empty database with the correct identity settings, and copy the data over one table at a time in the correct order so that you do not have FK constraint violations.
Example:
-- Original table
create table MYTest
(
IDint not null primary key clustered,
xint not null
)
BEGIN TRANSACTION
GO
-- Replacement table
CREATE TABLE dbo.Tmp_MYTest
(
ID int NOT NULL IDENTITY (1, 1),
x int NOT NULL
) ON [PRIMARY]
GO
-- Allow existing values to be inserted into identity column
SET IDENTITY_INSERT dbo.Tmp_MYTest ON
GO
IF EXISTS(SELECT * FROM dbo.MYTest)
EXEC('INSERT INTO dbo.Tmp_MYTest (ID, x)
SELECT ID, x FROM dbo.MYTest WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_MYTest OFF
GO
DROP TABLE dbo.MYTest
GO
EXECUTE sp_rename N'dbo.Tmp_MYTest', N'MYTest', 'OBJECT'
GO
ALTER TABLE dbo.MYTest ADD CONSTRAINT
PK__MYTest PRIMARY KEY CLUSTERED
(
ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
May 10, 2011 at 10:26 am
i think this is the best option, but here we have to do also a lot of manual work.
Does somebody has a script to create a table from another table but then including the new identity
May 10, 2011 at 10:33 am
geert.massa (5/10/2011)
i think this is the best option, but here we have to do also a lot of manual work.Does somebody has a script to create a table from another table but then including the new identity
Yes microsfot does. It's called edit table in SSMS.
You're asking for a miracle here. There are 100s of things to consider when dropping / creating a table. There's no need to redo that code. Just hit edit save for 1 day and be done with it already.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply