April 25, 2016 at 11:51 am
How do I automate a process which will
a) select all tables from my database. some tables have auto identity columns.
b) generate insert script that will turn on/off identities and save that script in a particular location.
I'd greatly appreciate if someone has any links or hints to get this.
TIA
April 25, 2016 at 1:02 pm
ishaan99 (4/25/2016)
How do I automate a process which willa) select all tables from my database. some tables have auto identity columns.
b) generate insert script that will turn on/off identities and save that script in a particular location.
I'd greatly appreciate if someone has any links or hints to get this.
TIA
Why would you do this instead of creating a proper backup?
What you're asking is not that difficult, but it makes no sense.
April 25, 2016 at 1:16 pm
backup and restore would be my first choice too, unless you can come up with a decent reason to avoid it.
if you are really going to script it out,
there's also performance to consider; how many rows are you talking about;
I've seen scripts of INSERT...VALUES or INSERT ...SELECT bog down at 40K rows in SSMS.
if you are exporting anything more than a few lookup tables, i'd script out bcp out raw and bcp in raw to handle that kind of logic.
if you really want to script it, my modified version of Narayana Vyas Kondreddi's export procedure, which does handle the identity_insert commands very nicely,
will do the job: instead of PRINTing the commands, my version stuffs them into table and SELECT's all the commands together.
* edit: i guess his old tripod site is kaput. i just checked his link after posting.
Lowell
April 26, 2016 at 6:49 am
Theres dependency on DBA team to get the database restored to point of time recovery. Turnaround time could be slow , as a result I m looking for alternate way of getting the data restored. Currently I just use SSMS to generate data script of all tables in that database as that creates the identity on/off script too. But this being a manual process want to see if the same can be automated. TIA
April 26, 2016 at 7:01 am
ishaan99 (4/26/2016)
Theres dependency on DBA team to get the database restored to point of time recovery. Turnaround time could be slow , as a result I m looking for alternate way of getting the data restored. Currently I just use SSMS to generate data script of all tables in that database as that creates the identity on/off script too. But this being a manual process want to see if the same can be automated. TIA
That's why we have the full recovery model. It's secure and it gives you point in time restore without working on new solutions.
There's plenty of information about how to implement a DR plan with integrated tools. If you want something different, you could check different backup and restore tools made for SQL Server, but they'll cost money.
April 26, 2016 at 7:10 am
It's really, really easy to script out a backup and restore,and with just a bit of tweaking and testing, make it fully automated.
For example, I have a job that restores a production database to the test server once a month, and can kick it off on demand when the need arises for data to be refreshed a bit earlier.
you make sure a share exists so your test server can find the backup, and script out both steps.
here's a crappy example, i would create a job that runs this on production's server
BACKUP DATABASE [TheProductionDatabase]
TO DISK = N'W:\SQLBackup\TheProductionDatabase_MonthEnd.bak'
WITH COPY_ONLY,
NOFORMAT,
INIT,
NAME = N'TheProductionDatabase-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
COMPRESSION,
STATS = 10
and then on the test server, something similar;
RESTORE DATABASE [UserAnalysis]
FROM DISK = N'\\TheProductionServer\sqlbackup\TheProductionDatabase_MonthEnd.bak'
WITH FILE = 1,
MOVE N'TheProductionDatabase' TO N'D:\SQLData\UserAnalysis.mdf',
MOVE N'TheProductionDatabase_log' TO N'L:\SQLLogs\UserAnalysis_1.ldf',
NOUNLOAD,
REPLACE,
RECOVERY,
STATS = 5
Lowell
April 27, 2016 at 8:05 am
Below script will generate the Insert script for all table from one particular db..
--Start
declare @TableName varchar(50)
declare tblcur CURSOR FOR
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE' AND TABLE_NAME LIKE 'DUMMY_%'
open tblcur
Fetch from tblcur
into @TableName
while @@fetch_status = 0
begin
/* This script will generate script to insert/update from a source table in one database to an
identical destination table in another database or server. It can be run for inserts or updates,
and can be run for a single row in insert and update mode, or all rows in a table for insert mode.
*/
declare @tab varchar(50)
,@pk1Val varChar(10)
,@pk1Name varChar(50)
,@qt char(1)
,@StatementType varChar(10)
,@sql varchar(max)
set nocount on
set @tab = @TableName
/*
Instructions:
1) open script and connect to the source database
2) Change the variable values to change the output options for the script below (@tab, @statementtype etc)
3) execute the script (best to use text output)
4) copy the script output into a script window, and run on the destination table.
@tab = the name of the source table
@pk1Val = if selecting a single row or doing an update statement, the value of the primary key for that row
@pk1Name = if inserting a single row or doing an update statement, the name of the column for the primary key
@StatementType = either 'INSERT' to create an insert statement or 'UPDATE' for an Update statement
*/
select
@pk1Val = '', @pk1Name = '', @StatementType = 'insert'
declare @tabName varchar(50)
, @colName varchar(50)
, @colType varchar(50)
, @collength varChar(50)
, @colOrder int
, @IsIdent char(1)
create table #output (Line varChar(4000), LineOrder int)
create table #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int, ColValue varchar(4000), colType varchar(50))
declare @out varchar(4000)
,@lineCounter int
,@ColValue varchar(4000)
,@sortCol varchar(50)
/* get the ordering column */
select @sortCol = sc.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 = @tab
and ((sc.status = 0x80) OR (ColOrder = 1 and not sc.status = 0x80 ))
/* put in the repeating values based on the columns*/
declare objCurs CURSOR FOR
select so.name, sc.name, st.name, sc.length, Case when sc.status = 0x80 then 'Y' else 'N' END as IsIdent, ColOrder
from sysobjects so
inner join syscolumns sc
on so.id= sc.id
inner join systypes st
on sc.xtype = st.xusertype
where so.Name = @tab
DECLARE @counter int, @numCols int, @RowNumber int, @LastRowNumber int, @maxRowNumber int, @maxColOrder int
select @numCols = count(sc.id)
from sysobjects so
inner join syscolumns sc
on so.id= sc.id
where so.Name = @tab
--select @numCols --debug code
open objCurs
Fetch from objCurs
into @tabname, @colName, @colType, @colLength, @isIdent, @colOrder
while @@fetch_status = 0
begin
SET @counter = 0
/* get the value from the table */
if @IsIdent = 'N'
BEGIN
--select @TabName,@ColName, @ColType, @ColLEngth, @isIdent, @ColOrder --debug code
/* increase better type handling by inserting more case statments, handling different data types */
if datalength(@pk1Name) = 0 or datalength(@pk1Val) = 0
begin
/* getting all rows in the table */
exec ('insert into #ColumnValues (ColName, ColOrder, ColValue, ColType)
select ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName + ' order by ' + @SortCol + ' ' +
' declare @counter int set @counter = 0 ' +
' update #ColumnValues Set @Counter = RowNumber = @Counter + (' + @numCols + ' * 10) where ColName = ''' + @colName + '''' )
end
else
begin
/* filtering by a pk val */
exec ('insert into #ColumnValues (RowNumber, ColName, ColORder, ColValue, ColType)
select 0, ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName +
' where ' + @pk1Name + ' = ' + @pk1Val)
end
end /* if @isIdent = 'n' */
Fetch Next from objCurs
into @tabname, @colName, @colType, @colLength, @IsIdent, @ColOrder
end
--select * from #ColumnValues --debug code
select @MaxRowNumber = Max(rowNumber) from #columnValues --keep highest row number so we know when we are finished
select @MaxColOrder = max(ColOrder) from #ColumnValues where RowNumber = @MaxRowNumber
/* next cursor for outputting the results from the retval table into the output table */
declare ColVal_Curs cursor for
select ColName , ColOrder , RowNumber , ColValue , colType
from #ColumnValues
order by RowNumber, ColOrder
open ColVal_Curs
--set the last row number to the first in the table, so post loop checking works
select @lastRowNumber = min(rowNumber) from #ColumnValues
set @lineCounter = @LastRowNumber --initialise at the first row
fetch from ColVal_Curs into
@colName, @ColOrder, @RowNumber, @colValue, @ColType
while @@Fetch_status = 0
BEGIN /* cursor loop */
/* get the quote type to enclose the value from the column type */
select @QT = case @colType
when 'nvarchar' then ''''
when 'varchar' then ''''
when 'nchar' then ''''
when 'DateTime' then ''''
when 'ntext' then ''''
when 'char' then ''''
else ''
end
--select @linecounter, @colName, @ColOrder, @RowNumber, @colValue, @ColType
if not @ColValue is null
if @rowNumber = @lineCounter
select @out = case @statementType
when 'UPDATE' THEN 'Update ' + @tab + ' SET '
when 'INSERT' then 'INSERT INTO ' + @tab + ' ('
end
begin
if @StatementType = 'UPDATE'
BEGIN
select @Out = @out + @colName + ' = ' + @QT + COALESCE(@ColValue, 'NULL') + @QT + ',' -- + @ColType
--select @Out = @out + @colName + ' = ' + @QT + @colName + @QT + ',' -- + @ColType
insert into #output (Line, LineOrder)
values (@out, @lineCounter)
end
if @statementType = 'INSERT'
BEGIN
/* put comma in */
if @lineCounter > @RowNumber --not first line in set of values for row
select @out = @out + ','
/*put in the name of the column */
insert into #output (Line, LineOrder)
values (@out + @colName
, @lineCounter)
if @lineCounter > @RowNumber --not first line in set of values for row
select @out = ','
else
select @out = ''
/* put in the value of the column */
insert into #output (Line, LineOrder)
values (@out + @QT + COALESCE(@ColValue, 'NULL') + @QT
, @lineCounter + 10 + @numCols)
END
end /*not @ColValue is null */
select @lineCounter = @lineCounter + 1
set @out = ''
/* get the next record*/
fetch from ColVal_Curs into
@colName, @ColOrder, @RowNumber, @colValue, @ColType
--select @ColOrder, @MaxColOrder, @@Fetch_Status --debug code
if (@rowNumber > @lastRowNumber) or (@RowNumber = @MaxRowNumber and @MaxColOrder = @ColOrder and @@FEtch_Status = -1)
BEGIN
/* this bit of processing is done whenever the set of columsn in a row changes to the next row of the original table*/
/* ie we are building a record to insert, and the PK changes because we are at the next record */
/* remove the last comma from the last line */
declare @lastLine int
if @statementType = 'UPDATE'
begin
/*remove last comma*/
update #output
set Line = left(Line,datalength(Line)-1)
where lineOrder = @LineCounter
/* insert a 'where' clause */
insert into #output (line, LineOrder)
select ' WHERE ' + @pk1Name + ' = ' + @pk1Val, Max(LineOrder) + 1 from #output
end
if @statementType = 'INSERT'
BEGIN
/* put in a 'values' statement between the column names and the column values */
insert into #output (Line, LineOrder)
values (') VALUES (', @LastRowNumber + @numCols + 5)
/* close off the lot */
insert into #output (line, lineorder)
select ')', Max(LineOrder) + 1 from #output
END
set @lastRowNumber = @RowNumber
set @lineCounter = @RowNumber /* reset linecounter for next set */
End /* if rownumber > last row number */
end /* cursor loop */
close objCurs
deallocate objCurs
close ColVal_Curs
deallocate ColVal_Curs
/* get the statements out from the list*/
set @sql = 'select line as [Script], lineorder into master.dbo.' + @tab +'_Script from #output order by lineorder'
--print @sql
exec (@sql)
set @sql =''
set @TableName =''
/* bug tracking code - uncomment to diagnose problems
select distinct RowNumber from #ColumnValues order by 1
select * from #ColumnValues
order by RowNumber, ColOrder, ColName, ColValue
*/
drop table #output
drop table #ColumnValues
set nocount off
Fetch Next from tblcur
into @tablename
set @tab = @TableName
end
close tblcur
deallocate tblcur
--select 'select Replace(script,''DUMMY_'','''') from '+ table_name +' order by lineorder asc' from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='base table' and table_name like 'dummy%'
--select 'drop table '+ table_name from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='base table' and table_name like 'dummy%'
--select 'SELECT ''UPDATE '+ TABLE_NAME +' SET SCRIPT =END if not exists(select ''''x'''' from '+ TABLE_NAME +' WHERE '' +(SELECT TOP 1 substring(script, CHARINDEX(''('', script, 0)+1,len(script)) from ' + TABLE_NAME +' WHERE script like ''insert%'') +''= -1) BEGIN '''
--FROM INFORMATION_SCHEMA.TABLES
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply