script for insert data sql statement

Some times we need insert into select statement for tables from one database to other database. Here is the script which will create the script for this process.



This can be useful who are using SQL Server 2000 or SQL Server 2005.
For those who are using SQL Server 2008 please look at here

Insert Script with Data SQL server 2008

Tables having idenity key

select 'set identity_insert '+ name + ' on ' + char(9)+ char(13) + 'insert into [' + name +']('+
stuff((select ','+name as [text()] from sys.columns where object_id =t.object_id
for xml path('')) ,1,1,'' )
+') select ' + stuff((select ','+name as [text()] from sys.columns where object_id =t.object_id
for xml path('')) ,1,1,'' ) +' from [linked server].[linked db].[dbo].[' + name +'] with (nolock)' +
char(9)+char(13) + 'set identity_insert ' + name + ' off '
from
sys.tables t
where object_id in (
select object_id from sys.identity_columns)

tables not having identity_key
select 'insert into '+ name +
' select * from [linked server].[linked db].[dbo].[' + name +'] with (nolock)'
from
sys.tables t
where object_id not in (
select object_id from sys.identity_columns)