January 10, 2013 at 2:43 am
Hi
I often transfer data from one db to another (most 2008 but also 2005 sometimes), i have to generate scripts every time, cos the db is very dynamic, so i havew been using:
declare @SourceDB varchar(11)
declare @sql varchar(128)
select @SourceDB = 'zzzzzzzz'
create table #tables(name varchar(128))
select @sql = 'insert #tables select TABLE_NAME from ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
exec (@sql)
create table #SpaceUsed (name varchar(128), rows varchar(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))
declare @name varchar(128)
select @name = ''
while exists (select * from #tables where name > @name)
begin
select @name = min(name) from #tables where name > @name
select @sql = 'exec ' + @SourceDB + '..sp_executesql N''insert #SpaceUsed exec sp_spaceused ' + @name + ''''
exec (@sql)
end
--select 'Select * From ' + name + char(10)+char(13) from #SpaceUsed where rows > 0
select 'insert into XXX.dbo.' + name + char(10)+ 'Select * From YYY.dbo.' + name + char(10)+char(13) from #SpaceUsed where rows > 0
drop table #tables
drop table #SpaceUsed
But that is not goog enough, as i need to get the column names instead of * and i shall not have an identity column out if one exists, so is it possible to modify the above so i get
insert into table (col1,col2...)
select col1,col2...
instead of insert into table
select * from table
Thanks in advance
Betina
January 10, 2013 at 4:46 am
How about something like this:
-- change to Text Results (press Ctrl+T) before running
-- change this to whichever database your selecting from
USE [YourSourceDatabase];
-- change this to the destination database name
DECLARE @targetDatabase SYSNAME = N'SomeDatabase';
SET NOCOUNT ON;
WITH cte
AS (
SELECT OBJECT_SCHEMA_NAME(p.object_id) AS ObjectSchemaName,
OBJECT_NAME(p.object_id) AS ObjectName,
p.object_id,
STUFF((
SELECT DISTINCT
N',' + QUOTENAME(c.name)
FROM sys.columns c
WHERE p.object_id = c.object_id
FOR
XML PATH('')
), 1, 1, '') AS ColumnList,
SUM(p.rows) AS rows
FROM sys.partitions p
JOIN sys.tables t ON p.object_id = t.object_id
WHERE p.index_id IN (0, 1)
AND t.type_desc = 'USER_TABLE'
AND t.is_ms_shipped = 0
GROUP BY p.object_id
HAVING SUM(p.rows) > 0
)
SELECT N'INSERT INTO ' + QUOTENAME(@targetDatabase) + N'.' + QUOTENAME(ObjectSchemaName) + N'.' + QUOTENAME(ObjectName) + N'(' + ColumnList + N')' + CHAR(13)
+ CHAR(10) + CHAR(9)+ 'SELECT ' + ColumnList + N' FROM ' + QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(ObjectSchemaName) + N'.' + QUOTENAME(ObjectName)
+ ';' + CHAR(13) + CHAR(10)
FROM cte
ORDER BY ObjectSchemaName,
ObjectName;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 11, 2013 at 3:12 am
Thank you very much, that brings me a lot closer to my goal 🙂
I will try to see if i can get the identity columns out.
But I just get 128 chars at each line, so i'll see if thats an option i can mingle with in the management studio.
January 11, 2013 at 4:45 am
beyan (1/11/2013)
I will try to see if i can get the identity columns out.
for indentity columns , check the column is_identity in sys.columns table , its a bit wise column
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 11, 2013 at 4:48 am
I have added
AND c.is_identity = 0
to the WHERE clause in the Stuff function and that did the trick.
Thanks for the help all 🙂
January 11, 2013 at 4:53 am
You're quite welcome.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply