Copy or insert Columns from a table in another Database, How?

Hallo,
I thought it is easily done, but no. I have 3 columns of hundreds of data i
n a table in another Database within thesame server that i want to move to m
y production Database. I have tried Select into, copy, insert method... wit
hout success.
Please help.
ThanksHi,
If the structure of the table in production database is identical then use
Insert into
Insert into productiondatabase..table_name(col1,col2,col3) select
col1,col2,col3 from devdb..table
Above command will fail incase the same table have any other column which
will not allow "NOT NULL"
I have tried Select into, copy, insert method... without success.
Tell the error you are getting.
Thanks
Hari
MCDBA
"Ototofioto" <anonymous@.discussions.microsoft.com> wrote in message
news:6ED03A97-26F9-483A-93B9-3BFB8F28C87D@.microsoft.com...
> Hallo,
> I thought it is easily done, but no. I have 3 columns of hundreds of data
in a table in another Database within thesame server that i want to move to
my production Database. I have tried Select into, copy, insert method...
without success.
> Please help.
> Thanks

Copy ID from one table to another as foreign key

Hi,
I have two tables, one, called tblDoctor, with
docNo, docName . . .
the other, called tblPatient with
pntNo, pntName . . .
I want to insert a specific docNo value from one row in tblDoctor into
a specific row in tblPatient using a stored procedure within ADO.
Can i have an SQL statement along the lines of:
INSERT INTO tblPatient (docNo)
WHERE pntName = "a value"
SELECT docNo
FROM tblDoctor WHERE docName = "another value"
If not, has anyone got any suggestions on alternative methods of
accomplishing the same thing?
ThanksTry,
update tblPatient
set docNo = (SELECT docNo FROM tblDoctor WHERE docName = "another value")
WHERE pntName = "a value"
Be sure that you get just one docNo for a specific docName, if not sql
server will give an error. A trick can be to use "TOP 1" in the select
statement, but this is not the correct way to do this.
AMB
"Assimalyst" wrote:

> Hi,
> I have two tables, one, called tblDoctor, with
> docNo, docName . . .
> the other, called tblPatient with
> pntNo, pntName . . .
> I want to insert a specific docNo value from one row in tblDoctor into
> a specific row in tblPatient using a stored procedure within ADO.
> Can i have an SQL statement along the lines of:
> INSERT INTO tblPatient (docNo)
> WHERE pntName = "a value"
> SELECT docNo
> FROM tblDoctor WHERE docName = "another value"
> If not, has anyone got any suggestions on alternative methods of
> accomplishing the same thing?
> Thanks
>|||I've guessed what your tables should look like from your question and I used
some artistic licence to add a docNo Foreign Key to the Patient table. That
is of course that I understand what you are trying to achieve.
CREATE TABLE tblPatient (patientNo int, pntName varchar(10), docNo int)
CREATE TABLE tblDoctor (docNo int, docName varchar(10))
UPDATE tblPatient SET DocNo = D.docNo
FROM (SELECT docNo FROM tblDoctor WHERE docName = 'another value') D
WHERE pntName = 'a value'
Hope this offers some thought to your problem
Andy
"Assimalyst" wrote:

> Hi,
> I have two tables, one, called tblDoctor, with
> docNo, docName . . .
> the other, called tblPatient with
> pntNo, pntName . . .
> I want to insert a specific docNo value from one row in tblDoctor into
> a specific row in tblPatient using a stored procedure within ADO.
> Can i have an SQL statement along the lines of:
> INSERT INTO tblPatient (docNo)
> WHERE pntName = "a value"
> SELECT docNo
> FROM tblDoctor WHERE docName = "another value"
> If not, has anyone got any suggestions on alternative methods of
> accomplishing the same thing?
> Thanks
>|||Thank you for the replies, i've tried both suggestions, but having no
luck. The tblPatient.doctorNo remains blank.
Below is the relevant code and stored procedure, i'm getting no errors,
and the code runs through the debugger without a hitch.
string strSgyNo = System.Convert.ToString(intSgyNo);
string strDocFName = fNameTxtBx.Text;
string strDocLName = lNameTxtBx.Text;
string strPatientCode = patientCodeLbl.Text;
string strDOB = dobLbl.Text;
// Use stored procedure to input doctorNo as foreign key in tblPatient
SqlCommand cmd = new SqlCommand("proc_AddDocToPnt", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Add parameters to command, which will be passed to the stored
procedure
cmd.Parameters.Add(new SqlParameter("@.docFName", strDocFName));
cmd.Parameters.Add(new SqlParameter("@.docLName", strDocLName));
cmd.Parameters.Add(new SqlParameter("@.sgyNo", strSgyNo));
cmd.Parameters.Add(new SqlParameter("@.pntCode", strPatientCode));
cmd.Parameters.Add(new SqlParameter("@.pntDOB", strDOB));
Stored Procedure:
CREATE PROCEDURE proc_AddDocToPnt
(@.docFName varchar(20),
@.docLName varchar(30),
@.sgyNo int,
@.pntCode varchar(15),
@.pntDOB datetime
)
AS
UPDATE tblPatient
SET doctorNo = D.doctorNo
FROM(SELECT doctorNo
FROM tblReferringDoctorContacts
WHERE (docFName = @.docFName)
AND (docLName=@.docLName)
AND (sgyNo=@.sgyNo)) D
WHERE ((pntUnitID = @.pntCode) AND (pntDOB = @.pntDOB))
GO
Can anyone spot a problem?
Thanks again.|||Hi
Run these 2 queries. Both should return results using Query Analyser
DECLARE @.docFName varchar(20)
DECLARE @.docLName varchar(30)
DECLARE @.sgyNo int
DECLARE @.pntCode varchar(15)
DECLARE @.pntDOB datetime
--Set the values that would be passed using your code
SET @.docFName = ''
SET @.docLName ''
SET @.sgyNo = 0
SET @.pntCode ''
SET @.pntDOB '1980-01-01'
SELECT doctorNo
FROM tblReferringDoctorContacts
WHERE (docFName = @.docFName)
AND (docLName=@.docLName)
AND (sgyNo=@.sgyNo)
SELECT * FROM tblPatient WHERE ((pntUnitID = @.pntCode) AND (pntDOB =
pntDOB))
If you don't get any results from either query, then the query to update the
tblPatient will results in no changes being made because 1 or both of the
criterion isn't matched.
Using Query Analyser (included with MS SQL2K) or Visual Studio to test your
procedure / queries may offer more information from the output of your
queries better than running from your code.
Good Luck :)
Andy Furnival
"Assimalyst" wrote:

> Thank you for the replies, i've tried both suggestions, but having no
> luck. The tblPatient.doctorNo remains blank.
> Below is the relevant code and stored procedure, i'm getting no errors,
> and the code runs through the debugger without a hitch.
> string strSgyNo = System.Convert.ToString(intSgyNo);
> string strDocFName = fNameTxtBx.Text;
> string strDocLName = lNameTxtBx.Text;
> string strPatientCode = patientCodeLbl.Text;
> string strDOB = dobLbl.Text;
> // Use stored procedure to input doctorNo as foreign key in tblPatient
> SqlCommand cmd = new SqlCommand("proc_AddDocToPnt", conn);
> cmd.CommandType = CommandType.StoredProcedure;
> // Add parameters to command, which will be passed to the stored
> procedure
> cmd.Parameters.Add(new SqlParameter("@.docFName", strDocFName));
> cmd.Parameters.Add(new SqlParameter("@.docLName", strDocLName));
> cmd.Parameters.Add(new SqlParameter("@.sgyNo", strSgyNo));
> cmd.Parameters.Add(new SqlParameter("@.pntCode", strPatientCode));
> cmd.Parameters.Add(new SqlParameter("@.pntDOB", strDOB));
>
> Stored Procedure:
> CREATE PROCEDURE proc_AddDocToPnt
> (@.docFName varchar(20),
> @.docLName varchar(30),
> @.sgyNo int,
> @.pntCode varchar(15),
> @.pntDOB datetime
> )
> AS
> UPDATE tblPatient
> SET doctorNo = D.doctorNo
> FROM(SELECT doctorNo
> FROM tblReferringDoctorContacts
> WHERE (docFName = @.docFName)
> AND (docLName=@.docLName)
> AND (sgyNo=@.sgyNo)) D
> WHERE ((pntUnitID = @.pntCode) AND (pntDOB = @.pntDOB))
> GO
> Can anyone spot a problem?
> Thanks again.
>|||Thanks again,
I've run that, it seems it is finding the doctorNo from
blReferringDoctorContacts, but is not ablke to find the row from
tblPatient because the insetrts are not working properly.
If the number entered into the form is say 19/04/1945, the database
stores the date as 19/04/2001? Always changes the year to 2001!'
Very strange, but hence it cannot find the record. Think i'll need to
sort this before proceding with the above.
Thanks again|||OK solved that, i'd declared the pntDOB ad a varchar instead of a
datetime in the stored procedure oops!
Since tried again though, and i'm still not getting a row selected from
tblPatient.
i've set @.pntCode = 'CO123' and @.pntDOB = '10/03/1950' a row exists in
the tblPatient where pntUnitID = CO123 and pntDOB = 10/03/1950, but it
is not being found any ideas why?
Thanks again|||The problem seems to be with the date, although it is displayed in the
table as 10/03/1950 it is actually stored as 1950-03-10. if i change
@.pntDOB to 1950-03-10 it works. :)|||Dates in SQL can be ambigious. Therefore 10/03/1950 could be interpreted as
either 10-March-1950 or 3-October-1950.
When using datatimes in sql use the data format yyyy-mm-dd so set your
@.pndDOB = '1950-03-10'
To see how your dates are being interpreted run the code with your @.pndDOB
set.
CONVERT(SMALLDATETIME, @.pndDOB, 112) -- 112 is the yyyy-mm-dd format
Thanks
Andy
"Assimalyst" wrote:

> OK solved that, i'd declared the pntDOB ad a varchar instead of a
> datetime in the stored procedure oops!
> Since tried again though, and i'm still not getting a row selected from
> tblPatient.
> i've set @.pntCode = 'CO123' and @.pntDOB = '10/03/1950' a row exists in
> the tblPatient where pntUnitID = CO123 and pntDOB = 10/03/1950, but it
> is not being found any ideas why?
> Thanks again
>|||Thank you again.
Forgive my stupidity but i'm not sure how to implement what you're
suggesting.
Do you mean to change the query SET to
SET @.pntDOB = CONVERT(SMALLDATETIME, @.pntDOB, 112)
I've tried this, and get now matched rows. I've also tried
SET @.pntDOB = CONVERT(SMALLDATETIME, 10/03/1950, 112)
and again get no matches. the only way i've found so far is to manually
type
SET @.pntDOB = '1950-03-10' but i really need to implement this convert.
Can you elaborate?
Thankssqlsql

copy data from one table to another with addition insert value

Hi,

I was wondering if you can help.

In my vb.net form I am running a query to insert data from one database table to another.

However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command.

I have managed to use @.@. identity to get the id of my first sql insert statement

but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @.@.identity value to my second sql statement.

My second sql statement is a follows:

sql2 ="INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" &" select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId

I hope you can help

Cheers

Mark :)

If you need the ID of the first insert, for the second query then you cannot do a batch insert. There are ways to be a little creative such as doing the 2 inserts separately and doing a batch update on the second query. It depends on your data though.

|||

Thanks Dinakar,

Yes i need the id of the first insert for the second query.

As I need to alter one field value in the select data I am copying from do you think I will need to do a batch update after the second query. Is there no way of doing the insert,select and batch update all on the second query?

Many thanks

Mark :)

|||

markbpriv:

Hi,

I was wondering if you can help.

In my vb.net form I am running a query to insert data from one database table to another.

However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command.

I have managed to use @.@. identity to get the id of my first sql insert statement

but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @.@.identity value to my second sql statement.

My second sql statement is a follows:

sql2 ="INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" &" select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId

I hope you can help

Cheers

Mark :)

Do your first insert, then get the identity, then do your second insert.

Example:

declare myIdentityintINSERT INTO Table1 (col1, col2)VALUES ('test1','test2')SET @.myIdentity =@.@.IDENTITYINSERT INTO Table2SELECT myIdentity ,'test','another test'

Good luck.

|||

There is a way you can trick the SQL Server. Do you have any column in the first table that has any column which has a column like "userid" or "username" that you hardcode from your source data? or any column that has a fixed value across all rows?

|||

Hi,

Thanks for your help.

I keep getting the following error when trying to do @.@.identity

Could not find stored procedure 'False'.

Any idea why?

Cheers

Mark :)

|||

markbpriv:

Hi,

Thanks for your help.

I keep getting the following error when trying to do @.@.identity

Could not find stored procedure 'False'.

Any idea why?

Cheers

Mark :)

Please chekout the seconf post here:http://www.aspspider.com/rss/Rss19894.aspx

If not help:
Make sure the stored procedure is exists in your database.
Execute the SP with the owner name: MyName.MyStoredProcedureName

I guess, you are concatenating a value to a SELECT statment maybe and that value is getting you False.

I found this link which support my guess:http://p2p.wrox.com/topic.asp?TOPIC_ID=1773

Please let me know if this help you or not.

Good luck.

Copy data from one table to another table with change in identity column values

Roy Harvey (roy_harvey@.snet.net) writes:

Quote:

Originally Posted by

If I understand your question, you want to insert the data from table
test into an already existing table.
>
INSERT existingtable
SELECT a, b
FROM test

I guess it should be:

INSERT existingtable
SELECT 10 + a, b
FROM test

since Salish wanted to change the values.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxOn Mon, 20 Aug 2007 21:54:50 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>since Salish wanted to change the values.

Thanks for catching that, Erland.

Roy

Copy and Past a row

Hello all,

Is there a way for me to copy a row, do some modification in a certain row and insert it to the same table as a new row?

Forward with thousand thanks.

Best regards,

Tee Song Yann

Where, in code in an application ? YOu will have to be a bit more specific.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hello Jens,

I'm trap in a pure SQL query's world. Imagine you are performing the above task in SQL Server Management Studio.

Best regards,

Tee Song Yann

|||Then you can just open a table with "Open Table" and select a row by clicking on the left side of the row (Selecting the whole column), STRG+C , go to the last row, which indicates a new row, STRG+V. Unless you leave the row, you will be able to change the values within, if you want to discard the insert or changes you can press ESC.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||OR <ctrl>C and <ctrl>V|||

Sorry I think I guide you people to the wrong place.

Actually it should be "Imagine you are performing the above task in SQL Server Management Studio SQL Query".

Can I perform that with SQL statement only?

Thanks.

|||

OK, yes this is sure possible:

INSERT INTO SomeTable2
(ColumnListHereto Insert)
Select YourColumnsHere
From SomeTable
Where ConditionsTofilterYourRow

Instead of YourColumnsHere you can use any manipulation on the data you want like

REPLACE(SomeColumn, 'a','b'), LEFT(SomeOtherColum,3)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hello Jens,

Thanks for your help. Then I better post more question here. Wahaha.

Thanks again and best regards.

Tee Song Yann

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK = 13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK => 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
--
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
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JY
Jon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegr oups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>
|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
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
|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

Copy a row in SQL SERVER 2000

Hello all,
I'd like to INSERT a new row based upon a SELECT of an existing row,
I've tried the below but there's a syntax error:
INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
13185)
Also, would this work as it will try to INSERT a field with a
duplicate PK. Any suggesting on how this could be best achived?
Thanks,
JYJon
You almost got it
INSERT INTO TableA SELECT * FROM TableA WHERE TableA.PK = 13185

> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
No it would not. Add an IDENTITY property as a surrogate key
"Jon" <JonMYates@.gmail.com> wrote in message
news:1178702349.126759.189880@.l77g2000hsb.googlegroups.com...
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
>|||On 9 May, 10:19, Jon <JonMYa...@.gmail.com> wrote:
> Hello all,
> I'd like to INSERT a new row based upon a SELECT of an existing row,
> I've tried the below but there's a syntax error:
> INSERT INTO TableA VALUES (SELECT * FROM TableA WHERE TableA.PK =
> 13185)
> Also, would this work as it will try to INSERT a field with a
> duplicate PK. Any suggesting on how this could be best achived?
> Thanks,
> JY
Just leave out VALUES and the brackets:
INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3 FROM ...
It makes no sense to duplicate an entire row. You need to supply new
values for at least the key column(s) of the table in question.
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
--|||INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)