Copying Records

  • Thread starter Thread starter Vayse
  • Start date Start date
V

Vayse

At the moment, each client has an Agreement. Off this agreement table are
around 20 other tables, each detailing a different section of the Agreement.
Now, I want to change the database so there is an Agreement per year. So
when the Agreement deadline is reached, the user can create a new
Agreeement, which is a copy of the old one. Then the user works on the new
Agreement from now on. This also means that each section should be copied,
but will now have the new AgreementID.

Now, if was just using Access, I would know what to do. Lets say I was
copying the Computers sections.
1) Create a new query.
2) Add the Computers table
3) Drag all the fields down, except for the AgreementID
4) Change the query to append, point to the Computers table. Each field
would then map
5) Make the AgreementID a parameter

Are there any such shortcuts in SQL Server? All the sections have at least
50 fields, so I don't want to type them all out.
I'm using SQL 2000 and Access 2000 adp. I also have Access XP on another PC,
if there is a feature there that would be useful.

Thanks
Vayse
 
Vayse said:
At the moment, each client has an Agreement. Off this agreement table are
around 20 other tables, each detailing a different section of the
Agreement.
Now, I want to change the database so there is an Agreement per year. So
when the Agreement deadline is reached, the user can create a new
Agreeement, which is a copy of the old one. Then the user works on the new
Agreement from now on. This also means that each section should be copied,
but will now have the new AgreementID.

Now, if was just using Access, I would know what to do. Lets say I was
copying the Computers sections.
1) Create a new query.
2) Add the Computers table
3) Drag all the fields down, except for the AgreementID
4) Change the query to append, point to the Computers table. Each field
would then map
5) Make the AgreementID a parameter

Are there any such shortcuts in SQL Server? All the sections have at least
50 fields, so I don't want to type them all out.
I'm using SQL 2000 and Access 2000 adp. I also have Access XP on another
PC, if there is a feature there that would be useful.
Click to expand...

You don't have to type out the column names. Using the Object Browser in
Query Analyzer you can drag the columns node into the editing window and QA
will automatically insert the column names. Do something like this:

INSERT INTO Computers (col1, col2, AgreementID)
SELECT col1, col2, @NewAgreementID
FROM Computers
WHERE AgreementID = @OldAgreementID;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
 
David Portas said:
You don't have to type out the column names. Using the Object Browser in
Query Analyzer you can drag the columns node into the editing window and
QA will automatically insert the column names. Do something like this:

INSERT INTO Computers (col1, col2, AgreementID)
SELECT col1, col2, @NewAgreementID
FROM Computers
WHERE AgreementID = @OldAgreementID;
Click to expand...

Thanks. I often dragged individual columns in QA, never realised you could
drag the columns node.
You learn something new every day!
Vayse
 
Back
Top