T-SQL : 2 Tabellen mit unterschiedlichen Spalten abgleichen

[Street Samurai]

Also die Sache ist simpel, leider bin ich ein kompletter T-SQL Anfänger und habe keine Ahnung wie ich überhaupt anfangen soll. Nachdem ich mich grade totgegoogelt hab wende ich mich an euch.

Ich habe 2 Tabellen

Table1 : SpalteA SpalteB SpalteC SpalteD

Table2 : SpalteA SpalteB SpalteE SpalteF

Jetzt will ich mit einer gespeicherten Prozedur folgendes machen :

Alles was in Table1 drin ist, aber nicht in Table2 soll in Table 2 rein

Hier : Spalte C und D in Table2 kopieren.

Alles was in Table2 drin ist, aber nicht in Table1 soll in Table2 gelöscht werden

Hier : Spalte E und F in Table2 löschen

• Die Datentypen der Spalten sollten beim kopieren übernommen werden.
  
• Die Datensätze die in den Tabellen stehen sind egal/ müssen nicht übernommen werden es geht nur um die Architektur der Tabelle (Table2 ist eine Auslagerungstabelle für Table1, sie müssen nur identisch sein weil sonst ein Kopiervorgang fehlerhaft ist wenn wieder irgendjemand was an Table1 rumgebastelt hat).
  

Ich danke im Vorraus jeglicher Hilfestellung, Anregung oder Lösung und Geduld mit mir T-SQL Noob.

[Jeglalf]

Was Du beschreibst ist, solange die Daten in Tabelle 2 egal sind, nur ein Kopieren der Tabelle 1 in Tabelle 2, das geht einfach über:

Drop Table table2;

commit; //falls kein autocommit an ist. Wichtig wegen Namenskonflikt!!

Select * Into table2 From table1;

Also:

Tabelle 2 löschen, dann neue Tabelle auf Basis der Tabelle 1 erstellen. So hast Du sogar die Daten von Tabelle 1 dabei.

[baba007]

diese prozedur macht keinen sinn. wofür diese kopiererei?

[Jeglalf]

Stimmt, so nen richtigen Sinn sehe ich darin auch nicht. immerhin ist eins der wichtigsten Prinzipien der Datenbanken die 1-maligkeit (abgesehen von Belegtabellen, History- und Klartext-Logs o.ä.)

Aber wenns eben so sein soll, solls so sein:)

Ahhh, hab aber noch ne Korrektur zum Code oben, zumindest bei Oracle kannste das COMMIT beim Drop table weglassen, da gedropte tabellen direkt gelöscht werden, Rollback geht da leider nicht. Wie es bei anderen systemen ist, weiß ich im mom nicht genau. Wobei du ja t-sql nutzt, also oracle in deinem fall weg fällt

Bearbeitet 16. Mai 2009 von Jeglalf

[Street Samurai]

Sinn erkennt man erst wenn man weiß , wofür eine Sinnlosigkeit verwendet wird. Denkt ihr nicht auch ?

Um Abhilfe zu schaffen folgendes :

Wir haben u.a. 2 Tabellen in unserer DB - User und User_Del

In der User Tab sind die User drin. (Wer hätte das gedacht )

und in die User_Del kommen die User , die durch einen Button auf unserer Webadmin Fläche gelöscht werden.

Nun was denkt ihr was passiert wenn man eine INSERT INTO auf eine Tabelle anwendet und der dann Spalten für mitgegebene Datensätze fehlen ?

Richtig, es funktioniert nicht.

Da leider bei uns öfters jemand mal die User Tab ändert aber die User_Del in Ruhe lässt (Gott weiß warum da nie jemand denkt) und unser System User auch automatisch löscht (Nach ner gewissen Zeit ohne Anmeldung), passiert es schnell das wir 80 User haben die sich nicht einloggen können, aber nirgends als gelöscht dokumentiert sind.

Deshalb ein Auto-Abgleich für die Auto-Löschung.

Wer mir jetzt kommt mit "Warum nicht irgend ein Ja/Nein Feld für gelöschte User und alles in eine Tabelle machen?" Die Diskussion hatte ich bereits mim Chef Antwort : "So isses und nur so wirds gemacht."

Trotzdem danke an Jeglalf , ich werd das Montag mal austesten.

[Jeglalf]

Die "so isses und so wirds gemacht"-Variante liebe ich auch ganz besonders, weil es immer so besonders konstruktiv zur Problemlösung beiträgt^^

Ansonsten finde ich es ganz witzig, dass sich Admins quasi gegen sich selbst absicher, ist aber andererseits nie falsch, da Rumbasteln an Produktivsystemen schnell zu unerfreuten Gesichtsausdrücken beim Chef führen kann.

[baba007]

Hast du dich schon mit den möglichkeiten des triggers auseinander gesetzt?

wir haben auch ohne ende usertables in diversen systemen und machen bei jedem Insert,update und delete auf der MainUserTab den trigger an, der die änderung in eine history table übernimmt. (neu + alt)

System User auch automatisch löscht

Das hatten wir früher auch mal und das hat nur arbeit gemacht. Seit dem wir einen user auf inaktiv setzen (keine anmeldung möglich) brauchen wir nur aus einem N ein Y (+new pw) und die sache läuft deutlich schneller.

Eine Tabelle zu kopieren ist weniger schön.

[Street Samurai]

Bitte zwingt mich jetzt nicht es zu erklären, es ist noch nicht ganz fertig, aber meine Problemstellung die ich hatte ist gelöst.

Für Leute die ein Ähnliches Problem hatten :

Use XXX

--Declare @QDB as varchar(200)

--Declare @ZDB as varchar(200)

Declare @QT as varchar(200)

Declare @ZT as varchar(200)

Declare @QID as int

Declare @ZID as int

Declare @Datatype as varchar(200)

Declare @DatatypeVergleicher as varchar(200)

Declare @MaxLength as int

Declare @Length as int

Declare @SQL as varchar(2000)

--Set @QDB = 'XXX'

--Set @ZDB = 'XXX'

--Set @QDB = @QDB + '.sys.objects'


Set @QT= 'XXX'

Set @ZT= 'XXX'


Set @QID=(Select object_id from sys.objects where name = @QT)

Set @ZID=(Select object_id from sys.objects where name = @ZT)



PRINT 'Abgleich von "'+@QT+'" mit "'+@ZT+'"...'

PRINT '---------------------------------------------------------------------------------------'

PRINT 'Löschen von Spalten, die in der Zieltabelle aber nicht in der Quelltabelle vorkommen...'


Declare @Feldname as Varchar(200)

Declare NameFZDel  Cursor for 


SELECT			ZSpalten.Name 

FROM			(Select [Name] from sys.syscolumns Where ID =@QID) QSpalten

RIGHT OUTER JOIN

				(Select [Name] from sys.syscolumns Where ID =@ZID) ZSpalten 

ON				QSpalten.Name = ZSpalten.Name

Where			QSpalten.Name is null


OPEN NameFZDel


FETCH NEXT FROM NameFZDel 

INTO @Feldname


WHILE @@FETCH_STATUS = 0

	BEGIN

	PRINT @Feldname

	Execute ('ALTER TABLE '+@ZT+' DROP COLUMN '+ @Feldname)

	FETCH NEXT FROM NameFZDel 

	INTO @Feldname

END 

CLOSE NameFZDel

DEALLOCATE NameFZDel


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

PRINT '--------------------------------------------------------'

PRINT 'Hinzufügen nicht vorhandener Tabellen bei Zieltabelle...'


Declare NameFZAdd  Cursor for 



SELECT			QSpalten.Name 

FROM			(Select [Name] from sys.syscolumns Where ID =@QID) QSpalten

left OUTER JOIN

				(Select [Name] from sys.syscolumns Where ID =@ZID) ZSpalten 

ON				QSpalten.Name = ZSpalten.Name

Where			ZSpalten.Name is null



OPEN NameFZAdd


FETCH NEXT FROM NameFZAdd 

INTO @Feldname


WHILE @@FETCH_STATUS = 0

	BEGIN


		SELECT     sys.columns.object_id, sys.columns.name, sys.columns.column_id, sys.columns.system_type_id, sys.types.name As Expr1, 

							  sys.columns.max_length,sys.Types.max_length, sys.columns.precision, sys.columns.is_nullable, sys.columns.is_identity

		FROM         sys.types INNER JOIN

							  sys.columns ON sys.types.user_type_id = sys.columns.user_type_id

		WHERE     (sys.columns.object_id = @QID and sys.columns.name = @Feldname)


		Set @Datatype = (SELECT sys.types.name 

		FROM         sys.types INNER JOIN

							  sys.columns ON sys.types.user_type_id = sys.columns.user_type_id

		WHERE     sys.columns.object_id = @QID and sys.columns.name = @Feldname)


		Set @MaxLength = (SELECT sys.Types.max_length

		FROM         sys.types INNER JOIN

							  sys.columns ON sys.types.user_type_id = sys.columns.user_type_id

		WHERE     sys.columns.object_id = @QID and sys.columns.name = @Feldname)

		PRINT @Feldname

		PRINT @Datatype

		If @MaxLength = 8000

			Begin

				Set @Length = (SELECT sys.Columns.max_length

				FROM         sys.types INNER JOIN

							 sys.columns ON sys.types.user_type_id = sys.columns.user_type_id

				WHERE     sys.columns.object_id = @QID and sys.columns.name = @Feldname)



				PRINT 'Length: ' + Cast(@Length as Varchar(20))

			Execute ('ALTER TABLE '+@ZT+' ADD '+@Feldname+' '+@Datatype+'('+@Length+')')

			End

			Else

			Begin

			Execute('ALTER TABLE '+@ZT+' ADD '+@Feldname+' '+@Datatype)

			End


	PRINT 'MaxLength: ' + Cast(@MaxLength as Varchar(20))






	FETCH NEXT FROM NameFZAdd

	INTO @Feldname

END 

CLOSE NameFZAdd

DEALLOCATE NameFZAdd


--Datentypabgleich

PRINT '----------------------------'

Print 'Abgleichen der Datentypen...'

Declare NameFZDatatypeAbgleich  Cursor for 



SELECT			QSpalten.Name 

FROM			(Select [Name] from sys.syscolumns Where ID =@QID) QSpalten

 INNER JOIN

				(Select [Name] from sys.syscolumns Where ID =@ZID) ZSpalten 

ON				QSpalten.Name = ZSpalten.Name



OPEN NameFZDatatypeAbgleich


FETCH NEXT FROM NameFZDatatypeAbgleich

INTO @Feldname


WHILE @@FETCH_STATUS = 0

	BEGIN


	Set @Datatype = (SELECT sys.types.name 

		FROM         sys.types INNER JOIN

							  sys.columns ON sys.types.user_type_id = sys.columns.user_type_id

		WHERE     sys.columns.object_id = @QID and sys.columns.name = @Feldname)


	Set @DatatypeVergleicher = (SELECT sys.types.name 

		FROM         sys.types INNER JOIN

							  sys.columns ON sys.types.user_type_id = sys.columns.user_type_id

		WHERE     sys.columns.object_id = @ZID and sys.columns.name = @Feldname)


	Set @MaxLength = (SELECT sys.Types.max_length

		FROM         sys.types INNER JOIN

							  sys.columns ON sys.types.user_type_id = sys.columns.user_type_id

		WHERE     sys.columns.object_id = @QID and sys.columns.name = @Feldname)




	if  @Datatype != @DatatypeVergleicher

			Begin

				If @MaxLength = 8000


				Begin

					Set @Length = (SELECT sys.Columns.max_length

					FROM         sys.types INNER JOIN

								 sys.columns ON sys.types.user_type_id = sys.columns.user_type_id

					WHERE     sys.columns.object_id = @QID and sys.columns.name = @Feldname)


					PRINT 'Length: ' + Cast(@Length as Varchar(20))


					Execute ('ALTER TABLE '+@ZT+' ALTER COLUMN '+@Feldname+' '+@Datatype+'('+@Length+')')

				End


				Else


				Begin

					Execute('ALTER TABLE '+@ZT+' ALTER COLUMN '+@Feldname+' '+@Datatype)

				End

			Print @Feldname

			Print @Datatype

			Print @DatatypeVergleicher

			End



	FETCH NEXT FROM NameFZDatatypeAbgleich 

	INTO @Feldname

END 

CLOSE NameFZDatatypeAbgleich

DEALLOCATE NameFZDatatypeAbgleich