Sunday, 16 December 2007

SQL data copy tip

I had a bug I was fixing and the I couldn't test it on our local version because the data in one table was massively different. This table was just a lookup table which makes thing a bit easier but I had to avoid a full database backup as it was massive and this issue was time sensitive. To extract the data I simply constructed a select statement which generated lots of insert statements for me! e.g.

select 'insert into tblBlah values(' + col1 + ',' + col2....

and then copied the results and ran it locally.

This worked great but was a bit time consuming to construct the above statement. So when I had a bit more time I wrote a bit of SQL which generated the code above just from the table name. Now this code wont be perfect (I only tested it on one pretty big table) so if you have any issues with it then let me know and Ill update id.

Also note that this should only really be used on lookup tables and tables without foreign key references because they wont be correct.

Hopefully this will save someone some time! Its also worth noting that RedGate have a product called SQL data copy (http://www.red-gate.com/products/SQL_Data_Compare/index.htm) and if you are doing this kind of thing regularly then I would highly recommend it.

Anyway, here is the code:
/**********************************************
* Author= Ross Dargan (blog.the-dargans.co.uk *
* This Sql can be used to copy data from one *
* table into another. It strips out the *
* identity columns and computed columns *
* Use this code however you like, but if you *
* republish it please referance me as the *
* original Author. Enjoy! *
**********************************************/
DECLARE @TableFromName varchar(50)
DECLARE @TableToName varchar(50)
DECLARE @WhereStatement varchar(max)
/*The table you are copying the data from*/
SET @TableFromName = 'tblMyTableFrom'
/*The table you are copying the data to*/
SET @TableToName = 'tblMyTableTo'
/*The where statement used to limit the data copied (set to null if you dont want a limit)*/
set @WhereStatement = 'ID<1000'

/************The actual Code**************/
--Loop through all of the columns so we can generate the sql
DECLARE updatecols cursor FAST_FORWARD FOR
SELECT sc.name,st.name
FROM sysobjects so
INNER JOIN syscolumns sc
ON so.id= sc.id
INNER JOIN systypes st
ON sc.xtype = st.xusertype
WHERE so.Name = @TableFromName
AND iscomputed = 0
AND colstat = 0
OPEN updatecols
DECLARE @Col varchar(250)
DECLARE @Type varchar(50)
DECLARE @Sql varchar(max)
SET @Sql = 'select '

FETCH NEXT FROM updatecols INTO @Col,@Type
WHILE @@FETCH_STATUS <> -1
BEGIN
IF(@Type='varchar')
BEGIN --This is a varchar so casting isnt required
SET @Sql = @sql +'[' + @Col + '], '
END
ELSE
BEGIN --Have to cast all other types though.
SET @Sql = @sql + 'cast ([' + @Col + '] as varchar), '
END
FETCH NEXT FROM updatecols INTO @Col,@Type
END
CLOSE updatecols
DEALLOCATE updatecols
--Now to tidy up the sql and add in the footer.
SET @Sql = substring(@Sql, 0, len(@Sql)) --Removes the trailing ,
SET @Sql = @Sql + ' FROM [' + @TableFromName + ']' --Adds in the from table
SET @Sql = 'INSERT INTO [' + @TableToName + '] ' + @Sql --Adds in the header
IF(@WhereStatement is not null)--Checks to see if a where statement exists
BEGIN--and adds it if it does
set @Sql = @Sql + ' where '+ @WhereStatement
END
/************The end!*******************/
--This simply shows the code which has been generated - this could just as easily
--execute it (sp_executesql @Sql) but its best to check it!
SELECT @Sql
Enjoy!

Ross

No comments: