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
Simon
execute 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.googlegr oups.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
>

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
>

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
>

Copying Tables and adding fields

Hi! I need some expert advise.
This is what I am trying to do:
1. I have two tables Table1 and Table2 in the same db.
2. Table2 has one column more than Table1. The extra column is the primary
key.
3. Copy contents of Table1 => Table2 but also add the extra primary key value
which is a sequential number.
Questions:
1. Is stored procedure the best way to do this ?
2. I do need to be sure Table1 does have data. So, should I do the following ?
IF ((Select count(*) from Table1) > 0)
do something .... what is the syntax of IF Statements in SPROC ?
3. Do I have to read a table row at a time ? Is CURSOR the best way to do
it ?
Any help or code snipets will be appreciated.
I am using .NET with SQL 2000 and C# on a windows platform.
Thanks,
LW
I think you will just need an INSERT statement:
INSERT INTO Table2 (col1, col2, ...)
SELECT col1, col2, ...
FROM Table1
David Portas
SQL Server MVP
|||The following will not work for me because:
1. Table2 has one extra column so when do I insert it ?
2. Also, how do I go through all records in Table1 ?
LW
"David Portas" wrote:

> I think you will just need an INSERT statement:
> INSERT INTO Table2 (col1, col2, ...)
> SELECT col1, col2, ...
> FROM Table1
> --
> David Portas
> SQL Server MVP
> --
>
>
|||1. You said that the extra column is a sequential number. If you make that
an IDENTITY column then the number will be assigned automatically - you
don't need to generate it. Lookup IDENTITY in Books Online. Note that
IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
is therefore no substitute for having a meaningful unique key in your table.
2. The SELECT statement will select ALL rows from Table1 unless you specify
a WHERE clause. You don't need to loop through each one. This is why SQL
statements are called "set operations" - because they work on SETS of rows,
not individual records.
David Portas
SQL Server MVP
|||David,
Thanks very much. I read up on IDENTITY and it will work really well
for my table.
The stored procedure is working just as you said. Amazing how simple
it was.
LW
"David Portas" wrote:

> 1. You said that the extra column is a sequential number. If you make that
> an IDENTITY column then the number will be assigned automatically - you
> don't need to generate it. Lookup IDENTITY in Books Online. Note that
> IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
> is therefore no substitute for having a meaningful unique key in your table.
> 2. The SELECT statement will select ALL rows from Table1 unless you specify
> a WHERE clause. You don't need to loop through each one. This is why SQL
> statements are called "set operations" - because they work on SETS of rows,
> not individual records.
> --
> David Portas
> SQL Server MVP
> --
>
>

Copying Tables and adding fields

Hi! I need some expert advise.
This is what I am trying to do:
1. I have two tables Table1 and Table2 in the same db.
2. Table2 has one column more than Table1. The extra column is the primary
key.
3. Copy contents of Table1 => Table2 but also add the extra primary key value
which is a sequential number.
Questions:
1. Is stored procedure the best way to do this ?
2. I do need to be sure Table1 does have data. So, should I do the following ?
IF ((Select count(*) from Table1) > 0)
do something .... what is the syntax of IF Statements in SPROC ?
3. Do I have to read a table row at a time ? Is CURSOR the best way to do
it ?
Any help or code snipets will be appreciated.
I am using .NET with SQL 2000 and C# on a windows platform.
Thanks,
LWI think you will just need an INSERT statement:
INSERT INTO Table2 (col1, col2, ...)
SELECT col1, col2, ...
FROM Table1
--
David Portas
SQL Server MVP
--|||The following will not work for me because:
1. Table2 has one extra column so when do I insert it ?
2. Also, how do I go through all records in Table1 ?
LW
"David Portas" wrote:
> I think you will just need an INSERT statement:
> INSERT INTO Table2 (col1, col2, ...)
> SELECT col1, col2, ...
> FROM Table1
> --
> David Portas
> SQL Server MVP
> --
>
>|||1. You said that the extra column is a sequential number. If you make that
an IDENTITY column then the number will be assigned automatically - you
don't need to generate it. Lookup IDENTITY in Books Online. Note that
IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
is therefore no substitute for having a meaningful unique key in your table.
2. The SELECT statement will select ALL rows from Table1 unless you specify
a WHERE clause. You don't need to loop through each one. This is why SQL
statements are called "set operations" - because they work on SETS of rows,
not individual records.
--
David Portas
SQL Server MVP
--|||David,
Thanks very much. I read up on IDENTITY and it will work really well
for my table.
The stored procedure is working just as you said. Amazing how simple
it was.
LW
"David Portas" wrote:
> 1. You said that the extra column is a sequential number. If you make that
> an IDENTITY column then the number will be assigned automatically - you
> don't need to generate it. Lookup IDENTITY in Books Online. Note that
> IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
> is therefore no substitute for having a meaningful unique key in your table.
> 2. The SELECT statement will select ALL rows from Table1 unless you specify
> a WHERE clause. You don't need to loop through each one. This is why SQL
> statements are called "set operations" - because they work on SETS of rows,
> not individual records.
> --
> David Portas
> SQL Server MVP
> --
>
>

Copying Tables and adding fields

Hi! I need some expert advise.
This is what I am trying to do:
1. I have two tables Table1 and Table2 in the same db.
2. Table2 has one column more than Table1. The extra column is the primary
key.
3. Copy contents of Table1 => Table2 but also add the extra primary key valu
e
which is a sequential number.
Questions:
1. Is stored procedure the best way to do this ?
2. I do need to be sure Table1 does have data. So, should I do the following
?
IF ((Select count(*) from Table1) > 0)
do something .... what is the syntax of IF Statements in SPROC ?
3. Do I have to read a table row at a time ? Is CURSOR the best way to do
it ?
Any help or code snipets will be appreciated.
I am using .NET with SQL 2000 and C# on a windows platform.
Thanks,
LWI think you will just need an INSERT statement:
INSERT INTO Table2 (col1, col2, ...)
SELECT col1, col2, ...
FROM Table1
David Portas
SQL Server MVP
--|||The following will not work for me because:
1. Table2 has one extra column so when do I insert it ?
2. Also, how do I go through all records in Table1 ?
LW
"David Portas" wrote:

> I think you will just need an INSERT statement:
> INSERT INTO Table2 (col1, col2, ...)
> SELECT col1, col2, ...
> FROM Table1
> --
> David Portas
> SQL Server MVP
> --
>
>|||1. You said that the extra column is a sequential number. If you make that
an IDENTITY column then the number will be assigned automatically - you
don't need to generate it. Lookup IDENTITY in Books Online. Note that
IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
is therefore no substitute for having a meaningful unique key in your table.
2. The SELECT statement will select ALL rows from Table1 unless you specify
a WHERE clause. You don't need to loop through each one. This is why SQL
statements are called "set operations" - because they work on SETS of rows,
not individual records.
David Portas
SQL Server MVP
--|||David,
Thanks very much. I read up on IDENTITY and it will work really well
for my table.
The stored procedure is working just as you said. Amazing how simple
it was.
LW
"David Portas" wrote:

> 1. You said that the extra column is a sequential number. If you make that
> an IDENTITY column then the number will be assigned automatically - you
> don't need to generate it. Lookup IDENTITY in Books Online. Note that
> IDENTITY does not enforce any uniqueness constraint on your data. IDENTITY
> is therefore no substitute for having a meaningful unique key in your tabl
e.
> 2. The SELECT statement will select ALL rows from Table1 unless you specif
y
> a WHERE clause. You don't need to loop through each one. This is why SQL
> statements are called "set operations" - because they work on SETS of rows
,
> not individual records.
> --
> David Portas
> SQL Server MVP
> --
>
>sql

copying data from one table to a new one with some different data types

Is it possible to easily copy data from one table to another if the data types don't match.

I know you can do a INSERT INTO table1(col1,col2) SELECT (col2,col7) FROM table2 if the data types match but is there a way to do this if they don't.

I'm not trying to copy date times into bit fields or anything. I just have an old table that I built when I really didn't know what I was doing now I at least

think I have a better understanding of what data types to use, so I was wanting to move the data in the orignal table to my new one. Most of the fields in the old

database are text datatypes and the new database is nvarchar(50) data types.

Thanks for any suggestions.

If the datatypes are "compatible" then you should be okay. As you mentioned moving from text datatype to nvarchar should be okay. Try using the BCP utility if you have millions of rows in the table.|||

I tried doing that and it gives an error saying string or binary data may be lost so the execution was cancelled.

I was able to do what I needed to by coding up a dummy method in C# that read everything into a datatable and then wrote it out to the new database, but this would be something nice to know how to do in T-Sql. Any idea why I was getting the error above. The only other two data types in the table are datetime and bit, but those fields match exactly no conversion should be necessary so the only reason I can think I'm getting that error is because I'm trying to convert a text field to a nvarchar().

Any way thanks for the help ndinakar!

|||

Try this in your query analyzer:

DECLARE @.tTABLE ( col1INT IDENTITY, col2TEXT, col3BIT )INSERT INTO @.tVALUES ('aaaaaaaaaaaaaaaaaa',0)SELECT *FROM @.tDECLARE @.t2TABLE (col1INT IDENTITY, col2NVARCHAR(50), col3BIT )INSERT INTO @.t2SELECT t1.col2, t1.col3FROM @.t t1SELECT *FROM @.t2

|||

I was essentially doing what you suggested ndinakar and was getting an error about truncating string or binary values.

I found this KB article with that error and by turning of ANSI WARNINGS I was able to get my data copied

http://support.microsoft.com/default.aspx/kb/255765

Thanks for your help!

Copying an ntext cell from one cell into another (destination row already exists

Hi!

What I'd like to do is:

UPDATE table1
SET
A_TEXT_COLUMN = (SELECT another_text_column
FROM table2
WHERE table2_id = @.precomputed_id_1)
WHERE table1_ID = @.precomputed_id_2

Since the cells are text, this does not work. Since the cell to be updated is in an already exitant row, it's not possible to simply use insert.

I'd like to do something like (PSEUDOcode):

WRITETEXT(table1.A_TEXT_COLUMN, READTEXT(@.textptr_initialised_to_point_at_target_c ell))

But the *actual* synatx of WRITETEXT and READTEXT seem totally inappropriate for any such trick...

Any hints or pointers HUGELY appreciated... THANXIs this a standard *problem*? Or an unsolved one (just an sqlserver limitation that must be dealt with in a totally different way??)

I saw 10 people read the thread but nobody answered, maybe the problem is a generally troublesome one with no good solutions...

Any ideas at all?

Thanx,

wOAst

copying all rows from one table into another existing table and overwriting data

i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.

any ideas on how i could do this easily?

thanksUSE Northwind
GO

SET NOCOUNT ON
SELECT * INTO myTable1 FROM Employees
GO

-- Have a Look

SELECT * FROM myTable1

-- Make a Backup

SELECT * INTO myTable2 FROM myTable1
GO

-- oops

DELETE FROM myTable1 WHERE EmployeeID BETWEEN 2 AND 6
GO

-- Have a Look

SELECT * FROM myTable1
GO

SET IDENTITY_INSERT myTable1 ON
GO

-- Recover the Rows

INSERT INTO myTable1 (
EmployeeID
, LastName
, FirstName
, Title
, TitleOfCourtesy
, BirthDate
, HireDate
, Address
, City
, Region
, PostalCode
, Country
, HomePhone
, Extension
, Photo
, Notes
, ReportsTo
, PhotoPath
)
SELECT EmployeeID
, LastName
, FirstName
, Title
, TitleOfCourtesy
, BirthDate
, HireDate
, Address
, City
, Region
, PostalCode
, Country
, HomePhone
, Extension
, Photo
, Notes
, ReportsTo
, PhotoPath
FROM myTable2
WHERE EmployeeID NOT IN (SELECT EmployeeID
FROM myTable1)
GO

SET IDENTITY_INSERT myTable1 OFF
GO

-- Have a Look

SELECT * FROM myTable1
GO

-- Clean up

DROP TABLE myTable1
DROP TABLE myTable2
GO

copy two table

hi,

How copy two table between two database?

copy 1 table1 to 2 table2

1 - cursor number,
2 - cursor number,
table1 - sourse table
table2 - destination table

Jaromi"ja" <jaromi111@.poczta.onet.pl> ???/???? ? ???? ???:
news:1etlgtor6kjsh.bx223caeyi5p$.dlg@.40tude.net...
> hi,
> How copy two table between two database?
> copy 1 table1 to 2 table2
> 1 - cursor number,
> 2 - cursor number,
> table1 - sourse table
> table2 - destination table
> Jaromi

Right click on table in EM -> all tasks -> export data...|||On Sun, 15 Aug 2004 20:42:08 +0200, ja wrote:

>hi,
>How copy two table between two database?
>copy 1 table1 to 2 table2
>1 - cursor number,
>2 - cursor number,
>table1 - sourse table
>table2 - destination table
>Jaromi

Hi Jaromi,

Probably something like this:

INSERT table2 (column1, column2, ...)
SELECT column1, column2, ...
FROM table1

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

copy table structure

Hi,
In SQL Server, Is there any DDL available to copy a table structure alone and not the data,
I believe 'SELECT * into new_table from table1' will copy both structure and data as well.
Please advice,
Thanks,
SmithaYou can do:

select top 0 * into new_Table from table1;

--or
select top 0 * into new_Table from table1 where 1=0;

-- or in SQL Server 2005
select top(0) * into new_Table from table1;

Copy Specific Cells between Tables based on Login ID

I want to copy FirstName and LastName cells from table1 to table2. Which FirstName and LastName cells depend on the username, i.e. which person has logged in.

Q1) How can this be done? (I have chosen to SELECT from Table1 and output to variables (C# code); then INSERT these variables into Table2. [Finding it very difficult]).

Any suggestions/comments would be appreciated. Thank you in advance.

You can achieve what you need in a single statement

In SQL Server and Oracle

insert into test3 (lib_id, lib_name, lib_desc)
select lib_id, lib_name, lib_desc from test2 where lib_id=1

copy some fields of table1 to an existing table2

I have two tables (table1 and table2) in a database of sqlserver 2000. I
want to add those fields of table1 who doesn't exist in table2, to table2.
How can I do that?
Thanks,
HugoEduco Gent wrote:
> I have two tables (table1 and table2) in a database of sqlserver 2000. I
> want to add those fields of table1 who doesn't exist in table2, to table2.
You will have to do this by hand unless you purchase a diff tool that is
designed to work against database schema. SQLCompare is a pretty popular
one.
Otherwise you will have to do it by hand.
ALTER TABLE table2
ADD COLUMN [NewColumn] <datatype>
See Books Online for more information.
http://msdn.microsoft.com/library/e..._aa-az_4e5h.asp
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Hi Educo
Check if following solution helps you.
I took two tables Table1 and Table2 (Formatting need to be done)
create table Table1(a int , b int , c int,d varchar(100),e nvarchar(100),f
text , g money)
create table Table2(a int , b int)
This proc will do the job.
Create Procedure Proc_AddCols
@.Tab1 Varchar(1000),
@.Tab2 Varchar(1000)
As
declare
@.sqlString Varchar(8000),
@.ColString Varchar(8000)
Begin
Set @.ColString = ''
Select @.ColString = @.ColString + 'Alter Table ' + IsNull(@.Tab2,'') + ' Add
' + IsNull(Column_Name,'') + ' ' + Case upper(IsNull(Data_Type,''))
When 'INT' then ' INT'
When 'VARCHAR' then ' VARCHAR(' + cast(IsNull(Character_Maximum_Length,'')
as Varchar) + ')'
When 'NVARCHAR' then ' NVARCHAR(' +
cast(IsNull(Character_Maximum_Length,'')
as Varchar) + ')'
Else ' ' + upper(IsNull(Data_Type,''))
end + ' ' + Case IsNull(IS_Nullable,'') when 'YES' then '' else ' NotNull'
end
From Information_Schema.Columns Where Table_Name Like @.Tab1 And Column_Name
not in
(Select Column_Name from Information_Schema.Columns Where Table_Name like
@.Tab2)
Exec(@.ColString)
End
Go
Exec Proc_AddCols 'Table1','Table2'
Go
Exten this stored procedure for your solution
Tell me if it solves your problem.
Vishal Khajuria
Sungard SCT India
"Educo Gent" wrote:

> I have two tables (table1 and table2) in a database of sqlserver 2000. I
> want to add those fields of table1 who doesn't exist in table2, to table2.
> How can I do that?
> Thanks,
> Hugo
>
>

Copy Row Of Data From Table to Table In Same DB

Hi.

Like the title says - how do i do this?

I was given the following example:

INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A'

The above statement threw the following error:

An explicit value for the identity column in table 'TABLE2' can only
be specified when a column list is used and IDENTITY_INSERT is ON.

Then, after filling in all the column names in my above select
statement I kept getting an error to the effect that the number of
source and destination columns don't match. This is because one column
"confirm_hash" does not exist in the destination table, just the
source table.

could somebody show me how to get this to work?

thanks!

PS - MS SQL SERVER EXPRESS 2005The syntax to use INSERT INTO is like this:

INSERT INTO TABLE2
(<column_list>)
SELECT <column_list>
FROM TABLE1
WHERE COL1 = 'A'

A few brief notes:
- the <column_listwill list your columns (like COL1, COL2, COL3, etc.)
- the <column_listmust contain the same number of columns in both clauses
(INSERT INTO and SELECT)
- if you do not specify the <column_listin the INSERT INTO clause (as you
did in your sample query), then the <column_listin SELECT must much all
columns in TABLE2
- the columns have to be of the same data type and size, being able to
implicitly convert, or explicitly converted via CAST/CONVERT
- in your case if the "confirm_hash" column does not exists in the
destination table, then you have to drop it from the column list (or alter
TABLE2 before the insert to add the column)
- you do not have to list the IDENTITY column as it will get automatically
the value based on the IDENTITY (of if you want to force a value in that
column, run before the query SET IDENTITY_INSERT TABLE2 ON)

If you post your CREATE TABLE statements for both tables, some sample data
and desired results you can get much better help.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 4, 1:54 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

The syntax to use INSERT INTO is like this:
>
INSERT INTO TABLE2
(<column_list>)
SELECT <column_list>
FROM TABLE1
WHERE COL1 = 'A'
>
A few brief notes:
- the <column_listwill list your columns (like COL1, COL2, COL3, etc.)
- the <column_listmust contain the same number of columns in both clauses
(INSERT INTO and SELECT)
- if you do not specify the <column_listin the INSERT INTO clause (as you
did in your sample query), then the <column_listin SELECT must much all
columns in TABLE2
- the columns have to be of the same data type and size, being able to
implicitly convert, or explicitly converted via CAST/CONVERT
- in your case if the "confirm_hash" column does not exists in the
destination table, then you have to drop it from the column list (or alter
TABLE2 before the insert to add the column)
- you do not have to list the IDENTITY column as it will get automatically
the value based on the IDENTITY (of if you want to force a value in that
column, run before the query SET IDENTITY_INSERT TABLE2 ON)
>
If you post your CREATE TABLE statements for both tables, some sample data
and desired results you can get much better help.
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Thanks Plamen,

I have followed your directions and that works (tested in QA).
Since the query is now getting kind of detailed, I have decided to
create a stored procedure out of this. I am getting an error:

Msg 102, Level 15, State 1, Procedure sp_MyStoredProcedure, Line 75
Incorrect syntax near ','.

Would you mind telling me why I am getting this error (and checking my
SPROC in general)? One note - I have added a final column (column18)
in my sproc that exists in Table2, but not in Table1.

Thanks, I really appreciate any feedback you can provide.

Peter

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:<Author: Last, First>
-- Create date: <Create Date: 4 June 2007>
-- Description:<Description: Table To Table Copy>
-- =============================================

CREATE PROCEDURE sp_MyStoredProcedure

@.column1 DATETIME = NULL,
@.column2 VARCHAR(50) = NULL,
@.column3 VARCHAR(50) = NULL,
@.column4 VARCHAR(50) = NULL,
@.column5 VARCHAR(50) = NULL,
@.column6 INT = NULL,
@.column7 VARCHAR(50) = NULL,
@.column8 VARCHAR(50) = NULL,
@.column9 INT = NULL,
@.column10 INT = NULL,
@.column11 INT = NULL,
@.column12 VARCHAR(50) = NULL,
@.column13 VARCHAR(50) = NULL,
@.column14 VARCHAR(50) = NULL,
@.column15 VARCHAR(50) = NULL,
@.column16 VARCHAR(50) = NULL,
@.column17 VARCHAR(50) = NULL,
@.column18 VARCHAR(50) = NULL

AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO Table1
(column1,
column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17)
SELECT column1,
column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17
FROM Table2 t2
WHERE t2.column1 = @.column1,
column2 = @.column2,
column3 = @.column3,
column4 = @.column4,
column5 = @.column5,
column6 = @.column6,
column7 = @.column7,
column8 = @.column8,
column9 = @.column9,
column10 = @.column10,
column11 = @.column11,
column12 = @.column12,
column13 = @.column13,
column14 = @.column14,
column15 = @.column15,
column16 = @.column16,
column17 = @.column17,
column18 = @.column18

END
GO|||The syntax error is because of the commas in the WHERE clause. The
conditions in the WHERE clause are logical expressions and you have to use
AND or OR between expressions based on what you need to filter. A trimmed
down example is:

INSERT INTO Table1
(column1,
column2)
SELECT column1,
column2
FROM Table2
WHERE column1 = @.column1
AND column2 = @.column2

All that said, I am a bit puzzled why you decided to write this stored
procedure and the purpose of passing those column parameters. If you just
need to copy the Table2 to Table1, then directly run the statement like
this:

INSERT INTO Table1
(column1,
column2,
-- ... the rest of the columns go here
column17)
SELECT column1,
column2,
-- ... the rest of the columns go here
column17
FROM Table2

And then if you have any filters that you need to apply to the columns from
Table2, you can add the WHERE clause. Also, you could wrap that statement in
a stored procedure, but I just do not see the purpose of passing all those
column parameters to the SP. Can you explain why you added them and how you
plan to execute the SP, and maybe an example of what parameters you pass?

If you are trying to perform something like dynamic searching (that is
filter on multiple variable conditions), then you may want to read Erland
Sommarskog's article on dynamic search conditions:
http://www.sommarskog.se/dyn-search.html
HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 4, 3:21 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

The syntax error is because of the commas in the WHERE clause. The
conditions in the WHERE clause are logical expressions and you have to use
AND or OR between expressions based on what you need to filter. A trimmed
down example is:
>
INSERT INTO Table1
(column1,
column2)
SELECT column1,
column2
FROM Table2
WHERE column1 = @.column1
AND column2 = @.column2
>
All that said, I am a bit puzzled why you decided to write this stored
procedure and the purpose of passing those column parameters. If you just
need to copy the Table2 to Table1, then directly run the statement like
this:
>
INSERT INTO Table1
(column1,
column2,
-- ... the rest of the columns go here
column17)
SELECT column1,
column2,
-- ... the rest of the columns go here
column17
FROM Table2
>
And then if you have any filters that you need to apply to the columns from
Table2, you can add the WHERE clause. Also, you could wrap that statement in
a stored procedure, but I just do not see the purpose of passing all those
column parameters to the SP. Can you explain why you added them and how you
plan to execute the SP, and maybe an example of what parameters you pass?
>
If you are trying to perform something like dynamic searching (that is
filter on multiple variable conditions), then you may want to read Erland
Sommarskog's article on dynamic search conditions:http://www.sommarskog.se/dyn-search.html
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Hi Plamen,

Thanks - you have been a ton of help.

OK, the situation is that I have a page that is a "click-back" from
a registration page. The user finds the code in his inbox and pastes
it in his http:// box for registration confirmation - you know the
deal.
Once that happens, the code I have been writing moves the data
the user input for registration from a temp table to the official
registered
users table. This is what we have been discussing in this thread.
So, all the values are registration values (reg date, firstName,
lastName,
city, state, zip code, security question, security answer, etc). There
is no identity column in common because the userID identity column
in the destination table will automatically increment upon insertion.

As for the SPROC decision, I decided to use a SPROC because of the
the size of the SQL statement - i thought it was a bit lengthly and
involved
so, i figured it turn it into a SPROC. I am guessing this is a poor
reason
to create a SPROC... maybe you could tell me when is the best time to
use them? I am kind of learning as I go.

Anyway, below is the original statement (inside the SqlConnection
statement). It works when I run it in SQL Express. Let me know if you
think it is more forgiving to do it this way.

Thanks again for your help.

MyConn As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("myConnStr").ConnectionString)
Dim MyCmd As New SqlCommand("INSERT INTO Users (regdate, pass, role,
squestion, sanswer, zcode, altemail, email, bdaymonth, bdayday,
bdayyear, gender, sitename, city, state, country, lastName, firstName)
SELECT regdate, pass, role, squestion, sanswer, zcode, altemail,
email, bdaymonth, bdayday, bdayyear, gender, sitename, city, state,
country, lastName, firstName FROM TempRegistration t WHERE t.confirm
= '8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077'", MyConn)|||Ok, now it is more clear what you are trying to do... :)

Yes, stored procedure is best here, as it can reuse previously cached
execution plan. Perhaps something like this:

CREATE PROCEDURE ConfirmUserRegistration
@.confirmation_cd NVARCHAR(50)
AS

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRAN

INSERT INTO Users
(egdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName)
SELECT regdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName
FROM TempRegistration
WHERE confirm = @.confirmation_cd;

COMMIT TRAN;

END TRY
BEGIN CATCH

IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN;
END
ELSE IF (XACT_STATE()) = 1
BEGIN
COMMIT TRAN;
END

DECLARE
@.ErrorMessage NVARCHAR(4000),
@.ErrorSeverity INT,
@.ErrorState INT,
@.ErrorNumber INT,
@.ErrorLine INT,
@.ErrorProcedure NVARCHAR(200),
@.ErrMessage NVARCHAR(4000);

SELECT
@.ErrorMessage = ERROR_MESSAGE(),
@.ErrorSeverity = ERROR_SEVERITY(),
@.ErrorState = ERROR_STATE(),
@.ErrorNumber = ERROR_NUMBER(),
@.ErrorLine = ERROR_LINE(),
@.ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');

SET @.ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+
N'Message: '+ ERROR_MESSAGE();

RAISERROR(
@.ErrMessage,
@.ErrorSeverity,
1,
@.ErrorNumber,
@.ErrorSeverity,
@.ErrorState,
@.ErrorProcedure,
@.ErrorLine
);

END CATCH;

GO

Then in your code call the SP like this (I just typed here, please check for
syntax, I've been using more C# lately and could be missing something):

Using connection As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("myConnStr").ConnectionString)

Try
Dim command As SqlCommand = New SqlCommand( _
"ConfirmUserRegistration",
connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter = command.Parameters.Add( _
"@.confirmation_cd ",
SqlDbType.NVarChar, _
50)
parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"

command.Connection.Open()
command.ExecuteNonQuery()

Catch exSQL As SqlException
' Log and show error

Catch exGen As Exception
' Log and show error

End Try

End Using

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 4, 8:15 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Ok, now it is more clear what you are trying to do... :)
>
Yes, stored procedure is best here, as it can reuse previously cached
execution plan. Perhaps something like this:
>
CREATE PROCEDURE ConfirmUserRegistration
@.confirmation_cd NVARCHAR(50)
AS
>
SET NOCOUNT ON;
>
BEGIN TRY
>
BEGIN TRAN
>
INSERT INTO Users
(egdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName)
SELECT regdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName
FROM TempRegistration
WHERE confirm = @.confirmation_cd;
>
COMMIT TRAN;
>
END TRY
BEGIN CATCH
>
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN;
END
ELSE IF (XACT_STATE()) = 1
BEGIN
COMMIT TRAN;
END
>
DECLARE
@.ErrorMessage NVARCHAR(4000),
@.ErrorSeverity INT,
@.ErrorState INT,
@.ErrorNumber INT,
@.ErrorLine INT,
@.ErrorProcedure NVARCHAR(200),
@.ErrMessage NVARCHAR(4000);
>
SELECT
@.ErrorMessage = ERROR_MESSAGE(),
@.ErrorSeverity = ERROR_SEVERITY(),
@.ErrorState = ERROR_STATE(),
@.ErrorNumber = ERROR_NUMBER(),
@.ErrorLine = ERROR_LINE(),
@.ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');
>
SET @.ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+
N'Message: '+ ERROR_MESSAGE();
>
RAISERROR(
@.ErrMessage,
@.ErrorSeverity,
1,
@.ErrorNumber,
@.ErrorSeverity,
@.ErrorState,
@.ErrorProcedure,
@.ErrorLine
);
>
END CATCH;
>
GO
>
Then in your code call the SP like this (I just typed here, please check for
syntax, I've been using more C# lately and could be missing something):
>
Using connection As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("myConnStr").ConnectionString)
>
Try
Dim command As SqlCommand = New SqlCommand( _
"ConfirmUserRegistration",
connection)
command.CommandType = CommandType.StoredProcedure
>
Dim parameter As SqlParameter = command.Parameters.Add( _
"@.confirmation_cd ",
SqlDbType.NVarChar, _
50)
parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"
>
command.Connection.Open()
command.ExecuteNonQuery()
>
Catch exSQL As SqlException
' Log and show error
>
Catch exGen As Exception
' Log and show error
>
End Try
>
End Using
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Thanks again Plamen. This thread has been very helpful.

I have a final question. How do I handle cases where the confirmation
code
doesn't exist? Say a user is trying to guess a code - How would the
stored procedure catch a mismatch and return the result to VB.NET so
the appropriate message can be sent to the user?

Thanks again for all your help.
Peter|||On Jun 5, 7:17 am, pbd22 <dush...@.gmail.comwrote:

Quote:

Originally Posted by

On Jun 4, 8:15 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:
>
>
>

Quote:

Originally Posted by

Ok, now it is more clear what you are trying to do... :)

>

Quote:

Originally Posted by

Yes, stored procedure is best here, as it can reuse previously cached
execution plan. Perhaps something like this:

>

Quote:

Originally Posted by

CREATE PROCEDURE ConfirmUserRegistration
@.confirmation_cd NVARCHAR(50)
AS

>

Quote:

Originally Posted by

SET NOCOUNT ON;

>

Quote:

Originally Posted by

BEGIN TRY

>

Quote:

Originally Posted by

BEGIN TRAN

>

Quote:

Originally Posted by

INSERT INTO Users
(egdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName)
SELECT regdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName
FROM TempRegistration
WHERE confirm = @.confirmation_cd;

>

Quote:

Originally Posted by

COMMIT TRAN;

>

Quote:

Originally Posted by

END TRY
BEGIN CATCH

>

Quote:

Originally Posted by

IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN;
END
ELSE IF (XACT_STATE()) = 1
BEGIN
COMMIT TRAN;
END

>

Quote:

Originally Posted by

DECLARE
@.ErrorMessage NVARCHAR(4000),
@.ErrorSeverity INT,
@.ErrorState INT,
@.ErrorNumber INT,
@.ErrorLine INT,
@.ErrorProcedure NVARCHAR(200),
@.ErrMessage NVARCHAR(4000);

>

Quote:

Originally Posted by

SELECT
@.ErrorMessage = ERROR_MESSAGE(),
@.ErrorSeverity = ERROR_SEVERITY(),
@.ErrorState = ERROR_STATE(),
@.ErrorNumber = ERROR_NUMBER(),
@.ErrorLine = ERROR_LINE(),
@.ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');

>

Quote:

Originally Posted by

SET @.ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+
N'Message: '+ ERROR_MESSAGE();

>

Quote:

Originally Posted by

RAISERROR(
@.ErrMessage,
@.ErrorSeverity,
1,
@.ErrorNumber,
@.ErrorSeverity,
@.ErrorState,
@.ErrorProcedure,
@.ErrorLine
);

>

Quote:

Originally Posted by

END CATCH;

>

Quote:

Originally Posted by

GO

>

Quote:

Originally Posted by

Then in your code call the SP like this (I just typed here, please check for
syntax, I've been using more C# lately and could be missing something):

>

Quote:

Originally Posted by

Using connection As New
SqlConnection(ConfigurationManager.ConnectionStrin gs("myConnStr").ConnectionString)

>

Quote:

Originally Posted by

Try
Dim command As SqlCommand = New SqlCommand( _
"ConfirmUserRegistration",
connection)
command.CommandType = CommandType.StoredProcedure

>

Quote:

Originally Posted by

Dim parameter As SqlParameter = command.Parameters.Add( _
"@.confirmation_cd ",
SqlDbType.NVarChar, _
50)
parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"

>

Quote:

Originally Posted by

command.Connection.Open()
command.ExecuteNonQuery()

>

Quote:

Originally Posted by

Catch exSQL As SqlException
' Log and show error

>

Quote:

Originally Posted by

Catch exGen As Exception
' Log and show error

>

Quote:

Originally Posted by

End Try

>

Quote:

Originally Posted by

End Using

>

Quote:

Originally Posted by

HTH,

>

Quote:

Originally Posted by

Plamen Ratchevhttp://www.SQLStudio.com

>
Thanks again Plamen. This thread has been very helpful.
>
I have a final question. How do I handle cases where the confirmation
code
doesn't exist? Say a user is trying to guess a code - How would the
stored procedure catch a mismatch and return the result to VB.NET so
the appropriate message can be sent to the user?
>
Thanks again for all your help.
Peter

Actually, I have a bit of an addition to the above "final
question" :) .
I am also wondering where in the SPROC that you have provided
I could place a confirmation that the insert statement has happened
successfully? Or, how do I include a check within the SPROC to
verify successful insertion? I ask because, once the data has been
successfully moved from the Temp table to the Users table, I will need
to delete the source row in the Temp table. I can figure out how to
code the deletion but am not quite sure how the "onSuccess" statement
looks that indicates that it is OK to go ahead and delete the row.

Thanks again!|||I will try to sketch here the answer to both questions:

1). To detect that the confirmation code exists, you can check the number of
rows affected by the insert (using @.@.rowcount), and then return that value
to the client using an output parameter. If the number of rows is 1 (I
assume you have either a primary key or UNIQUE constraint on the
confirmation code column so duplicates are not possible), then you know you
had a code match, if 0 then there was no match. Here is an abbreviated code
of the SP:

CREATE PROCEDURE ConfirmUserRegistration
@.confirmation_cd NVARCHAR(50),
@.numrows INT OUTPUT
AS
-- ...
BEGIN TRAN

INSERT INTO Users
(egdate,
pass,
-- ...
firstName)
SELECT regdate,
pass,
-- ...
firstName
FROM TempRegistration
WHERE confirm = @.confirmation_cd;

SET @.numrows = @.@.rowcount;

DELETE FROM TempRegistration
WHERE confirm = @.confirmation_cd;

COMMIT TRAN;

Note that you can directly perform the DELETE without checking the result of
the INSERT, because if there is no match then there will be no rows deleted.
If you want you can have an IF @.numrows 0 before executing the DELETE
statement to run it only when there is a match.

2). On your client side, you have to define the output parameter and then
check the results, abbreviated code here:

'... connection, command and first parameter initialization go here
' now add the output parameter
parameter = command.Parameters.Add( _
"@.numrows",
SqlDbType.Int)
parameter.Direction = ParameterDirection.Output

'... open the connection and execute command go here
' retrieve the output value
If (command.Parameters("@.numrows").Value = 1) Then
' we have a match and confirmation is complete
Else
' confirmation code is invalid - show alert
End If

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 5, 8:36 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

I will try to sketch here the answer to both questions:
>
1). To detect that the confirmation code exists, you can check the number of
rows affected by the insert (using @.@.rowcount), and then return that value
to the client using an output parameter. If the number of rows is 1 (I
assume you have either a primary key or UNIQUE constraint on the
confirmation code column so duplicates are not possible), then you know you
had a code match, if 0 then there was no match. Here is an abbreviated code
of the SP:
>
CREATE PROCEDURE ConfirmUserRegistration
@.confirmation_cd NVARCHAR(50),
@.numrows INT OUTPUT
AS
-- ...
BEGIN TRAN
>
INSERT INTO Users
(egdate,
pass,
-- ...
firstName)
SELECT regdate,
pass,
-- ...
firstName
FROM TempRegistration
WHERE confirm = @.confirmation_cd;
>
SET @.numrows = @.@.rowcount;
>
DELETE FROM TempRegistration
WHERE confirm = @.confirmation_cd;
>
COMMIT TRAN;
>
Note that you can directly perform the DELETE without checking the result of
the INSERT, because if there is no match then there will be no rows deleted.
If you want you can have an IF @.numrows 0 before executing the DELETE
statement to run it only when there is a match.
>
2). On your client side, you have to define the output parameter and then
check the results, abbreviated code here:
>
'... connection, command and first parameter initialization go here
' now add the output parameter
parameter = command.Parameters.Add( _
"@.numrows",
SqlDbType.Int)
parameter.Direction = ParameterDirection.Output
>
'... open the connection and execute command go here
' retrieve the output value
If (command.Parameters("@.numrows").Value = 1) Then
' we have a match and confirmation is complete
Else
' confirmation code is invalid - show alert
End If
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Thanks a ton Plamen,

This thread was immensely helpful. I really appreciate it.
As a final note, for anybody that is using this thread for their own
registration system, you need to comment out NOCOUNT ON to
get the appropriate response from the SPROC (at least, I think
that is what solved my "no response" problem).

Thanks again Plamen!|||>Like the title says - how do i do this? .. An explicit value for the identity column in table 'TABLE2' can only be specified when a column list is used and IDENTITY_INSERT is ON. <<

Let us go back to RDBMS basics. A row models a complete fact ("John
bought a squid on 2007-05-12"). This fact should appear in one
table, one time and in one way in the entire schema. This is the
foundation of normalization.

In the old days, with punch cards, paper files, etc. we would
physically move these physical records from one physical location to
another physical location. We had redundancy and we want to get rid
of it. You want to increase it.

You should never use IDENTITY in an RDBMS; you want to have a
relational key. A key has nothing to do with the physical location of
the data in the hardware; it is based on the nature of the data being
modeled.

You entire approach is that of someone managing a 1950's paper file
system. Please read a book before you code again. If you want
stinking dirty kludges, then you can get them in Newsgroup; but being
a good RDBMS programmer will take YEARS of hard work. Be better than
that.