Best Way to Move Data

[ksaab2002]

Hello,

I would like to make a structural copy of an existing table and populate it with data from another table - is there an easy way to do this?

The tables are identical except that one allows nulls and another does not -- I am trying to in essence get rid of the allow nulls and the <NULL> values...

Thanks!

 

[mrdenny]

Try this.

select *
into {NewTable}
from {OldTable}

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[ksaab2002]

Will that get rid of the <NULL> values in my existing table? (If the new table does not have "Allow Nulls" checked) or will it bring in all the <NULL> values?

 

[SajidAttar]

Hi in this code I have used

ExistingTable = your source table
NewTable = new table with the structure of existingtable
OtherTable = Table from which you want to get the data

/* Creating new table */
declare @name varchar(255) , @DtType varchar(100), @Length int, @SQLStr nvarchar(4000)

Declare curFieldNames cursor for 
Select B.[name] as FieldName, type_name(B.xusertype) as DataType , convert(int, B.length) as Length from SysObjects A , SysColumns B where A.id = b.id and A.Name = '<ExistingTable>' Order By colid

set @sqlstr  = ''
open curFieldNames
fetch next from curFieldNames into @Name, @DTType, @Length
while @@Fetch_Status = 0
begin

if len(ltrim(rtrim(@SQLSTR))) > 0
	set @SQLSTR = @SQLSTR + ', '

if @DTType = 'datetime' or @Dttype = 'int'
begin
	set @SQLStr = @SQLStr + @Name + ' ' + @DTType 
end
else
begin
	set @SQLStr = @SQLStr + @Name + ' ' + @DTType + ' (' + convert( varchar, @length) + ')'
end
fetch next from curFieldNames into @Name, @DTType, @Length
end

Close curFieldNames
deallocate curFieldNames

set @SQLStr = 'Create table NewTable (' + @SQLStr + ')'
print @SQLSTR
execute (@SQLSTR)

/* inserting values from  other table which is identical */
insert into newTable select * from othertable

hope this will fulfill your requirement.



Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell