Copying specific data from table in DB1 to table in DB2

I need to copy the following columns from my Employee table in my Performance DB to my Employee table in my VacationRequest DB:
CompanyID, FacilityID, EmployeeID, FirstName, LastName,
[Password] = 'nippert', Role = 'Employee'

I tried the advice on this website but to no avail:
http://www.w3schools.com/sql/sql_select_into.aspHi Jason,INSERT INTO should fit your needs.

Copying Records into a different table using triggers

I am in the process of writing a trigger that will insert a new record into
another table when a record is added to a specific table. I was wondering if
there is a way to just 'copy' the entire row without using INSERT INTO
...and VALUES?
adv-thanks-anceYou could use
INSERT INTO [AnotherTable]
SELECT * FROM inserted
assuming that [AnotherTable] has the same number of columns, in the same
order, with the same datatypes as the table that has the trigger on it.
"CSHARPITPRO" wrote:

> I am in the process of writing a trigger that will insert a new record int
o
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Thanks Mark,
Is this being copied from the 'INSERTED' table in SQL?
"Mark Williams" wrote:
> You could use
> INSERT INTO [AnotherTable]
> SELECT * FROM inserted
> assuming that [AnotherTable] has the same number of columns, in the same
> order, with the same datatypes as the table that has the trigger on it.
> --
> "CSHARPITPRO" wrote:
>|||I am not sure if there is a way to do this (I have not seen one) but I would
avoid this like the plague.
The tables are identical today, but who knows what will change tomorrow.
Something as simple as adding and audit or archive date to either table
would break any app using such an approach. Structures have a tendency to
evolve over time, and any code that assumes two structures will be identical
is begging for trouble down the line.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> I am in the process of writing a trigger that will insert a new record
into
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Yes. If all you want to do is a straight copy of the rows inserted into the
table with the trigger on it, you can use the syntax
INSERT INTO [AnotherTable] SELECT * FROM inserted
inserted is the virtual table that conains the inserted rows that caused the
trigger to fire. Note that inserted will also contain rows when an UPDATE is
performed (The new values will be in the inserted virtual table, and the old
values will be in the deleted virtual table).
--
If"CSHARPITPRO" wrote:
> Thanks Mark,
> Is this being copied from the 'INSERTED' table in SQL?
> "Mark Williams" wrote:
>|||Thanks Jim,
What do you think the best method is to copy the 'changed' records to
my AuditTable with out using the INSERTED Table? Should I just use INSERT
INTO ..and VALUES?
Thanks
"Jim Underwood" wrote:

> I am not sure if there is a way to do this (I have not seen one) but I wou
ld
> avoid this like the plague.
> The tables are identical today, but who knows what will change tomorrow.
> Something as simple as adding and audit or archive date to either table
> would break any app using such an approach. Structures have a tendency to
> evolve over time, and any code that assumes two structures will be identic
al
> is begging for trouble down the line.
>
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> into
> if
>
>|||I would use insert into with a specific list of values.
i.e. Insert into table1 (field1, field2, field3) values (select field1,
field2, field3 from inserted)
As everyone else mentioned you can use "insert into table select * from
inserted", but if any structures change you will runinto trouble.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>
would
to
identical
wondering|||Yes. Performance and consistency will both be served if you do so. It does
require a bit more work now, and a bit more when you make changes, but if
you design things, this won't be too much of a problem. You can also use
the information_schema.columns view to get the columns that are needed and
build a quicky insert generator (or use the ones in QA/SSMS) to do much of
the hard work of typing out column names.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>|||On Wed, 18 Jan 2006 09:23:03 -0800, CSHARPITPRO wrote:

>Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
>my AuditTable with out using the INSERTED Table? Should I just use INSERT
>INTO ..and VALUES?
Hi CSHARPITPRO,
No. INSERT ... VALUES inserts just one row at a time; a trigger can be
fired with a large number of rows in the inserted pseudo-table.
Use
INSERT INTO Tablename (Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM inserted
Hugo Kornelis, SQL Server MVP

Copying data

Hello,
I need to copy from around 9 specific tables in one database (A) to another
database (B). Database B contains an exact image of those 9 tables.
Here is my requirement -
1) Altogether there could be around 4 million records across those tables.
2) The data would be moved from A to B every night.
3) Using SSIS is not an option as I would not be able to call this from Ax.
But I will
be able to call stored procs, statements etc.
Could anyone suggest me an optimum solution please?
Many thanks,
Harish Mohanbabu
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
Sorry - my mistake.
Tables in database B is not an exact mirror image. Those 9 tables contain 2
new columns which is not there in Database A.
Harish Mohanbabu
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
"Harish Mohanbabu" wrote:

> Hello,
> I need to copy from around 9 specific tables in one database (A) to another
> database (B). Database B contains an exact image of those 9 tables.
> Here is my requirement -
> 1) Altogether there could be around 4 million records across those tables.
> 2) The data would be moved from A to B every night.
> 3) Using SSIS is not an option as I would not be able to call this from Ax.
> But I will
> be able to call stored procs, statements etc.
> Could anyone suggest me an optimum solution please?
> Many thanks,
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/

Copying data

Hello,
I need to copy from around 9 specific tables in one database (A) to another
database (B). Database B contains an exact image of those 9 tables.
Here is my requirement -
1) Altogether there could be around 4 million records across those tables.
2) The data would be moved from A to B every night.
3) Using SSIS is not an option as I would not be able to call this from Ax.
But I will
be able to call stored procs, statements etc.
Could anyone suggest me an optimum solution please?
Many thanks,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/Sorry - my mistake.
Tables in database B is not an exact mirror image. Those 9 tables contain 2
new columns which is not there in Database A.
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
"Harish Mohanbabu" wrote:

> Hello,
> I need to copy from around 9 specific tables in one database (A) to anothe
r
> database (B). Database B contains an exact image of those 9 tables.
> Here is my requirement -
> 1) Altogether there could be around 4 million records across those tables.
> 2) The data would be moved from A to B every night.
> 3) Using SSIS is not an option as I would not be able to call this from Ax
.
> But I will
> be able to call stored procs, statements etc.
> Could anyone suggest me an optimum solution please?
> Many thanks,
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/

Copying data

Hello,
I need to copy from around 9 specific tables in one database (A) to another
database (B). Database B contains an exact image of those 9 tables.
Here is my requirement -
1) Altogether there could be around 4 million records across those tables.
2) The data would be moved from A to B every night.
3) Using SSIS is not an option as I would not be able to call this from Ax.
But I will
be able to call stored procs, statements etc.
Could anyone suggest me an optimum solution please?
Many thanks,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/Sorry - my mistake.
Tables in database B is not an exact mirror image. Those 9 tables contain 2
new columns which is not there in Database A.
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
"Harish Mohanbabu" wrote:
> Hello,
> I need to copy from around 9 specific tables in one database (A) to another
> database (B). Database B contains an exact image of those 9 tables.
> Here is my requirement -
> 1) Altogether there could be around 4 million records across those tables.
> 2) The data would be moved from A to B every night.
> 3) Using SSIS is not an option as I would not be able to call this from Ax.
> But I will
> be able to call stored procs, statements etc.
> Could anyone suggest me an optimum solution please?
> Many thanks,
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/

Copy tables between databases using variables

I am trying to find the best way to copy specific tables from one database
to another when the source and target database names are not always the
same. Can you use variables to specify (or prompt the user) to provide
source and target databases? The target database will exist with the the
same tables as the source. The tables to copy will always be the same.

Example:
UserA wants to copy 10 tables from Data1 to Data2
UserB wants to copy 10 tables from Data4 to Data5

I'm sure a script can do this in Query Analyzer but is there a more user
friendly method when the user has ony standard SQL tools?

Thanks in advance.Hi

You could use DTS and change the source/destination tables:
http://www.sqldts.com/default.aspx?213

You could use dynamic SQL if you don't open yourself to SQL injection:
http://www.sommarskog.se/dynamic_sql.html

John

<rdraider@.sbcglobal.net> wrote in message
news:5GRWb.23042$V57.1004@.newssvr27.news.prodigy.c om...
> I am trying to find the best way to copy specific tables from one database
> to another when the source and target database names are not always the
> same. Can you use variables to specify (or prompt the user) to provide
> source and target databases? The target database will exist with the the
> same tables as the source. The tables to copy will always be the same.
> Example:
> UserA wants to copy 10 tables from Data1 to Data2
> UserB wants to copy 10 tables from Data4 to Data5
> I'm sure a script can do this in Query Analyzer but is there a more user
> friendly method when the user has ony standard SQL tools?
> Thanks in advance.

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