SQL export, change and import

Update?

insert into yourtable select col1, col2, col3, "123" from yourtable

?

Not update, the 'tst' DATAset rows still needed.

I think the thing i'm looking for is a script the exports .
And then a script that imports.

So if you start with a table dbo.aa with column DataSet you end up with all the records where DataSet originally equals 'TST' containing DataSet equal to '123' ?

For one table, this is:

update dbo.aa
    set DataSet = '123'
    where DataSet = 'TST'

Then you have to repeat this for every table that has a field DataSet in it?

The code might be put into a stored procedure:

create procedure dbo.ChangeDataSet
(  @strTableName  varchar(64)    )
as
Begin
   declare @strCmd varchar(2000)
   set @strCmd = 'update ' + @strTableName + '  '
   set @strCmd = @cmd + '  set DataSet = ''123''    '
   set @strCmd = @cmd + '  where DataSet = ''TST''   '
   exec(@strCmd)
End

which gets called for each table like this:

exec dbo.ChangeDataSet(@strTableName)

And the code to get a list of all the tables with a column called DataSet:

declare @taTableList table (TableName varchar(64)
insert into @taTableList
SELECT table_name=sysobjects.name
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
   WHERE sysobjects.xtype='U'
      and syscolumns.name = 'DataSet'

select * from @taTableList

Then you can either use a cursor or a loop to work through @taTableList, getting each table name in turn and running the stored procedure on it.

If you effectively want to duplicate the rows in your table with the new ones having '123' instead of 'TST' the sql I posted should work

insert into yourtable select col1, col2, col3, "123" from yourtable

Be aware though as DcpKing said about uniqueness.....

Thanks, I'm looking in your suggested solutions later today :)