Copying tables from another database

Hi,
I'm trying to copy the data of a table into another table in another
database.
I.E. Copy contents of database1.table1 into database2.table1
I've got a table in database2 that lists the tables that need to be
copied.
I'm running a store proceedure that reads each record in the table and
checks database2 to see if the table exists (database1 doesnt need this
check it is assummed the table exists there). If the table exist then
the table is just updated. However if the table does not exist then the
table is created (in database2 as it already exists in database1). I've
created a store proceedure for this but I can only get it to create new
table in original databases and not in the other database. i.e. i can
only create a table in database2 when it uses another table in the same
database as reference, where it should be creating a table in database2
when using a table in database1 as a refernce. An example of the code
below:
CREATE PROCEDURE create_table AS
select @.table_name = 'table1'
declare @.table_loc varchar (60)
select @.table_loc = 'database1.dbo.table1'
-- column counter
declare @.column_limit int
-- get number of columns
select @.column_limit = (select count(column_name) from
information_Schema.columns where table_Name=@.table_loc)
-- column pointer
declare @.column_num int
-- defines column point as 1
select @.column_num =1
-- column name
declare @.column_name char(20)
-- table columns define
declare @.col_name char(20)
declare @.data char(20)
declare @.length int
declare @.nullable char (20)
-- get the column name
select @.col_name = (select column_name from information_Schema.columns
where table_Name=@.table_loc and ordinal_position=@.column_num)
-- gets the data type
select @.data = (select data_type from information_Schema.columns where
table_Name=@.table_loc and ordinal_position=@.column_num)
--gets the max length
select @.length = (select character_maximum_length from
information_Schema.columns where table_Name=@.table_loc and
ordinal_position=@.column_num)
-- gets if table nullable or not
select @.nullable = (select is_nullable from information_Schema.columns
where table_Name=@.table_loc and ordinal_position=@.column_num)
-- creates first column in the table
-- checks to see if column will allow null values
if @.nullable = 'No'
BEGIN -- if null values are not allows
-- checks data type if char or varchar will create of correct length
if @.data = 'varchar' or @.data = 'char'
begin
-- creates a column for a char of varchar
exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + ' ('
+ @.length + ') ' + ' not null)')
print 'Column created ' + @.col_name + @.data
end -- if
else
begin
-- creates a column for any other type of data
exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + '
not null)')
print 'Column created ' + @.col_name + @.data
end --else
END -- if for nullable
ELSE
BEGIN -- if null values are allowed
-- checks data type if char or varchar will create of correct length
if @.data = 'varchar' or @.data = 'char'
begin
-- creates a column for a char of varchar
exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + ' ('
+ @.length + ')' + ')')
print 'Column created ' + @.col_name + @.data
end -- if
else
begin
-- creates a column for any other type of data
exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + ')')
print 'Column created ' + @.col_name + @.data
end -- else
END -- else for nullable
-- incremnets counter as first column been create
select @.column_num=@.column_num+1
-- while loop adds columns to the table
while @.column_num <= @.column_limit
BEGIN
-- gets the column name
select @.col_name = (select column_name from information_Schema.columns
where table_Name=@.table_loc and ordinal_position=@.column_num)
-- gets the data type
select @.data = (select data_type from information_Schema.columns where
table_Name=@.table_loc and ordinal_position=@.column_num)
-- gets the max length
select @.length = (select character_maximum_length from
information_Schema.columns where table_Name=@.table_loc and
ordinal_position=@.column_num)
-- gets if table nullable or not
select @.nullable = (select is_nullable from information_Schema.columns
where table_Name=@.table_loc and ordinal_position=@.column_num)
-- adds the column to the table (whatever table name @.tab_name is)
-- checks data type if char or varchar will create of correct length
if @.data = 'varchar' or @.data = 'char'
begin
-- creates a column for a char of varchar
exec ('ALTER TABLE ' + @.table_name + ' ADD ' + @.col_name + ' ' +
@.data + '(' + @.length + ')')
print 'Column created ' + @.col_name + @.data
end -- if
else
begin
-- creates a column for any other type of data
exec ('ALTER TABLE ' + @.table_name + ' ADD ' + @.col_name + @.data)
print 'Column created ' + @.col_name + @.data
end -- else
select @.column_num=@.column_num+1
END -- while -- for creating the table
print 'Table name ' + @.table_name + ' created.'
GO
I hope all that makes sense...
Thanks in advance
Simonexecute the following before your create statement
use database2
look up USE in BOL
http://sqlservercode.blogspot.com/
"accyboy1981@.gmail.com" wrote:
> Hi,
> I'm trying to copy the data of a table into another table in another
> database.
> I.E. Copy contents of database1.table1 into database2.table1
> I've got a table in database2 that lists the tables that need to be
> copied.
> I'm running a store proceedure that reads each record in the table and
> checks database2 to see if the table exists (database1 doesnt need this
> check it is assummed the table exists there). If the table exist then
> the table is just updated. However if the table does not exist then the
> table is created (in database2 as it already exists in database1). I've
> created a store proceedure for this but I can only get it to create new
> table in original databases and not in the other database. i.e. i can
> only create a table in database2 when it uses another table in the same
> database as reference, where it should be creating a table in database2
> when using a table in database1 as a refernce. An example of the code
> below:
> CREATE PROCEDURE create_table AS
> select @.table_name = 'table1'
> declare @.table_loc varchar (60)
> select @.table_loc = 'database1.dbo.table1'
>
> -- column counter
> declare @.column_limit int
> -- get number of columns
> select @.column_limit = (select count(column_name) from
> information_Schema.columns where table_Name=@.table_loc)
> -- column pointer
> declare @.column_num int
> -- defines column point as 1
> select @.column_num =1
> -- column name
> declare @.column_name char(20)
> -- table columns define
> declare @.col_name char(20)
> declare @.data char(20)
> declare @.length int
> declare @.nullable char (20)
>
> -- get the column name
> select @.col_name = (select column_name from information_Schema.columns
> where table_Name=@.table_loc and ordinal_position=@.column_num)
> -- gets the data type
> select @.data = (select data_type from information_Schema.columns where
> table_Name=@.table_loc and ordinal_position=@.column_num)
> --gets the max length
> select @.length = (select character_maximum_length from
> information_Schema.columns where table_Name=@.table_loc and
> ordinal_position=@.column_num)
> -- gets if table nullable or not
> select @.nullable = (select is_nullable from information_Schema.columns
> where table_Name=@.table_loc and ordinal_position=@.column_num)
> -- creates first column in the table
> -- checks to see if column will allow null values
> if @.nullable = 'No'
> BEGIN -- if null values are not allows
> -- checks data type if char or varchar will create of correct length
> if @.data = 'varchar' or @.data = 'char'
> begin
> -- creates a column for a char of varchar
> exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + ' ('
> + @.length + ') ' + ' not null)')
> print 'Column created ' + @.col_name + @.data
> end -- if
> else
> begin
> -- creates a column for any other type of data
> exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + '
> not null)')
> print 'Column created ' + @.col_name + @.data
> end --else
> END -- if for nullable
> ELSE
> BEGIN -- if null values are allowed
> -- checks data type if char or varchar will create of correct length
> if @.data = 'varchar' or @.data = 'char'
> begin
> -- creates a column for a char of varchar
> exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + ' ('
> + @.length + ')' + ')')
> print 'Column created ' + @.col_name + @.data
> end -- if
> else
> begin
> -- creates a column for any other type of data
> exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + ')')
> print 'Column created ' + @.col_name + @.data
> end -- else
> END -- else for nullable
> -- incremnets counter as first column been create
> select @.column_num=@.column_num+1
> -- while loop adds columns to the table
> while @.column_num <= @.column_limit
> BEGIN
> -- gets the column name
> select @.col_name = (select column_name from information_Schema.columns
> where table_Name=@.table_loc and ordinal_position=@.column_num)
> -- gets the data type
> select @.data = (select data_type from information_Schema.columns where
> table_Name=@.table_loc and ordinal_position=@.column_num)
> -- gets the max length
> select @.length = (select character_maximum_length from
> information_Schema.columns where table_Name=@.table_loc and
> ordinal_position=@.column_num)
> -- gets if table nullable or not
> select @.nullable = (select is_nullable from information_Schema.columns
> where table_Name=@.table_loc and ordinal_position=@.column_num)
> -- adds the column to the table (whatever table name @.tab_name is)
> -- checks data type if char or varchar will create of correct length
> if @.data = 'varchar' or @.data = 'char'
> begin
> -- creates a column for a char of varchar
> exec ('ALTER TABLE ' + @.table_name + ' ADD ' + @.col_name + ' ' +
> @.data + '(' + @.length + ')')
> print 'Column created ' + @.col_name + @.data
> end -- if
> else
> begin
> -- creates a column for any other type of data
> exec ('ALTER TABLE ' + @.table_name + ' ADD ' + @.col_name + @.data)
> print 'Column created ' + @.col_name + @.data
> end -- else
> select @.column_num=@.column_num+1
> END -- while -- for creating the table
> print 'Table name ' + @.table_name + ' created.'
> GO
> I hope all that makes sense...
> Thanks in advance
> Simon
>|||To read the information schema from another database you'll have to
specify the database name in front of the information_schema.table_name
- maybe that's the problem.
Why would you do this in a proc? Have you considered using Enterprise
Manager or some other tool to script the changes? Procs aren't normally
used to modify the schema and doing so can give some problems if new
objects are later referenced in the proc using static code. It may just
be possible using dynamic SQL but I don't see the case for using a proc
rather than a script that can be tested, reliably reproduced and then
checked into source control.
--
David Portas
SQL Server MVP
--|||Could be that you a solving problems that I am not
aware off, but you method seems rather complex.
To create a table from 'another' database in the actual
database you could use :
Select * into actualtablename from another.tablename
Maybe this is a good starting point for you, but I might
be wrong.
Good luck,
ben brugman
<accyboy1981@.gmail.com> wrote in message
news:1128084724.400328.211300@.g43g2000cwa.googlegroups.com...
> Hi,
> I'm trying to copy the data of a table into another table in another
> database.
> I.E. Copy contents of database1.table1 into database2.table1
> I've got a table in database2 that lists the tables that need to be
> copied.
> I'm running a store proceedure that reads each record in the table and
> checks database2 to see if the table exists (database1 doesnt need this
> check it is assummed the table exists there). If the table exist then
> the table is just updated. However if the table does not exist then the
> table is created (in database2 as it already exists in database1). I've
> created a store proceedure for this but I can only get it to create new
> table in original databases and not in the other database. i.e. i can
> only create a table in database2 when it uses another table in the same
> database as reference, where it should be creating a table in database2
> when using a table in database1 as a refernce. An example of the code
> below:
> CREATE PROCEDURE create_table AS
> select @.table_name = 'table1'
> declare @.table_loc varchar (60)
> select @.table_loc = 'database1.dbo.table1'
>
> -- column counter
> declare @.column_limit int
> -- get number of columns
> select @.column_limit = (select count(column_name) from
> information_Schema.columns where table_Name=@.table_loc)
> -- column pointer
> declare @.column_num int
> -- defines column point as 1
> select @.column_num =1
> -- column name
> declare @.column_name char(20)
> -- table columns define
> declare @.col_name char(20)
> declare @.data char(20)
> declare @.length int
> declare @.nullable char (20)
>
> -- get the column name
> select @.col_name = (select column_name from information_Schema.columns
> where table_Name=@.table_loc and ordinal_position=@.column_num)
> -- gets the data type
> select @.data = (select data_type from information_Schema.columns where
> table_Name=@.table_loc and ordinal_position=@.column_num)
> --gets the max length
> select @.length = (select character_maximum_length from
> information_Schema.columns where table_Name=@.table_loc and
> ordinal_position=@.column_num)
> -- gets if table nullable or not
> select @.nullable = (select is_nullable from information_Schema.columns
> where table_Name=@.table_loc and ordinal_position=@.column_num)
> -- creates first column in the table
> -- checks to see if column will allow null values
> if @.nullable = 'No'
> BEGIN -- if null values are not allows
> -- checks data type if char or varchar will create of correct length
> if @.data = 'varchar' or @.data = 'char'
> begin
> -- creates a column for a char of varchar
> exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + ' ('
> + @.length + ') ' + ' not null)')
> print 'Column created ' + @.col_name + @.data
> end -- if
> else
> begin
> -- creates a column for any other type of data
> exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + '
> not null)')
> print 'Column created ' + @.col_name + @.data
> end --else
> END -- if for nullable
> ELSE
> BEGIN -- if null values are allowed
> -- checks data type if char or varchar will create of correct length
> if @.data = 'varchar' or @.data = 'char'
> begin
> -- creates a column for a char of varchar
> exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + ' ('
> + @.length + ')' + ')')
> print 'Column created ' + @.col_name + @.data
> end -- if
> else
> begin
> -- creates a column for any other type of data
> exec ('create table ' + @.table_name + ' (' + @.col_name + @.data + ')')
> print 'Column created ' + @.col_name + @.data
> end -- else
> END -- else for nullable
> -- incremnets counter as first column been create
> select @.column_num=@.column_num+1
> -- while loop adds columns to the table
> while @.column_num <= @.column_limit
> BEGIN
> -- gets the column name
> select @.col_name = (select column_name from information_Schema.columns
> where table_Name=@.table_loc and ordinal_position=@.column_num)
> -- gets the data type
> select @.data = (select data_type from information_Schema.columns where
> table_Name=@.table_loc and ordinal_position=@.column_num)
> -- gets the max length
> select @.length = (select character_maximum_length from
> information_Schema.columns where table_Name=@.table_loc and
> ordinal_position=@.column_num)
> -- gets if table nullable or not
> select @.nullable = (select is_nullable from information_Schema.columns
> where table_Name=@.table_loc and ordinal_position=@.column_num)
> -- adds the column to the table (whatever table name @.tab_name is)
> -- checks data type if char or varchar will create of correct length
> if @.data = 'varchar' or @.data = 'char'
> begin
> -- creates a column for a char of varchar
> exec ('ALTER TABLE ' + @.table_name + ' ADD ' + @.col_name + ' ' +
> @.data + '(' + @.length + ')')
> print 'Column created ' + @.col_name + @.data
> end -- if
> else
> begin
> -- creates a column for any other type of data
> exec ('ALTER TABLE ' + @.table_name + ' ADD ' + @.col_name + @.data)
> print 'Column created ' + @.col_name + @.data
> end -- else
> select @.column_num=@.column_num+1
> END -- while -- for creating the table
> print 'Table name ' + @.table_name + ' created.'
> GO
> I hope all that makes sense...
> Thanks in advance
> Simon
>