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 one coulm to another

Hi Everyone.
I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is unique.
I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I do
this?
Thanks for your help.If you want to update all values in the LOGIN column, then it should be as
simple as this:
UPDATE MyTable
SET LOGIN = LICENSE_NO
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||If I understand what you are trying to do...
update t1
set LOGIN=LICENSE_NO
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.|||"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.
UPDATE FOO set LOGIN=LICENSE_NO
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks very much. Works great.
"Plamen Ratchev" wrote:
> If you want to update all values in the LOGIN column, then it should be as
> simple as this:
> UPDATE MyTable
> SET LOGIN = LICENSE_NO
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

Copy one coulm to another

Hi Everyone.
I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is unique.
I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I do
this?
Thanks for your help.
If you want to update all values in the LOGIN column, then it should be as
simple as this:
UPDATE MyTable
SET LOGIN = LICENSE_NO
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||If I understand what you are trying to do...
update t1
set LOGIN=LICENSE_NO
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.
|||"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.
UPDATE FOO set LOGIN=LICENSE_NO
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks very much. Works great.
"Plamen Ratchev" wrote:

> If you want to update all values in the LOGIN column, then it should be as
> simple as this:
> UPDATE MyTable
> SET LOGIN = LICENSE_NO
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
sqlsql

Copy one column to another

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

Quote:

Originally Posted by NamelessNumberheadMan

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

try:
update Mapping Set SID = SMappingID
where SMappingID IS NOT NULL|||

Quote:

Originally Posted by NamelessNumberheadMan

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

The query is simple:

UPDATE TABLENAME
SET NEWCOLUMN = SOURCECOLUMN WHERE SOURCECOLUMN IS NOT NULL.

Thanks.

copy on condition

i have two tables A and B with the same fields,

If the id field of table B equals id field in Table A i need to update th edata for that id row.
If the id field doesn;t match then i need to insert a new record in tale A for that id

that is i need to perform insertion or updation into table A depending on table B data
Can anyone give me some idea how to start?

You need two statements.
UPDATE A SET col1 = b.col1, col2 = b.col2, ... FROM A INNER JOIN B on A.pkcol = B.pkcol
INERT INTO A (pkcol, col1, col2, col3)
SELECT pkcol, col1, col2, col3
FROM B
WHERE pkcol NOT IN (SELECT pkcol FROM A)|||

Thank you very much Alex!
One more question, I have millions of data in the table, will the 2 queries work well for such huge data
.I mean performance wise. I have created indexes .

|||You'll just have to try it to find out. No way of guessing that without knowing what the tables are, how the indexes are, hardware, load, etc ...

copy of table from another database into your database

We copy a table from a progress database into our sql server database that we use as a read-only table. We perform this action through a job.

Is there a way to have a read only table from another database that is put into your database that will always be insinc with the original copy to use as a read only copy, that as the original table is updated, it will proprogate to your copied table?

Would setting up a package to import the data from the other database on a schedule work? In SQL Server, you can do an import and you have the option to save the import as a package and schedule it to run at your discretion.

|||

If you require the tables be kept in sync, then the easiest way would be to use a link from the source to the destination server, and have triggers on the source populate the destination. This does carry a performance penalty, of course.

A more performant solution would be to have the triggers on the source write their updates to an update table on the same database, and poll it for changes every 5 minutes or so, but you're not guaranteed to have the DBs in sync then.

copy of existing table and data

Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>
Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:

> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need to
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
>

copy of existing table and data

My query is very simple, I am new to SQL.
I want to create copy of existing table and data.
Pls suggest a command !
Thanks in advance
SanjayEverything in the database? If so, I suggest backup and restore. If not, che
ck out some of the tools
at http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.
gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Thanks for the prompt response.
I think, i have failed to pass on my message.
I want to create a new table using existing table with its structure and
records.
Sanjay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYKfdypeHHA.4300@.TK2MSFTNGP02.phx.gbl...
> Everything in the database? If so, I suggest backup and restore. If not,
> check out some of the tools at
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>|||Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:

> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need t
o
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
>

copy of existing table and data

My query is very simple, I am new to SQL.
I want to create copy of existing table and data.
Pls suggest a command !
Thanks in advance
SanjayEverything in the database? If so, I suggest backup and restore. If not, check out some of the tools
at http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Thanks for the prompt response.
I think, i have failed to pass on my message.
I want to create a new table using existing table with its structure and
records.
Sanjay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYKfdypeHHA.4300@.TK2MSFTNGP02.phx.gbl...
> Everything in the database? If so, I suggest backup and restore. If not,
> check out some of the tools at
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>> My query is very simple, I am new to SQL.
>> I want to create copy of existing table and data.
>> Pls suggest a command !
>> Thanks in advance
>> Sanjay
>|||Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:
> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need to
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> > My query is very simple, I am new to SQL.
> >
> > I want to create copy of existing table and data.
> > Pls suggest a command !
> >
> > Thanks in advance
> > Sanjay
> >
>
>

Copy just new rows

Hi,
I need to copy just the new rows from a Oracle DB to a MS-SQL table. I
created a DTS and I can get the information with no problems the issues is
that I only want to insert the new rows or records created.
Any tipsHow do you expect to define "new rows"?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"julio delgado" <jdelgado89@.hotmail.com> wrote in message
news:u5z3eMiLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need to copy just the new rows from a Oracle DB to a MS-SQL table. I
> created a DTS and I can get the information with no problems the issues is
> that I only want to insert the new rows or records created.
> Any tips
>|||If you're talking about rows with keys that do not exist in the target, you
can use a linked server as follows:
INSERT INTO Target(column_list)
SELECT <column_list> FROM Source AS S
WHERE NOT EXISTS
(SELECT * FROM Target AS T
WHERE T.key = S.key)
Just specify instead of source or target (depending on where you're
connected to) the full four part name (server.db.owner.object).
BG, SQL Server MVP
www.SolidQualityLearning.com
"julio delgado" <jdelgado89@.hotmail.com> wrote in message
news:u5z3eMiLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need to copy just the new rows from a Oracle DB to a MS-SQL table. I
> created a DTS and I can get the information with no problems the issues is
> that I only want to insert the new rows or records created.
> Any tips
>|||Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:O%23M548iLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> If you're talking about rows with keys that do not exist in the target,
you
> can use a linked server as follows:
> INSERT INTO Target(column_list)
> SELECT <column_list> FROM Source AS S
> WHERE NOT EXISTS
> (SELECT * FROM Target AS T
> WHERE T.key = S.key)
> Just specify instead of source or target (depending on where you're
> connected to) the full four part name (server.db.owner.object).
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "julio delgado" <jdelgado89@.hotmail.com> wrote in message
> news:u5z3eMiLFHA.2120@.TK2MSFTNGP10.phx.gbl...
is
>sqlsql

Copy info from one table two another with conditions

I am importing a csv file into a temp table (works fine), now I want to take the data in the temp table based off of the temp id compared to the id of the permanant file.

basically if the record id exists skip to the next line and so forth, I am able to import everything just fine but I cannot get the WHERE condition to acually work.

INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 WHERE table2.col1 <> table1.col1

this is what my actual code is

INSERT INTO USER_INFO (fpu_id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE stmailbox.id <> dbo.USER_INFO.USERID

this is the actual error that I get.

Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'USER_INFO' does not match with a table name or alias name used in the query.

--
thanks for the helpWell I think that I got it...

INSERT INTO USER_INFO (id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE stmailbox.id NOT IN (SELECT id FROM USER_INFO)

Let me know if there is a better way

Thanks

Copy Image data type from one table to another

Hi,
I've a column col1 of image data type in table1. I would like to copy the data from col1 to another image column col2 in table2. Before moving the value, checking has to be done to specify which col1 data from table1 is needed and the destination has to be checked too.

Example: insert into col2
(select col1 from table1 where table1_id =5)
where table2_id =6

Hence bcp wouldn't work. Can anyone suggest me a way to do it. I tried using writetext but then, i've to get data from col1 in a variable, which is not possible. Any suggestions would be very helpful.

Thanks in advance.
Ramya.I found the way to do it. Here is the code:

declare @.ptrvalsource binary(16)

DECLARE @.ptrvaldest binary(16)

select @.ptrvalsource =textptr(col1) from table1 where table1_id = 5

SELECT @.ptrvaldest = TEXTPTR(col2) FROM table2 where table2_id=6

updateTEXT table2.col2 @.ptrvaldest 0 null table1.col1 @.ptrvalsource

Thanks,
Ramya.

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 from one table to the other using stored procedure

Hi there,
How to the scripts for copying data in table A to table B using stored procedure?
ThanxINSERT INTO Table_A
SELECT * FROM Table_B|||Hi there,

I got this one prob.............i have to copy from data which consist in the VIEWS section in SQL Server Enterprise Manager.
This views section already contain the script which limit the user to few databases and i need to copy watever display in the view section to a new table in other database.
Thanx|||Hi there...

Maybe this will help:

Select * into Other_db..newTable from Source_db..View

Other_db = the database where the new table should be created
newTable = the name of the new table
Source_db = is the database where you query the view
View = the name of the View

I hope this helps.

Greetz,
Marc,
:)|||thanx

copy fields of one table to the field structure of another table

How can I copy fields (not records) from one table to the field structure of
another table (in the same database on a sql server 2000)? So that I have
not to re-type the name and properties of the fields and to be sure the name
and properties of the fields are the same in both tables.
Thanks,
HugoScript Table definition and create new table with different name.
In Query Analizer right click on table -> Script Object to New Window As ->
Create. Type new name for table and run script.
"Educo Gent" wrote:

> How can I copy fields (not records) from one table to the field structure
of
> another table (in the same database on a sql server 2000)? So that I have
> not to re-type the name and properties of the fields and to be sure the na
me
> and properties of the fields are the same in both tables.
> Thanks,
> Hugo
>
>|||SELECT * FROM SourceTable
Into DestinationTable WHERE 1=0
Remeber that it will not copy the keys and indexes
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Educo Gent" <educo.gent@.skynet.be> wrote in message
news:ndHOd.8101$Ll6.561275@.phobos.telenet-ops.be...
> How can I copy fields (not records) from one table to the field structure
> of another table (in the same database on a sql server 2000)? So that I
> have not to re-type the name and properties of the fields and to be sure
> the name and properties of the fields are the same in both tables.
> Thanks,
> Hugo
>|||Hi Roji
The syntax is wrong
> SELECT * FROM SourceTable
> Into DestinationTable WHERE 1=0
SELECT * INTO Dest FROM Source WHERE 1=0
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:O0bvQS2DFHA.1292@.TK2MSFTNGP10.phx.gbl...
> SELECT * FROM SourceTable
> Into DestinationTable WHERE 1=0
> Remeber that it will not copy the keys and indexes
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Educo Gent" <educo.gent@.skynet.be> wrote in message
> news:ndHOd.8101$Ll6.561275@.phobos.telenet-ops.be...
structure
>|||You are right Uri. Thanks
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23QWD0W2DFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi Roji
> The syntax is wrong
>
> SELECT * INTO Dest FROM Source WHERE 1=0
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:O0bvQS2DFHA.1292@.TK2MSFTNGP10.phx.gbl...
> structure
>

copy field from one table to another

I am trying to copy the values of one table to another one:
Select ProdNum, Name,NYUSAPrice,MPN
into ProductsQuartz [ProdNum,Name,Price,MPN]
from productswithprice
Both table already exist, trying to map the fields but I get an error '
AAlso tried:
Insert into
ProductsQuartz ('ProdNum','Name','Price','MPN')
values ('productswithprice.ProdNum',
'productswithprice.Name','productswithprice.NYUSAPrice','productswithprice.M
PN')
But I get an error, this are fields from another table in the database, how
can I accoplish this ?
A
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:e4Lgt10BFHA.1392@.tk2msftngp13.phx.gbl...
>I am trying to copy the values of one table to another one:
> Select ProdNum, Name,NYUSAPrice,MPN
> into ProductsQuartz [ProdNum,Name,Price,MPN]
> from productswithprice
> Both table already exist, trying to map the fields but I get an error '
> A
>|||Hi
Insert into
ProductsQuartz ('ProdNum','Name','Price','MPN')
Select productswithprice.ProdNum,
productswithprice.Name,productswithprice.NYUSAPrice,productswithprice.MPN
from Productswithprice
Hth
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:O8mV950BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Also tried:
> Insert into
> ProductsQuartz ('ProdNum','Name','Price','MPN')
> values ('productswithprice.ProdNum',
>
'productswithprice.Name','productswithprice.NYUSAPrice','productswithprice.M
PN')
> But I get an error, this are fields from another table in the database,
how
> can I accoplish this ?
> A
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:e4Lgt10BFHA.1392@.tk2msftngp13.phx.gbl...
>|||thx
"AM" <shahdharti@.gmail.com> wrote in message
news:uwvPX$0BFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> Insert into
> ProductsQuartz ('ProdNum','Name','Price','MPN')
> Select productswithprice.ProdNum,
> productswithprice.Name,productswithprice.NYUSAPrice,productswithprice.MPN
> from Productswithprice
> Hth
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:O8mV950BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> 'productswithprice.Name','productswithprice.NYUSAPrice','productswithprice
.M
> PN')
> how
>

Copy field data from table to another

Let's say, I have Table1 and Table2, both with the exactly same
structure and data in it, except that Table1.Field1 is empty in data
but Table2.Field1 have data in it.

How could I copy all the data from Table2.Field1 to Table1.Field1?
Is there any simpler way except looping through all the records to do
it?Sorry, just some add on to my previous question.
If I have this:

Table1
RecId ==> record ID
Name ==> name (empty)
Desc ==> description
... (another 23 fields to go)

Table2
RecId ==> recordID
Name ==> name (with data)
Desc ==> description
... (another 29 fields to go)

The Table1.Name is empty in data, then Table2.Name have data.
How could I write a SQL statement to copy all the data from Table2.Name
to Table1.Name since the table structure is different. I need to copy
the data in by mathing the RecordId.

I mean, copy Table2.Name to Table1.Name where their recordID is the
same.
Just as an extra info, there're 500,000 records in the tables.

Thanks a lot in advance.

Peter CCH|||Hi

Assuming recordid is a key that you match the records on. If your fields
have no value I assume they are NULL.

UPDATE T
SET Name = S.NAME,
Desc = S.Desc,
...

FROM Table 1 T
JOIN Table2 S ON s.recordid = t.recordid
WHERE Name IS NULL
OR DESC IS NULL

If you dont want to update table1 if it has a value try something like.

UPDATE T
SET Name = CASE WHEN NAME IS NULL THEN S.NAME ELSE NAME END,
Desc = CASE WHEN NAME IS NULL THEN S.DESC ELSE DESC END,,
...

FROM Table 1 T
JOIN Table2 S ON s.recordid = t.recordid
WHERE Name IS NULL
OR DESC IS NULL

John

"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1112424964.224162.251730@.g14g2000cwa.googlegr oups.com...
> Sorry, just some add on to my previous question.
> If I have this:
> Table1
> RecId ==> record ID
> Name ==> name (empty)
> Desc ==> description
> ... (another 23 fields to go)
> Table2
> RecId ==> recordID
> Name ==> name (with data)
> Desc ==> description
> ... (another 29 fields to go)
> The Table1.Name is empty in data, then Table2.Name have data.
> How could I write a SQL statement to copy all the data from Table2.Name
> to Table1.Name since the table structure is different. I need to copy
> the data in by mathing the RecordId.
> I mean, copy Table2.Name to Table1.Name where their recordID is the
> same.
> Just as an extra info, there're 500,000 records in the tables.
>
> Thanks a lot in advance.
>
>
> Peter CCH|||Since it join both of the tables first then only update the value, if
the table have 500,000 records, will the SQL statement above takes very
long time? (Assume it update that 500,000 records)|||Hi

5000000 rows would not take that long, but as the where condition limits the
rows you may see less being updated. You may also want to check that you are
not updating with the same values.

UPDATE T
SET Name = S.NAME,
[Desc] = S.[Desc],
...

FROM Table 1 T
JOIN Table2 S ON s.recordid = t.recordid
WHERE ( T.Name IS NULL AND T.Name <> S.Name )
OR ( T.[DESC] IS NULL AND T.[DESC] <> S.[DESC])

John

"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1112454364.911855.240540@.o13g2000cwo.googlegr oups.com...
> Since it join both of the tables first then only update the value, if
> the table have 500,000 records, will the SQL statement above takes very
> long time? (Assume it update that 500,000 records)|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> Since it join both of the tables first then only update the value, if
> the table have 500,000 records, will the SQL statement above takes very
> long time? (Assume it update that 500,000 records)

Updating 500000 rows is usually not snap. Exactly how long time depends
on your hardware, but also the definition of the column. If the column
is fixed length, all updates can be in place. But if the column is a
varchar or varbinary column, many rows will grow out of their current
pages, so that must be a lot of page splits and data moved around. Again,
exactly how massive this effect will be depends on the data. If the
average length is three characters, you have have luck and most pages
have space to spare. If the average length is 100 bytes, this is less
likely.

Another thing that matters is whether data and log files have the space.
Updating half a million rows will take a toll on the log file. Exactly
how big that toll is, depends on the width of the table. If each row
is 10 bytes in averages you need a lot less log if the average row length
is 360 bytes, If the log does not have the space, it will have to
autogrow and autogrow does not come for free. If you are running with
simple recovery, you can hold down the log explosion by doing the
update in batches of 50000 or so.

But in short, the only way to get answer to a question like this one is
to benchmark.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsqlsql

copy db table from one db to another

how do I copy a table, structure only from one database to another? I select copy in the table view of one database, but then paste is not available to me in table view of the other database. I am transitioning two db tables from a development web server to the production server, in order to go live... I don't want to have to type all of the design in the new table dialog!

Please help quickly!From Enterprise Manager if you left-click the tablename and under the menu option All Tasks you will see anoth menu option called Emport Data... . This will allow you to export the data and the table structure (if the destination is SQL Server).|||the quickest way to do it is to go into enterprise manager, and click (single click) on the table that must have its structure copied. press Ctrl-C (ie copy), open query analyser, select the database in which the new table must be created and press Ctrl-V (ie paste). The sql code for creating the table should now appear.

This works in SQL Server 7. I have not tried it in 2000.|||For Generating SQL Script:
1. You can select whatever tables you want in Enterprise manager
2. Right click and then in "All Tasks" select "Generate SQL Script"

This is the correct way of generating SQL Script for any objects.

Have fun.

Varad01

Copy Date Dimension

Hello Guys and Galls,

I have a fact table with two different keys referring to the same date dimension table. In a query you would use table aliassing to join the date dimension twice.

In AS I created a DSV with two aliasses of the date dimension table. Lets give um names:
- dim_ActivityDate
- dim_OrderDate

Now, I have created a dimension "Activity Date", based on the dim_ActivityDate dimension. I want to create a second dimension, "Order Date", which is a copy of the "Activity Date" dimension, but based on the dim_OrderDate alias from the DSV.

Is there an easy way to do this, or do I have to create the whole dimension using the wizard again?

Regards, Jeroen

Hi Jeroen,

No, you don't need to create two separate physical dimensions. You just need to create one dimension and add it to the cube more than once - a 'role playing dimension'. In Visual Studio just double click on your cube to edit it and assuming you have the dimension already added to the cube once, you just need to right-click on Dimensions box in the bottom-right-hand corner of the 'Cube Structure' tab and select 'Add Cube Dimension'. Add the date dimension again and you'll find that it gets added with a new name, which you can change to Order Date, and then if you go to the Dimension Usage tab you'll find you can join it to your measure group on the dim_OrderDate key column.

HTH,

Chris

|||Splendid! Thnx Chris.

Copy database roles between databases sql server 2005

Hi There,
This is vexing to say the least.
Setup:
Single SQL 2005 standard server: 2 databases, with the same table
structure and query structure.
Table Count: 78
Query Count: 3
Database A has 8 customized security roles, with different permissions
across all of the tables.
Database B has the same table structure, but different data, and none
of the roles defined in A.
How do I copy the roles from Database A to Database B?
To do it by hand would be error prone, and not much fun...
Someone posted the following in another forum, but I can't get it to
work, says that there is an invalid connection:
- Execute batch in the old DB
-- Execute result in new DB
-- Script permissions on all tables
-- Author: Th. Fuchs, IMC GmbH Chemnitz
declare @.object int, @.hresult int, @.property varchar(255), @.return
varchar(8000)
declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
declare @.ScriptType integer, @.tabname varchar(200), @.dbname
varchar(128), @.pwd varchar(20)
declare @.tablelist table (tabid integer, tabname varchar(128))
set @.dbname = 'INVEKOS2' -- define db to script
set @.pwd = '' -- top secret!
-- Create the sqlserver-object
execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
if @.hresult = 0 -- connect to server
execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
'SMUL-DB-121', 'sa', @.pwd
-- Get all tablenames
insert into @.tablelist(tabid, tabname)
select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
object_name(id)
from dbo.sysobjects
where objectproperty(id, 'IsTable') = 1
and objectproperty(id, 'IsSystemTable') = 0
-- step through tables, script descriped in
http://msdn.microsoft.com/library/de...f_m_s_5e2a.asp
select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
select tabname from @.tablelist order by tabid
open cur_tab
fetch next from cur_tab into @.tabname
while @.@.fetch_status = 0 and @.hresult = 0
begin
select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
'").script'
execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
@.ScriptType
print @.return
fetch next from cur_tab into @.tabname
end
close cur_tab
deallocate cur_tab
-- Destroy the object.
if @.hresult = 0 -- disconnect and freemem
execute @.hresult = sp_OADestroy @.object
-- If Error occurs, get a tip
if @.hresult != 0
begin
execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
select hresult = convert(varbinary(4),@.hresult), Source = @.src,
Description = @.desc
end
Thanks for your time.
Perhaps one of these articles will give you the information you desire.
http://www.sqlservercentral.com/scri...tions/1598.asp Script Roles
and Permissions
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<manganb@.gmail.com> wrote in message
news:1159562833.316536.262150@.m73g2000cwd.googlegr oups.com...
> Hi There,
> This is vexing to say the least.
> Setup:
> Single SQL 2005 standard server: 2 databases, with the same table
> structure and query structure.
> Table Count: 78
> Query Count: 3
>
> Database A has 8 customized security roles, with different permissions
> across all of the tables.
> Database B has the same table structure, but different data, and none
> of the roles defined in A.
> How do I copy the roles from Database A to Database B?
> To do it by hand would be error prone, and not much fun...
> Someone posted the following in another forum, but I can't get it to
> work, says that there is an invalid connection:
> - Execute batch in the old DB
> -- Execute result in new DB
> -- Script permissions on all tables
> -- Author: Th. Fuchs, IMC GmbH Chemnitz
> declare @.object int, @.hresult int, @.property varchar(255), @.return
> varchar(8000)
> declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
> declare @.ScriptType integer, @.tabname varchar(200), @.dbname
> varchar(128), @.pwd varchar(20)
> declare @.tablelist table (tabid integer, tabname varchar(128))
> set @.dbname = 'INVEKOS2' -- define db to script
> set @.pwd = '' -- top secret!
> -- Create the sqlserver-object
> execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
> if @.hresult = 0 -- connect to server
> execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
> 'SMUL-DB-121', 'sa', @.pwd
> -- Get all tablenames
> insert into @.tablelist(tabid, tabname)
> select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
> object_name(id)
> from dbo.sysobjects
> where objectproperty(id, 'IsTable') = 1
> and objectproperty(id, 'IsSystemTable') = 0
> -- step through tables, script descriped in
> --
> http://msdn.microsoft.com/library/de...f_m_s_5e2a.asp
> select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
> declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
> select tabname from @.tablelist order by tabid
> open cur_tab
> fetch next from cur_tab into @.tabname
> while @.@.fetch_status = 0 and @.hresult = 0
> begin
> select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
> '").script'
> execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
> @.ScriptType
> print @.return
> fetch next from cur_tab into @.tabname
> end
> close cur_tab
> deallocate cur_tab
> -- Destroy the object.
> if @.hresult = 0 -- disconnect and freemem
> execute @.hresult = sp_OADestroy @.object
> -- If Error occurs, get a tip
> if @.hresult != 0
> begin
> execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> select hresult = convert(varbinary(4),@.hresult), Source = @.src,
> Description = @.desc
> end
> Thanks for your time.
>