Posts
460
Comments
6497
Trackbacks
0
Eine Tabelle in eine temporäre Tabelle kopieren

Es gibt mindestens 2 Wege eine Tabelle in eine temporäre Tabelle zu kopieren:

einfacher Weg: 

DECLARE @tabelle nvarchar(100)
DECLARE @sql nvarchar(1000)

set @tabelle = "TabellenName"

set @sql = 'select * into ##temp from ' + @tabelle 
exec(@sql) 

   

umständlicher Weg: 

-- eine leere temporäre Tabelle anlegen
if object_id(N'tempdb..#temp') is not null
begin
   drop table #temp
end

create table #temp(id int)  

DECLARE TabellenFelder CURSOR FOR 
select  column_name, data_type, character_maximum_length,is_nullable 
from    information_schema.columns
where   table_name = @Tabelle
order   by ordinal_position

OPEN TabellenFelder 

FETCH NEXT FROM TabellenFelder INTO @column_name, @data_type, @character_maximum_length, @is_nullable 

WHILE @@FETCH_STATUS =
      BEGIN 
            --neue Spalte hinzufügen 
            set @sql = 'alter table #temp add ' + @column_name + ' ' 
            set @sql = @sql + @data_type 
            if @data_type ='nvarchar'  
            set @sql = @sql + '(' + @character_maximum_length +')' 
            exec(@sql)

            FETCH NEXT FROM TabellenFelder INTO @column_name, @data_type, @character_maximum_length, @is_nullable  
      END

CLOSE TabellenFelder
DEALLOCATE TabellenFelder

--die erste Spalte wieder löschen
set @sql = 'alter table #temp drop column id'
exec(@sql)

-- jetzt die Tabelle in die temporäre Tabelle kopieren
set @sql = 'insert into #temp select * from ' + @Tabelle
exec(@sql) 

posted on Wednesday, May 11, 2011 11:57 AM Print
Comments
Gravatar
# re: Eine Tabelle in eine temporäre Tabelle kopieren
brad
2/18/2016 7:57 PM
Drawing a table through program needs to have ground preparation of giving a note of how many columns and rows it does exist according to essay writing services. Here i am able to see a different program on copying it to another. All it needs a copy command i guess.
Gravatar
# MSBSHSE SSC Results
SHAWN
12/22/2016 6:08 AM
Check Out Maharashtra Board SSC Result 2017.
MSBSHSE SSC Results
Gravatar
# re: Eine Tabelle in eine temporäre Tabelle kopieren
Bob
2/8/2017 5:07 AM
It’s a great pleasure to know about the recent graduation ceremony at Unity High School. In the last academic year ending 110 students were gone out from Unity High School. Keep sharing more posts on this blog! ipad 2 wholesale

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 3 and 2 and type the answer here: