Copier une table avec IDENTITY_INSERT

Salut,

J'essai de recopier une table comme ceci:

Code:

---

1 2 3

SET IDENTITY_INSERT Table1 ON; INSERT INTO Table1 SELECT * FROM Table2; SET IDENTITY_INSERT Table1 OFF;

---

Mais ça ne fonctionne pas.
Si j'enlève l'autoincrément à la main sur la table la ligne de copie fonctionne.

Comment faire avec des requêtes SQL ?

D'avance merci

Bonjour,

Comment sais-tu que ça ne marche pas ? Message d'erreur ? Lequel ?
L'utilisateur qui exécute cela a-t-il des droits au moins ddladmin sur la base ?

Oui j'ai un message d'erreur:

Code:

---

[Microsoft][ODBC SQL Server Driver][SQL Server]Une valeur explicite e la colonne identité de la table 'Base1..Table1' ne peut être spécifiée que si on fait appel à la liste des colonne et si IDENTITY_INSERT est ON.

---

(ma table se situe sur une autre base).

Pour ce qui est des droits pour l'instant j'effectue mes tests avec le l'utilisateur "sa" qui a tous les droits.

(question subsidiaire comment on fait si la base est carrément sur un autre serveur ?)

Bon, eh bien comme l'indique le message d'erreur, il faut que tu fasses ton INSERT en mentionnant explicitement tes colonnes, ce que tu devrais d'ailleurs toujours faire.

Pour un autre serveur, tu peux utiliser le mécanimse des serveurs liés. Regarde serveur lié ou linked server dans l'aide en ligne.

bah lister toutes les colonnes c'est bien gentil mais si j'enlève l'histoire d'autoincrémentation ça marche sans ça.

Et vu que je dois faire le travail pour presque 50 tables et que chacune possède de nombreux champs j'ai pas envie de perdre du temps avec ça (ou alors on peut lister les champs avec une requête de manière générique mais ça je ne sais pas faire).

merci pour les réponses en tous les cas.

Citation:

---

Envoyé par [DreaMs]

bah lister toutes les colonnes c'est bien gentil mais si j'enlève l'histoire d'autoincrémentation ça marche sans ça.

Et vu que je dois faire le travail pour presque 50 tables et que chacune possède de nombreux champs j'ai pas envie de perdre du temps avec ça (ou alors on peut lister les champs avec une requête de manière générique mais ça je ne sais pas faire).

merci pour les réponses en tous les cas.

---

Salut,

en fait c'est plus simple que cela... En tout cas à comprendre l'erreur ; faire un "insert into... select *" sur des tables qui ont une colonne IDENTITY est une erreur... Car, en schématisant, tu demandes à mettre la même valeur identity dans ta table destination que dans ta table source. Or, ceci est faux conceptuellement

C'est pour cela que SQL server te dit de lister explicitement toutes les colonnes des tables, sauf celle(s) identity.

POur faire ce genre de script automatiquement, c'est assez simple. Il suffit de faire un curseur sur sysobjects puis de faire une jointures sur syscolumns et de récupérer le nom des colone dans Xtype <> identity.

Par exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

create table source (idd int not null identity(1,1), val varchar(10) not null, val3 varchar(10) not null) create table dest (idi int not null identity(1,1), val5 varchar(10) not null, val10 varchar(10) not null)   insert into source select 'test', 'avant'       declare @sql nvarchar(4000) declare @col sysname set @sql = 'INSERT INTO dest SELECT '   declare cur_col cursor for select c.name from syscolumns c inner join sysobjects o on o.id = c.id where o.name = 'source' and c.xtype <>56   open cur_col fetch next from cur_col into @col while (@@FETCH_STATUS =0) BEGIN SET @SQL = @SQL + @col +', ' fetch next from cur_col into @col END close cur_col deallocate cur_col set @SQL = LEFT(@SQL, LEN(@SQL) -1) + ' FROM source'   exec (@sql)   select * from dest

---

CDlt

Passez par du SQL dynamique :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

DECLARE @SQL VARCHAR(8000)   DECLARE C CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES   OPEN C   FECTH C INTO @SQL   WHILE @@FECTH_STATUS = 0 BEGIN SET @SQL = 'SET IDENTITY_INSERT ' + TABLE_SCHEMA + '.' + TABLE_NAME +' ON; ' + 'INSERT INTO '+ TABLE_SCHEMA + '.' + TABLE_NAME + ' SELECT * FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME +'2;' + 'SET IDENTITY_INSERT ' + TABLE_SCHEMA + '.' + TABLE_NAME +' OFF; ' EXEC (SQL)   FECTH C INTO @SQL   END   CLOSE C   DEALLOCATE C

---

A +

Bonjour,
Dans le même ordre d'idée, je dois importer une base Oracle vers Mssql 2000 par l'assistant DTS. Ce sont strictement les mêmes base de données (tables et colonnes).
Il faudrait que je puisse inhiber les colonnes Identity sous SQL2000 afin de récupérer les valeurs d'Oracle et comme il y a + de 350 tables, je ne veux pas aller dans les options d'importation pour chacune d'entre elles.

J'ai essayé de modifier le type de colonnes des tables SQL 200 au préalable:

set @DynamicSQL = 'SET IDENTITY_INSERT '+ @table + ' ON '
execute (@DynamicSQL)

Mais cela n'est pas appliqué lors du DTS.

Quelqu'un aurat-il une idée ?
D'avance un grand merci

Il n'y a pas de moyen d'enlever par script la propriété identity. Tu peux tout importer dans une base de donnée temporaire, et ensuite faire un insert dans tes tables destination en utilisant le SQL dynamique fourni par SQLPro.

:(
Ok merci

Bonjour,

Je relance le sujet car je ne comprends pas tout dans le script de SQLPro.

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

Dans SELECT il met un nom de table et pas un nom de colonne. Est-ce normale?

Que veux-t-il dire dans l'instruction?
FROM INFORMATION_SCHEMA.TABLES

Merci.

Ps: Je suis débutant en SQL server 2005

Ce sont des vues d'information de schéma, définies par la norme SQL. Une règle des bases de données relationnelles, est de maintenir leurs métadonnées dans des tables, comme des données, ce qui permet des requêtes sur les métadonnées.

http://sqlserver.developpez.com/faq/?page=Schema

Merci.

En fait il y avait une erreur de frappe pour le FETCH.
une fois corrigé j'ai pu comprendre le select.

J'aurai du enlever ma question.
Désolé.:oops: