Copying rows from one table to another 1

[Manna]

I have two tables in my SQL Server 7.0 database that are identical in format. I want to use T-SQL to find certain rows from the first table and load them into the second table. How do I do that?

Manna

 

[RajD]

insert tableB
select * from tableA
where <filter_condition>

 

[tlbroadbent]

Insert Into table2
Select * From Table1
Where <condition> Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Manna]

It worked! Thank you both very much! Believe it or not, our DBA and I were both stumped on this one. He has the excuse that he's sick, though. Manna

 

[Manna]

It worked on the test database, but on the live database I encounter the following error message:

Server: Msg 273, Level 16, State 1, Line 3
Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.

Is there an alternative to tediously listing the dozens of fields in this table? I tried updating the msrepl_synctran_ts field in the source table, reseting all the values to NULL. The update worked, but the Insert still fails with the same error message. Manna

 

[tlbroadbent]

You need to list the columns. Use a script like the following to generate the Insert statement.

use MyDatabase
go
declare @sql1 nvarchar(4000), @sql2 nvarchar(4000)

select @sql1=@sql1+c.name+', '
from syscolumns c inner join sysobjects o
on c.id=o.id
where o.id=object_id('table1')
--eliminate timestamp column
And c.name not like '%ts%'
order by colorder

Set @sql1=left(@sql1, len(@sql1)-1)
Set @sql2='Insert Into table2 (' + @sql1 + ')' +
char(10) + 'Select ' + @sql1 + ' From table1'

Print @sql2
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Manna]

TL,

Thanks again. That looks simple enough. I'll test it out when I get done with this morning's urgent project. Manna

 

[Manna]

tlbroadbent,

I'm finally back to the original project.

I've modified the script you offered to read as follows:
=======================
use DOL
go
declare @sql1 nvarchar(4000), @sql2 nvarchar(4000)

select @sql1=@sql1+c.name+', '
from syscolumns c inner join sysobjects o
on c.id=o.id
where o.id=object_id('Solicit')
--eliminate timestamp column
And c.name not like '%ts%'
order by colorder

Set @sql1=left(@sql1, len(@sql1)-1)
Set @sql2='Insert Into SolBack (' + @sql1 + ')' +
char(10) + 'Select ' + @sql1 + ' From Solicit' +
char(10) + ' Where Camp_ref like ' + '_0GG0SQN55%' + ''

Print @sql2
===========================
I'm running this in Query Analyzer, but without any apparent results. The version without the &quot;where&quot; clause also runs without errors but also without any apparent results. Nothing shows up in the results window and the row count is 0. What am I doing wrong?
Manna

 

[tlbroadbent]

I just realized that the code I provided doesn't work in SQL 7. It does work in SQL 2000. Here is some code that works in SQL 7.

declare @sql1 nvarchar(4000), @sql2 nvarchar(4000)
declare @name nvarchar(40)

Declare col_list cursor for
Select c.name
From syscolumns c inner join sysobjects o
on c.id=o.id
where o.id=object_id('Solicit')
--eliminate timestamp column
And c.name not like '%ts%'
order by colorder

Open col_list
Fetch Next From Col_list Into @name

WHILE @@FETCH_STATUS = 0
BEGIN
Set @sql1=@sql1+rtrim(@name)+', '
Fetch Next From Col_list Into @name
END

Set @sql1=left(@sql1, len(@sql1)-1)
Set @sql2='Insert Into SolBack (' + @sql1 + ')' +
char(10) + 'Select ' + @sql1 + ' From Solicit' +
char(10) + ' Where Camp_ref like ' + '_0GG0SQN55%' + ''

Print @sql2

close col_list
deallocate col_list Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Manna]

tlbroadbent,

This version doesn't appear to work for me, either. Nothing gets loaded into my target table, and the results pane doesn't show any activity. But it parses okay

Maybe I'll just bite the bullet and list out all the fields. It can't be THAT time consuming, after all. Manna

 

[tlbroadbent]

The code doesn't do the actual insert. It only creates the SQL. You need to copy the output of the script and paste it into a query window. Then execute the insert statement.

Having said that, I need to ask, &quot;Does the script ouput a SQL statement?&quot; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Manna]

No, the script doesn't output an SQL statement. It doesn't output anything at all. I'm executing it via Query Analyzer. Is that the correct thing to do? I just paste the code in, parse it, and execute it. Manna

 

[Manna]

The needed rows have been successfully transferred to the target table. I used a Query in the DTS Wizard. Manna

 

[tlbroadbent]

I'm glad you finished your task. I'd just like to clarify what happened or didn't happen.

Query Analyzer has two output windows. One for results (grid) and another for messages. The scripts I provided would have output to the messages area because the output was via a PRINT statement rather than a select statement. Did you look on the messages tab? There had to be output because variable @sql2, that is printed, has constant values assigned. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Manna]

tlbroadbent,

I find a Results tab in Query Analyzer, which is ordinarily where the output from my T-SQL queries ends up. I'm running SQL Server 7.00.623.

If I switch from Results in Text to Results in Grid, then the Messages display in a separate tab from the results. But I still find nothing under either the Results or the Messages tab when I run the above commands in the Query Analyzer. I couldn't say why. It &quot;executes&quot; quickly, possibly too quickly. Manna

 

[tlbroadbent]

Have you looked to see if the &quot;No Exec&quot; query option is checked on? If that is not the problem then try this.

Change Print @sql2 to Select @sql2. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Manna]

Under the Query menu / Current Connection Options, I do NOT have &quot;no execute&quot; checked. Further, the other queries that I run produce results in the Results pane.

Using Select @sql2, I get results . . . sort of. A blank header row, then NULL and &quot;(1 row(s) affected)&quot;. Manna

 

[tlbroadbent]

Add the following line after the declare statements in the script.


Set @sql1=''
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Manna]

Now I get the &quot;Insert into&quot; part of the query in the Results pane. And &quot;Sele&quot;. Why is the select statement being truncated? Manna

 

[tlbroadbent]

The select is truncated because of the query settings. Theere is an option for maximum text length. Change the setting or use the Print statement in place of the Select @sql2 and you should see the entire statement. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.