How to copy, replace, and insert across all rows in the database

I'll do my best to explain:

1. Copy all rows that exist in current database
2. Mass replace a specific string value in every row that contains a specific field
3. Insert the copied rows

Not sure what approach to take other than hammer开发者_StackOverflowing out sql scripts.

Thanks!

Is this as simple as creating an Data Flow Source, selecting all the rows, then passing them to a Derived Column transformation, which would be along the lines of:

REPLACE( [ColumnName], "SpecificValue", "ReplacementValue" ) 

and then insert these rows into your destination table using the relevant Data Flow Destination.

I may be misunderstanding or simplifying step 2...

Assuming you have a table called "table2" and that table consists of the columns facilabbr, unitname, and sortnum... You can select all rows into a temporary table (# signifies a temporary table) changing the "unitname" column to something else...You'll be left with the new values in the temporary table. You can then replace the values in your initial table if you want.

INSERT INTO #temptable1
SELECT facilabbr, 
'myNewUnitName' as unitname, 
sortnum 
FROM table2

DELETE FROM table2

INSERT INTO table2
SELECT facilabbr, 
unitname, 
sortnum 
FROM #temptable1

--THIS QUERY IS ONLY EQUIPPED TO HANDLE: 
--SIMPLE NUMERICS SUCH AS FLOATS, INTS, ETC
--SIMPLE STRING DATA TYPES LIMITED TO: VARCHARS, CHARS, NCHARS AND NVARCHARS
--DATES AND DATETIMES

Create Procedure SQLCloner
@TableName as VarChar(max), -- Table that holds data to clone.
@NewTableName as VarChar(max) = '', -- Table to Insert into. If same as Tablename leave blank or write ''.
@VarCharFind as VarChar(max) = '', -- Value to find (In order to replace). If you aren't replacing leave blank or write ''.
@VarCharReplace as VarChar(max) = '', -- Value to replace. If you aren't replacing leave blank or write ''.
@OptionalParam As VarChar(Max) = '' -- Your WHERE clause. If you have none leave blank or write ''.

AS

Declare @index as int = 1
Declare @rowcount As Int = 0
Declare @execFunction As VarChar(max) = ''
Declare @InsertTableRowName As VarChar(max) = ''
Declare @TempFilterType As VarChar(Max) = ''



--Create RowCount of Table
Select @ROWCOUNT = Count(*)

From    (
        Select  Column_Name 
        From    INFORMATION_SCHEMA.COLUMNS
        Where   Table_Name = '' + @TableName + ''
        ) As TheCount

--Use While Loop to create Table Columns
While @index <= @rowcount
Begin

--Determines the Variable type to change the exec function accordingly
    Select  @TempFilterType = TypeTable.DATA_TYPE

    From    (
            Select  Data_Type,
                    ROW_NUMBER() OVER (Order By Ordinal_Position) as RowNum
            From    INFORMATION_SCHEMA.COLUMNS
            Where   Table_Name = @TableName

            ) As TypeTable
    Where TypeTable.RowNum = @index

--Prepares @InsertTableRowName With the first part of the string
    Set @InsertTableRowName = Case 
        When    @TempFilterType IN('varchar', 'nvarchar','char', 'nchar')
            Then    @InsertTableRowName + ''''''''' + ' 
        When    @TempFilterType IN('datetime', 'date')
            Then    @InsertTableRowName + ''''''''' + Convert(varchar(Max), '
        Else
                    @InsertTableRowName + 'Convert(varchar(Max), '
    End

--Determines the Name of the Column
    Select  @InsertTableRowName = @InsertTableRowName  + 
    Case
    When @TempFilterType IN('varchar', 'nvarchar','char', 'nchar')
        Then 'ISNULL(' + 'Replace(' + Column_Name + ','''''''','''''''''''')' + ','''')'
    When    @TempFilterType IN('datetime', 'date')
        Then 'ISNULL(' + 'Replace(' + Column_Name + ','''''''','''''''''''')' + ',''12/31/9999'')'
    Else
        'ISNULL(' + 'Replace(' + Column_Name + ','''''''','''''''''''')' + ',0)'
    End


    From    (
            Select  Column_Name, 
                    ROW_NUMBER() OVER (Order By Ordinal_Position) As RowNum 
            From INFORMATION_SCHEMA.COLUMNS
            Where Table_Name = @TableName
            ) As TheRow
    Where RowNum = @index


--Finishes Closes each column insert (in every instance)
    Set @InsertTableRowName = Case 

        When    @TempFilterType IN('varchar', 'nvarchar','char', 'nchar')
            Then    @InsertTableRowName + ' + '''''''''
        When    @TempFilterType IN('datetime', 'date')
            Then    @InsertTableRowName + ') + ''''''''' 
        Else
                    @InsertTableRowName + ') '

    End


--Links each Row together with commas and plus signs until the very end.
    If @index < @rowcount 
    Begin 

        Set     @InsertTableRowName = Case 
            When    @TempFilterType IN('varchar', 'nvarchar','char', 'nchar')
                Then    @InsertTableRowName + ' + ' + ''',''' + ' + '
            When    @TempFilterType IN('datetime', 'date')
                Then    @InsertTableRowName + ' + '','' + '
            Else
                        @InsertTableRowName + ' + '','' + '
        End

    End

    Set @index = @index + 1
End

--Puts the Query together (without any of the Parameters yet). 
--First, determine if a new table should be used instead.
If @NewTableName = ''
Begin
    Set @NewTableName = @TableName
End

--Next, Build the Query, and do it accordingly with if there is a Find/Replace asked for.
Set @execFunction = 'Select '
If @VarCharFind <> ''
Begin
    Set @execFunction = @execFunction + 'Replace('
End
Set @execFunction = @execFunction + '''insert into ' + @NewTableName + ' Values('' + ' + @InsertTableRowName + ' + '')'' '
If @VarCharFind <> ''
Begin
    Set @execFunction = @execFunction + ', ''' + @VarCharFind + ''', ''' + @VarCharReplace + ''') '
End
Set @execFunction = @execFunction + 'From ' + @TableName




--Adds in the optional Parameters
If      @OptionalParam <> ''
Begin
    Set @execFunction = @execFunction + ' ' + @OptionalParam
End
Set @execFunction = @execFunction + CHAR(13)+CHAR(10)

--Executes the function and pulls an entire set of queries to copy into the new Database

Print @execFunction
Exec(@execFunction)

GO