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 accross databases for backup

I am not an expert in SQL
I am faced with the task of copying certain attributes froma table in a
database to create a table in another database to have a sort of refined
backup database.I have to do it through a script to run in Sqlserver query
analyser I have created a query of sort ->
select attrib1, attrib2. into back_up_database_table From
Actual_database..Actual_table where <some condition>
I am logged to query analyser using the backup_database
I am promted to use the command sp_addlinkedserver for the actual database
however still i am getting the error SQLserver doesnot exist or access denie
d
How can I use an accross databes script
Can any help
Thanks in advance
AbhishekWhat kind of a DB are you copying from? Is it a SQL to SQL or access to SQL
?
"adg" wrote:

> I am not an expert in SQL
> I am faced with the task of copying certain attributes froma table in a
> database to create a table in another database to have a sort of refined
> backup database.I have to do it through a script to run in Sqlserver query
> analyser I have created a query of sort ->
> select attrib1, attrib2. into back_up_database_table From
> Actual_database..Actual_table where <some condition>
> I am logged to query analyser using the backup_database
> I am promted to use the command sp_addlinkedserver for the actual database
> however still i am getting the error SQLserver doesnot exist or access den
ied
> How can I use an accross databes script
> Can any help
> Thanks in advance
> Abhishek
>|||When selecting from a table on a linked SQL Server, keep in mind that the
server name prefix and object owner (typically DBO) are required. For
example, if you have a linked server called SERVERNAME:
select attrib1, attrib2. into back_up_database_table From
SERVERNAME.Actual_database.DBO.Actual_table where <some condition>
"adg" <u17748@.uwe> wrote in message news:5a7c9fe20d792@.uwe...
>I am not an expert in SQL
> I am faced with the task of copying certain attributes froma table in a
> database to create a table in another database to have a sort of refined
> backup database.I have to do it through a script to run in Sqlserver query
> analyser I have created a query of sort ->
> select attrib1, attrib2. into back_up_database_table From
> Actual_database..Actual_table where <some condition>
> I am logged to query analyser using the backup_database
> I am promted to use the command sp_addlinkedserver for the actual database
> however still i am getting the error SQLserver doesnot exist or access
> denied
> How can I use an accross databes script
> Can any help
> Thanks in advance
> Abhishek|||I was copying a sqlserver databases Thanks JT giving the fullname of the
database has solved the problem.Its working fine.
Thanks again
JT wrote:
>When selecting from a table on a linked SQL Server, keep in mind that the
>server name prefix and object owner (typically DBO) are required. For
>example, if you have a linked server called SERVERNAME:
>select attrib1, attrib2. into back_up_database_table From
>SERVERNAME.Actual_database.DBO.Actual_table where <some condition>
>
>[quoted text clipped - 13 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200601/1