Discussion :

[[DreaMs]]

Salut,

J'essai de recopier une table comme ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[rudib]

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 ?

[[DreaMs]]

Oui j'ai un message d'erreur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

[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 ?)

[rudib]

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.

[[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.

[ZERS]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[SQLpro]

Passez par du SQL dynamique :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

[clb01]

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

[rudib]

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.

[clb01]

Ok merci

[Lancyo]

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

[rudib]

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

[Lancyo]

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é.