By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,890 Members | 1,285 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,890 IT Pros & Developers. It's quick & easy.

MSSQL insert into data conversion problem

P: 3
Hi, I do have 6 identical tables on six different databases (same server). I would like to merge them into one single table for reporting purposes.

For the majority of the table it does work without a problem with following procedure:

/*
100: NL
200: BE
300: UK
400: FR
600: SP
800: IT
*/
declare @dbName nvarchar(20)
declare @tblName nvarchar(30)
declare @command char(900)
declare @command2 char(900)
declare @command3 char(900)
declare @command4 char(900)
declare @command5 char(900)
declare @command6 char(900)
declare @tmpCommand char(500)
declare tbl_cur cursor for

SELECT distinct c.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND
t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND c.table_name not in ('AbsenceTypes','AccountAdministrations','BacoDisc ussionFixedTypes','BacoDiscussions','BankAccounts' ,'BankTransactions','bnkacc','btwtrs','EBDataQueue Entries','elnmnl','elnslp','ExactOptions','exomart 7','frhstx','frstx','frvstx','hrcomp_resource','hr components','hrwdoc','ItemAssortment','ItemNumbers ','Items','kstdr','kstpl','omzalg','omzart','orhst x','orkrg','orstx','orvstx','pospreset','recipe',' voorrd')
order by c.TABLE_NAME


open tbl_cur
fetch next from tbl_cur into @tblName


while @@fetch_status = 0 begin


set @dbName='100'
select @command='IF EXISTS (SELECT * FROM CERTIS_NEW.dbo.sysobjects WHERE id = object_id(N'+ char(39) +'[CERTIS_NEW].[dbo].[' + @tblName + ']'+ char(39) +') )' +
' begin DROP TABLE [CERTIS_NEW].dbo.' + @tblName + ' end ' +
' SELECT ' + char(39) + 'Certis Netherlands' + char(39) + 'AS branchname, ' + char(39) + 'NL' + char(39) + ' AS branch, *' +
' INTO CERTIS_NEW.dbo.'+ @tblName +
' FROM [maantsrv3].[' + @dbname + '].dbo.'+ @tblName +
' UNION ALL'
set @dbName='200'
select @command2=
' SELECT ' + char(39) + 'Certis Belgium' + char(39) + ' AS branchname, ' + char(39) + 'BE' + char(39) + ' AS branch, *' +
' FROM [maantsrv3].[' + @dbname + '].dbo.'+ @tblName +
' UNION ALL'
set @dbName='300'
select @command3=
' SELECT ' + char(39) + 'Certis UK' + char(39) + ' AS branchname, ' + char(39) + 'UK' + char(39) + ' AS branch, * ' +
' FROM [maantsrv3].[' + @dbname + '].dbo.'+ @tblName +
' UNION ALL'
set @dbName='400'
select @command4=
' SELECT ' + char(39) + 'Certis France' + char(39) + ' AS branchname, ' + char(39) + 'FR' + char(39) + ' AS branch, *' +
' FROM [maantsrv3].[' + @dbname + '].dbo.'+ @tblName +
' UNION ALL'

set @dbName='600'
select @command5=
' SELECT ' + char(39) + 'Certis Spain' + char(39) + ' AS branchname, ' + char(39) + 'SP' + char(39) + ' AS branch, *' +
' FROM [maantsrv3].[' + @dbname + '].dbo.'+ @tblName +
' UNION ALL'

set @dbName='800'
select @command6=
' SELECT ' + char(39) + 'Certis Italy' + char(39) + ' AS branchname, ' + char(39) + 'IT' + char(39) + ' AS branch, *' +
' FROM [maantsrv3].[' + @dbname + '].dbo.'+ @tblName

print (@command + @command2 + @command3 +@command4 +@command5 + @command6)
exec (@command + @command2 + @command3 + @command4 + @command5 + @command6)


fetch next from tbl_cur into @tblName
end


close tbl_cur
deallocate tbl_cur



But for the table I had to put in te "c.table_name not in list...." there I do receive data conversion errors. I even tried for these tables to create the table first and then to populate them,but even then I receive conversion problem. For some reason MSSQL tries to change the datatype of certain columns.

Can anyone help me out here please?
Many thanks
Oct 19 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.