April 21, 2010 at 5:48 am
Hi
I need to automate the table update process where a table is imported from a production snapshot at the end of the month and appended with a period to the same named table in the mart.
Now my problem is if the production table has grown during the said month, i.e one or more columns has been added to it then the insert will fail.
using the information schema I am able to do that check but what I need help with is how to write a query that will alter my table and add the missing columns. we are talking about 203 tables so I need to inlude those check and alterings in the stored procedure.
e.g
table1 table2
col1 int col1 int
col2 date col2 date
col3 varchar col3 varchar
col4 varchar
col5 varchar
What i need to do is import data from table2 into table1, now I need alter table code that I can incoporate in my pocedure so that when table2 has more columns than table1 then we can alter table1 and add the missing columns
thanks
April 21, 2010 at 6:32 am
Trybbe (4/21/2010)
What i need to do is import data from table2 into table1, now I need alter table code that I can incoporate in my pocedure so that when table2 has more columns than table1 then we can alter table1 and add the missing columns
That's doable but I wouldn't do it that way.
Good practice says related tables have to be keep structuraly in sync by appropriate change control management.
If structure of table1 depends on structure of table2 any ddl applied to table2 has to be applied to table1 during the same maintenance window.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 21, 2010 at 7:03 am
Hi thanks for the feedback, I totaly agree with you but the problem is that changes to the tables are done by a different team from a production side and I get to see these tables when a snapshot has been created on the first of every month. We have requested that should there be any changes to the tables they should inform us as they make the changes and what kind of changes but todate no luck.
I have created a script that does all the replace/append/update, but this script will have a problem with append if the table being appended has more columns
April 21, 2010 at 8:39 am
I don't have a script for this, but it seems like you could just use sys.tables and sys.columns and sys.types inside of a cursor or while loop to create dynamic SQL.
April 21, 2010 at 4:20 pm
Trybbe (4/21/2010)
Hi thanks for the feedback, I totaly agree with you but the problem is that changes to the tables are done by a different team from a production side and I get to see these tables when a snapshot has been created on the first of every month. We have requested that should there be any changes to the tables they should inform us as they make the changes and what kind of changes but todate no luck.I have created a script that does all the replace/append/update, but this script will have a problem with append if the table being appended has more columns
Heh... then make it important to them... tell the CEO that production failed due to a lack of communication on their part. High velocity pork chops work even in this day and age when correctly and appropriately launched.
--Jeff Moden
April 22, 2010 at 1:56 am
Hi Guys
Thanks for the suggestions here's what I've been trying, please tell me what is wrong with the alter statementdrop table #part
declare @Column_Name Varchar(Max)
declare @Data_Type Varchar(Max)
declare @RowNum int
create table #PART (RowNum int, Column_Name Varchar(Max), Data_Type Varchar(Max))
declare CustList cursor for
select Column_Name, Data_Type
From information_schema.columns
where table_name = 'customers'
except
select Column_Name, Data_Type
From information_schema.columns
where table_name = 'tmp_customers'
OPEN CustList
FETCH NEXT FROM CustList
INTO @Column_Name, @Data_Type
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ' ' + @Column_Name + ' ' + @Data_Type
insert into #Part values (@rownum, @column_name, @Data_Type)
alter table tmp_Customers add (@Column_Name @Data_Type)
FETCH NEXT FROM CustList
INTO @Column_Name, @Data_Type
END
CLOSE CustList
DEALLOCATE CustList
select * From #Part
April 22, 2010 at 5:13 am
The problem is here buddy
Trybbe (4/22/2010)
alter table tmp_Customers add (@Column_Name @Data_Type)
You cant pass a local variable to DDL statments.. now what u have resorted to using this way, i suggest u to use Dynamic SQL to accomplish this task..
Cheers!!
April 22, 2010 at 5:17 am
I changed your code, so it works. I also added ordinal_position, so the order of the columns is slightly more logical. You might need to improvie this code a bit more, so columns get added at the original position.
I'm sure Jeff and many others will say that you shouldn't use a cursor and the same for this kind of dynamic SQL, that might need some improvement also...
drop table #part
GO
declare @Column_Name sysname
declare @Data_Type sysname
declare @pos int
declare @RowNum int
declare @SQL VARCHAR(MAX)
create table #PART (RowNum int, Column_Name sysname, Data_Type sysname)
create table #cust (id int)
declare CustList cursor for
select Column_Name, Data_Type, ordinal_position
From information_schema.columns
where table_name = 'customers'
except
select Column_Name, Data_Type, ordinal_position
From information_schema.columns
where table_name = 'tmp_Customers'
order by ordinal_position
OPEN CustList
FETCH NEXT FROM CustList
INTO @Column_Name, @Data_Type, @pos
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ' ' + @Column_Name + ' ' + @Data_Type
insert into #Part values (@rownum, @column_name, @Data_Type)
SET @SQL = 'alter table tmp_Customers add ' + @Column_Name + ' ' + @Data_Type
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM CustList
INTO @Column_Name, @Data_Type, @pos
END
CLOSE CustList
DEALLOCATE CustList
select * From #Part
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
April 22, 2010 at 5:30 am
Thank you very much Ronald that's exactly what I was looking for.
April 22, 2010 at 5:33 am
trybbe, this below code might do the trick for you..
IF OBJECT_ID('TEMPDB..#part') IS NOT NULL
drop table #part
declare @RowNum int
create table #PART (RowNum int /*identity */identity(1,1) , Column_Name Varchar(Max), Data_Type Varchar(Max))
--===Follow the changes here
insert into #PART (Column_Name, Data_Type)
select Column_Name,
--|| these case satements maintain the integrity of the columns ||--
CASE UPPER(Data_Type)
WHEN 'VARCHAR' THEN DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))+')'
WHEN 'CHAR' THEN DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))+')'
WHEN 'NVARCHAR'THEN DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))+')'
WHEN 'NCHAR' THEN DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))+')'
WHEN 'DECIMAL' THEN DATA_TYPE+'('+CAST(NUMERIC_PRECISION AS VARCHAR(5))+','+CAST(NUMERIC_SCALE AS VARCHAR(5)) +')'
WHEN 'NUMERIC' THEN DATA_TYPE+'('+CAST(NUMERIC_PRECISION AS VARCHAR(5))+','+CAST(NUMERIC_SCALE AS VARCHAR(5)) +')'
WHEN 'MONEY' THEN DATA_TYPE+'('+CAST(NUMERIC_PRECISION AS VARCHAR(5))+','+CAST(NUMERIC_SCALE AS VARCHAR(5)) +')'
END Data_Type
From information_schema.columns
where table_name = 'customers'
except
select Column_Name,
--|| these case satements maintain the integrity of the columns ||--
CASE UPPER(Data_Type)
WHEN 'VARCHAR' THEN DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))+')'
WHEN 'CHAR' THEN DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))+')'
WHEN 'NVARCHAR'THEN DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))+')'
WHEN 'NCHAR' THEN DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))+')'
WHEN 'DECIMAL' THEN DATA_TYPE+'('+CAST(NUMERIC_PRECISION AS VARCHAR(5))+','+CAST(NUMERIC_SCALE AS VARCHAR(5)) +')'
WHEN 'NUMERIC' THEN DATA_TYPE+'('+CAST(NUMERIC_PRECISION AS VARCHAR(5))+','+CAST(NUMERIC_SCALE AS VARCHAR(5)) +')'
WHEN 'MONEY' THEN DATA_TYPE+'('+CAST(NUMERIC_PRECISION AS VARCHAR(5))+','+CAST(NUMERIC_SCALE AS VARCHAR(5)) +')'
END Data_Type
From information_schema.columns
where table_name = 'tmp_customers'
select * from #Part
--===Declare local variable like this
DECLARE @ALTER_QUERY VARCHAR(8000)
--||Initialization of the variable ||--
SET @ALTER_QUERY = ''
--||here is the business end. this is where the alter statements are stringed ||--
SELECT @ALTER_QUERY = @ALTER_QUERY + 'alter table tmp_customers add ['+Column_Name+'] '+Data_Type +CHAR(10)
FROM #Part
--|| first print your code to check of all the alter statements are formed correctly ||--
PRINT @ALTER_QUERY
--||uncomment the below if the above was good ||--
--EXEC(@ALTER_QUERY)
But be informed, you have to be careful while using dynamic sql..
Tell us here is that worked for u!
Cheers!!
~Edit : Added brackets for the column names to avoid Regular Naming conflicts
April 22, 2010 at 5:42 am
Ronald beat me to it.. but there is a difference between my code and ronald's.. ron's uses cursors, mine doesnt.. and i have also CASE statements to bring out the max length, precision, scale etc etc to the data_type.. and also, i dont think SQL-Injection will play a role in this context as there wont be any "malicious" column names.. so use this code at your will , trybbe 🙂
Cheers!!
April 22, 2010 at 5:45 am
Thanks guys I'm already putting it to ractice will let you know how it goes
April 22, 2010 at 6:03 am
Good Luck , Trybbe ! 🙂
April 22, 2010 at 7:42 am
Guys thank you very much for your help. I chose to go with Ronalds cursor for one simple reason:
when I ran your one cold coffee the target table was not ordered and results returned null data types, how ever since there is so much caution about the use of cursors and dynamic SQL I am still trying to see why am I not getting the desired results with this one. any way my final work ended like so:
declare
@HRDBTable int,
@OrgTable int
Select @HRDBTable = (SELECT COUNT(column_name) FROM INFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = 'Staff') --- hrdb table
select @OrgTable = (SELECTCOUNT(column_name) FROMServer1.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Staff') --- table from orgpos snapshot
IF @HRDBTable >= @OrgTable
Begin
declare
@DBName varchar(max),
@SQL varchar(max),
@Period Numeric(18,0)
SET @DBName = 'Server1'
SET @Period = 201003
SET @SQL = 'Insert Into Staff SELECT *, period = ' + @Period + 'FROM ' + @DBName + '.dbo.Staff'
EXEC (@SQL)
end
ELSE
declare @Column_Name sysname
declare @Data_Type sysname
declare @pos int
declare @RowNum int
-- declare @SQL VARCHAR(MAX)
-- create table #TblUpdate (RowNum int, Column_Name sysname, Data_Type sysname)
declare TblUpdate cursor for
selectColumn_Name, Case when DATA_TYPE not like '%char%' then DATA_TYPE else
DATA_TYPE + ' ' + '(' + CONVERT(varchar(20), CHARACTER_MAXIMUM_LENGTH) + ')'
end as DATA_TYPE, ordinal_position
FromServer1.information_schema.columns
wheretable_name = 'Staff'
andColumn_Name not IN (
select Column_Name
From information_schema.columns
where table_name = 'Staff')
order by ordinal_position
OPEN TblUpdate
FETCH NEXT FROM TblUpdate
INTO @Column_Name, @Data_Type, @pos
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
SET @SQL = 'alter table Staff add ' + @Column_Name + ' ' + @Data_Type
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM TblUpdate
INTO @Column_Name, @Data_Type, @pos
END
CLOSE TblUpdate
DEALLOCATE TblUpdate
go
alter table Staff
add Period1 Numeric
---> Update period1 with records in Period
UPDATEdbo.Staff
SETStaff.Period1 = Staff.Period
---> Drop Period and Rename Period1 to period
alter table Staff drop column Period
exec sp_rename 'Staff.Period1', 'Period', 'column'
-- Update table Staff
declare
@DBName varchar(max),
@SQL varchar(max),
@Period Numeric(18,0)
SET @DBName = 'Server1'
SET @Period = 201003
SET@SQL = 'Insert Into Staff SELECT *, period = ' + @Period + 'FROM ' + @DBName + '.dbo.Staff'
EXEC (@SQL)
Thanks Guys
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply