Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copying rows from one table to another 1

Status
Not open for further replies.

Manna

Programmer
Joined
Sep 27, 2001
Messages
14
Location
US
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
 
insert tableB
select * from tableA
where <filter_condition>
 

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.
 
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
 
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
 

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.
 
TL,

Thanks again. That looks simple enough. I'll test it out when I get done with this morning's urgent project. 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
 

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.
 
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
 

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.
 
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
 
The needed rows have been successfully transferred to the target table. I used a Query in the DTS Wizard. Manna
 

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.
 
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
 

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.
 
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
 

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.
 
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
 

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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top