I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all
its indexes and data in the same database as table A_copy.
2) I know how to figure out how many rows a table has, how do you figure
out how many columns?
3) Where does it say how large in terms of disk space (data) a table is?
4) Can you determine how large in terms of disk space only selected
columns within a table are?
[ Sugapablo ]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--music ]
[ http://www.2ra.org <--political | http://www.subuse.net <--discuss ]
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
SELECT * INTO A_copy
FROM A
Note: PK and FK and any indexed columns will migrate their data, but the
PK, FK and indexes themselves will not be recreated. You will have to do
that yourself.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
Take a look at the INFORMATION_SCHEMA views.
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<tablename>'
> 3) Where does it say how large in terms of disk space (data) a table is?
You can start with sp_spaceused 'objname'
EXEC sp_spaceused 'SomeTable'
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
Not easily. You would need to get the width of the column (average width
for variable length columns) and multiply that by the number of rows in the
table. There are other factors to include, however, this should get you
reasonably close.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi,
I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all its
indexes and data in the same database as table A_copy.
Generate Script with dependant objects using enterprise manager, execute
that in destination
database and use DTS to transfer the data.
2) I know how to figure out how many rows a table has, how do you figure out
how many columns?
select count(*) from syscolumns where object_name(id)='table_name'
or
Query the same on INFORMATION_SCHEMA.COLUMNS VIEW.
3) Where does it say how large in terms of disk space (data) a table is?
sp_spaceused <table_name>
4) Can you determine how large in terms of disk space only selected columns
within a table are?
You have to manually calculate based on usage and alloctions for each field.
Thanks
Hari
SQL Server MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.23.16.12.37.486877@.REMOVEsugapabl o.com...
> I'm trying to figure out how to do a few things in MS SQL Server but can't
> Google up what I need or find it in the docs that I have.
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
> 3) Where does it say how large in terms of disk space (data) a table is?
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>
Showing posts with label selected. Show all posts
Showing posts with label selected. Show all posts
Sunday, March 25, 2012
Copying tables, finding number of columns in a table, & size of data in selected c
I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all
its indexes and data in the same database as table A_copy.
2) I know how to figure out how many rows a table has, how do you figure
out how many columns?
3) Where does it say how large in terms of disk space (data) a table is?
4) Can you determine how large in terms of disk space only selected
columns within a table are?
[ Sugapablo
]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--mu
sic ]
[ http://www.2ra.org <--political | http://www.subuse.net <--di
scuss ]> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
SELECT * INTO A_copy
FROM A
Note: PK and FK and any indexed columns will migrate their data, but the
PK, FK and indexes themselves will not be recreated. You will have to do
that yourself.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
Take a look at the INFORMATION_SCHEMA views.
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<tablename>'
> 3) Where does it say how large in terms of disk space (data) a table is?
You can start with sp_spaceused 'objname'
EXEC sp_spaceused 'SomeTable'
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
Not easily. You would need to get the width of the column (average width
for variable length columns) and multiply that by the number of rows in the
table. There are other factors to include, however, this should get you
reasonably close.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi,
I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all its
indexes and data in the same database as table A_copy.
Generate Script with dependant objects using enterprise manager, execute
that in destination
database and use DTS to transfer the data.
2) I know how to figure out how many rows a table has, how do you figure out
how many columns?
select count(*) from syscolumns where object_name(id)='table_name'
or
Query the same on INFORMATION_SCHEMA.COLUMNS VIEW.
3) Where does it say how large in terms of disk space (data) a table is?
sp_spaceused <table_name>
4) Can you determine how large in terms of disk space only selected columns
within a table are?
You have to manually calculate based on usage and alloctions for each field.
Thanks
Hari
SQL Server MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.23.16.12.37.486877@.REMOVEsugapablo.com...
> I'm trying to figure out how to do a few things in MS SQL Server but can't
> Google up what I need or find it in the docs that I have.
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
> 3) Where does it say how large in terms of disk space (data) a table is?
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all
its indexes and data in the same database as table A_copy.
2) I know how to figure out how many rows a table has, how do you figure
out how many columns?
3) Where does it say how large in terms of disk space (data) a table is?
4) Can you determine how large in terms of disk space only selected
columns within a table are?
[ Sugapablo
]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--mu
sic ]
[ http://www.2ra.org <--political | http://www.subuse.net <--di
scuss ]> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
SELECT * INTO A_copy
FROM A
Note: PK and FK and any indexed columns will migrate their data, but the
PK, FK and indexes themselves will not be recreated. You will have to do
that yourself.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
Take a look at the INFORMATION_SCHEMA views.
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<tablename>'
> 3) Where does it say how large in terms of disk space (data) a table is?
You can start with sp_spaceused 'objname'
EXEC sp_spaceused 'SomeTable'
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
Not easily. You would need to get the width of the column (average width
for variable length columns) and multiply that by the number of rows in the
table. There are other factors to include, however, this should get you
reasonably close.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi,
I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all its
indexes and data in the same database as table A_copy.
Generate Script with dependant objects using enterprise manager, execute
that in destination
database and use DTS to transfer the data.
2) I know how to figure out how many rows a table has, how do you figure out
how many columns?
select count(*) from syscolumns where object_name(id)='table_name'
or
Query the same on INFORMATION_SCHEMA.COLUMNS VIEW.
3) Where does it say how large in terms of disk space (data) a table is?
sp_spaceused <table_name>
4) Can you determine how large in terms of disk space only selected columns
within a table are?
You have to manually calculate based on usage and alloctions for each field.
Thanks
Hari
SQL Server MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.23.16.12.37.486877@.REMOVEsugapablo.com...
> I'm trying to figure out how to do a few things in MS SQL Server but can't
> Google up what I need or find it in the docs that I have.
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
> 3) Where does it say how large in terms of disk space (data) a table is?
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>
Monday, March 19, 2012
Copying DB in SQL 2005
I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
Object method to copy one DB onto a new one on the same server, it looks like
it worked but the Identity Seed and Identity increment properties are missing
on some of the columns on the new DB. Has anyone else experienced this
behavior?
Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restore
to copy DBs. My preference is to use Transfer Object to transfer only tables
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:
> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks like
> it worked but the Identity Seed and Identity increment properties are missing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?
Object method to copy one DB onto a new one on the same server, it looks like
it worked but the Identity Seed and Identity increment properties are missing
on some of the columns on the new DB. Has anyone else experienced this
behavior?
Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restore
to copy DBs. My preference is to use Transfer Object to transfer only tables
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:
> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks like
> it worked but the Identity Seed and Identity increment properties are missing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?
Copying DB in SQL 2005
I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
Object method to copy one DB onto a new one on the same server, it looks like
it worked but the Identity Seed and Identity increment properties are missing
on some of the columns on the new DB. Has anyone else experienced this
behavior?Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restore
to copy DBs. My preference is to use Transfer Object to transfer only tables
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:
> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks like
> it worked but the Identity Seed and Identity increment properties are missing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?
Object method to copy one DB onto a new one on the same server, it looks like
it worked but the Identity Seed and Identity increment properties are missing
on some of the columns on the new DB. Has anyone else experienced this
behavior?Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restore
to copy DBs. My preference is to use Transfer Object to transfer only tables
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:
> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks like
> it worked but the Identity Seed and Identity increment properties are missing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?
Copying DB in SQL 2005
I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
Object method to copy one DB onto a new one on the same server, it looks lik
e
it worked but the Identity Seed and Identity increment properties are missin
g
on some of the columns on the new DB. Has anyone else experienced this
behavior?Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restor
e
to copy DBs. My preference is to use Transfer Object to transfer only table
s
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:
> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks l
ike
> it worked but the Identity Seed and Identity increment properties are miss
ing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?
Object method to copy one DB onto a new one on the same server, it looks lik
e
it worked but the Identity Seed and Identity increment properties are missin
g
on some of the columns on the new DB. Has anyone else experienced this
behavior?Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restor
e
to copy DBs. My preference is to use Transfer Object to transfer only table
s
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:
> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks l
ike
> it worked but the Identity Seed and Identity increment properties are miss
ing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?
Wednesday, March 7, 2012
copying a table
Hi,o
I found an option to copy a table without having to script the table, now I can't find it. Is there an option to do this? When I selected the option, it didn't work. Any ideas? Also how do you turn off trace and statistics?
thx,
Kat
select * into newtable from old table
dbcc traceoff
Friday, February 24, 2012
Copy/Paste behavior in SSMS
I realize that this seems odd, but I want to change the copy paste
behavior in SSMS.
Right now if you have no text selected in SSMS and do a control-c the
entire line is copied, but I want the copy to be null. Basicly I'm
automating some repetive functions and need to move only the selected
text to the clipboard. If no text is selected then want nothing.
Any ideas?
Thanks
William
wgbrown_nospam_@.gmail.com
How are you automating the tasks?
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
<wgbrown@.gmail.com> wrote in message
news:1164146398.788232.175850@.k70g2000cwa.googlegr oups.com...
>I realize that this seems odd, but I want to change the copy paste
> behavior in SSMS.
> Right now if you have no text selected in SSMS and do a control-c the
> entire line is copied, but I want the copy to be null. Basicly I'm
> automating some repetive functions and need to move only the selected
> text to the clipboard. If no text is selected then want nothing.
> Any ideas?
> Thanks
> William
> wgbrown_nospam_@.gmail.com
>
|||Using Autohotkey - www.autohotkey.com. Its a scripting language to
automate tasks. Right now I've had problems pulling selected text
using its tools - some editors don't get along with it so I've resorted
to using the clipboard but the SSMS behavior is different that other
tools.
Thanks
William
Paul A. Mestemaker II [MSFT] wrote:[vbcol=seagreen]
> How are you automating the tasks?
> Paul A. Mestemaker II
> Program Manager
> Microsoft SQL Server Manageability
> http://blogs.msdn.com/sqlrem/
> <wgbrown@.gmail.com> wrote in message
> news:1164146398.788232.175850@.k70g2000cwa.googlegr oups.com...
|||I'm not famliar with this utility at all. Can you give us an example of the
task you'd like to automate? Maybe we can find you a way to complete your
task using SQLCMD Mode in SSMS.
-Paul
<wgbrown@.gmail.com> wrote in message
news:1164179204.938292.85990@.k70g2000cwa.googlegro ups.com...
> Using Autohotkey - www.autohotkey.com. Its a scripting language to
> automate tasks. Right now I've had problems pulling selected text
> using its tools - some editors don't get along with it so I've resorted
> to using the clipboard but the SSMS behavior is different that other
> tools.
> Thanks
> William
>
> Paul A. Mestemaker II [MSFT] wrote:
>
|||Good Morning Paul.
There isn't one function but a bunch of little ones. Currently I'm
closing off certain characters like { [ ( " so I get {}, [], (), "" (I
work a lot in mdx). But I'd like to be able to reformat highlighted
text. Proper case words or such.
I was surprised about the copy behavior - I'd never noticed it.
Thanks
William
Paul A. Mestemaker II [MSFT] wrote:[vbcol=seagreen]
> I'm not famliar with this utility at all. Can you give us an example of the
> task you'd like to automate? Maybe we can find you a way to complete your
> task using SQLCMD Mode in SSMS.
> -Paul
> <wgbrown@.gmail.com> wrote in message
> news:1164179204.938292.85990@.k70g2000cwa.googlegro ups.com...
|||See if this helps with what you want?
http://www.red-gate.com/products/SQL_Refactor/index.htm
Andrew J. Kelly SQL MVP
<wgbrown@.gmail.com> wrote in message
news:1164215484.851461.150040@.h54g2000cwb.googlegr oups.com...
> Good Morning Paul.
> There isn't one function but a bunch of little ones. Currently I'm
> closing off certain characters like { [ ( " so I get {}, [], (), "" (I
> work a lot in mdx). But I'd like to be able to reformat highlighted
> text. Proper case words or such.
> I was surprised about the copy behavior - I'd never noticed it.
> Thanks
> William
>
> Paul A. Mestemaker II [MSFT] wrote:
>
|||I've looked at that and it looks really nice. As soon as I have time I
want to try it out. Much of my work is around MDX however, and the
tools are more limited. For good reason, for every person who does MDX
there must be 1000 (or more) sql programmers.
Thanks for the tip.
William
Andrew J. Kelly wrote:[vbcol=seagreen]
> See if this helps with what you want?
> http://www.red-gate.com/products/SQL_Refactor/index.htm
> --
> Andrew J. Kelly SQL MVP
> <wgbrown@.gmail.com> wrote in message
> news:1164215484.851461.150040@.h54g2000cwb.googlegr oups.com...
behavior in SSMS.
Right now if you have no text selected in SSMS and do a control-c the
entire line is copied, but I want the copy to be null. Basicly I'm
automating some repetive functions and need to move only the selected
text to the clipboard. If no text is selected then want nothing.
Any ideas?
Thanks
William
wgbrown_nospam_@.gmail.com
How are you automating the tasks?
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
<wgbrown@.gmail.com> wrote in message
news:1164146398.788232.175850@.k70g2000cwa.googlegr oups.com...
>I realize that this seems odd, but I want to change the copy paste
> behavior in SSMS.
> Right now if you have no text selected in SSMS and do a control-c the
> entire line is copied, but I want the copy to be null. Basicly I'm
> automating some repetive functions and need to move only the selected
> text to the clipboard. If no text is selected then want nothing.
> Any ideas?
> Thanks
> William
> wgbrown_nospam_@.gmail.com
>
|||Using Autohotkey - www.autohotkey.com. Its a scripting language to
automate tasks. Right now I've had problems pulling selected text
using its tools - some editors don't get along with it so I've resorted
to using the clipboard but the SSMS behavior is different that other
tools.
Thanks
William
Paul A. Mestemaker II [MSFT] wrote:[vbcol=seagreen]
> How are you automating the tasks?
> Paul A. Mestemaker II
> Program Manager
> Microsoft SQL Server Manageability
> http://blogs.msdn.com/sqlrem/
> <wgbrown@.gmail.com> wrote in message
> news:1164146398.788232.175850@.k70g2000cwa.googlegr oups.com...
|||I'm not famliar with this utility at all. Can you give us an example of the
task you'd like to automate? Maybe we can find you a way to complete your
task using SQLCMD Mode in SSMS.
-Paul
<wgbrown@.gmail.com> wrote in message
news:1164179204.938292.85990@.k70g2000cwa.googlegro ups.com...
> Using Autohotkey - www.autohotkey.com. Its a scripting language to
> automate tasks. Right now I've had problems pulling selected text
> using its tools - some editors don't get along with it so I've resorted
> to using the clipboard but the SSMS behavior is different that other
> tools.
> Thanks
> William
>
> Paul A. Mestemaker II [MSFT] wrote:
>
|||Good Morning Paul.
There isn't one function but a bunch of little ones. Currently I'm
closing off certain characters like { [ ( " so I get {}, [], (), "" (I
work a lot in mdx). But I'd like to be able to reformat highlighted
text. Proper case words or such.
I was surprised about the copy behavior - I'd never noticed it.
Thanks
William
Paul A. Mestemaker II [MSFT] wrote:[vbcol=seagreen]
> I'm not famliar with this utility at all. Can you give us an example of the
> task you'd like to automate? Maybe we can find you a way to complete your
> task using SQLCMD Mode in SSMS.
> -Paul
> <wgbrown@.gmail.com> wrote in message
> news:1164179204.938292.85990@.k70g2000cwa.googlegro ups.com...
|||See if this helps with what you want?
http://www.red-gate.com/products/SQL_Refactor/index.htm
Andrew J. Kelly SQL MVP
<wgbrown@.gmail.com> wrote in message
news:1164215484.851461.150040@.h54g2000cwb.googlegr oups.com...
> Good Morning Paul.
> There isn't one function but a bunch of little ones. Currently I'm
> closing off certain characters like { [ ( " so I get {}, [], (), "" (I
> work a lot in mdx). But I'd like to be able to reformat highlighted
> text. Proper case words or such.
> I was surprised about the copy behavior - I'd never noticed it.
> Thanks
> William
>
> Paul A. Mestemaker II [MSFT] wrote:
>
|||I've looked at that and it looks really nice. As soon as I have time I
want to try it out. Much of my work is around MDX however, and the
tools are more limited. For good reason, for every person who does MDX
there must be 1000 (or more) sql programmers.
Thanks for the tip.
William
Andrew J. Kelly wrote:[vbcol=seagreen]
> See if this helps with what you want?
> http://www.red-gate.com/products/SQL_Refactor/index.htm
> --
> Andrew J. Kelly SQL MVP
> <wgbrown@.gmail.com> wrote in message
> news:1164215484.851461.150040@.h54g2000cwb.googlegr oups.com...
Monday, February 13, 2012
Copy Selected column data from table to another during Upgrade of App
Hi,
I need to write a script that will be called during the database upgrade of my application. This is part of reorg of the tables. The script has to get data for say 4 columns from table A and insert it into another table B. Table B has identity insert column and remaining 4 columns matching the ones to be copied. The data is dependent on user database, hence number of records needs to be copied might be different. Also the columns can have null values.
I tried using bcp Command as follows..
bcp "select colA,colB,colC,colD from A" queryout "c:\temp\A.dat" -t"\t" -r"\n" -c
I'm able to get the dat file, but not the format file. Can anyone tell me how to get it using query file with -c option. Also if there is better option to copy data, kindly let me know.
This is very critical. Appreciate your help.
Thanks,
Ramya.Why not copy the data into a local temporary (or permanent) table?
I presume (perhaps incorrectly) that you want to retain this data to be inserted back into the modified table (or another table) later during the upgrade process.
Regards,
hmscott|||That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..
Thanks,
Ramya.|||Create TableTemp (
ID int IDENTITY(1,1),
ColumnA varchar(10),
ColumnB varchar(10),
ColumnC varchar(10),
ColumnD varchar(10)
)
GO
INSERT INTO TableTemp (ColumnA, ColumnB, ColumnC, ColumnD)
SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM
MySourceTable
GO
ALTER TABLE MySourceTable DROP COLUMN ColumnA
GO
This will leave a permanent copy of the data from MySourceTable in TempTable.
Regards,
hmscott
That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..
Thanks,
Ramya.
I need to write a script that will be called during the database upgrade of my application. This is part of reorg of the tables. The script has to get data for say 4 columns from table A and insert it into another table B. Table B has identity insert column and remaining 4 columns matching the ones to be copied. The data is dependent on user database, hence number of records needs to be copied might be different. Also the columns can have null values.
I tried using bcp Command as follows..
bcp "select colA,colB,colC,colD from A" queryout "c:\temp\A.dat" -t"\t" -r"\n" -c
I'm able to get the dat file, but not the format file. Can anyone tell me how to get it using query file with -c option. Also if there is better option to copy data, kindly let me know.
This is very critical. Appreciate your help.
Thanks,
Ramya.Why not copy the data into a local temporary (or permanent) table?
I presume (perhaps incorrectly) that you want to retain this data to be inserted back into the modified table (or another table) later during the upgrade process.
Regards,
hmscott|||That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..
Thanks,
Ramya.|||Create TableTemp (
ID int IDENTITY(1,1),
ColumnA varchar(10),
ColumnB varchar(10),
ColumnC varchar(10),
ColumnD varchar(10)
)
GO
INSERT INTO TableTemp (ColumnA, ColumnB, ColumnC, ColumnD)
SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM
MySourceTable
GO
ALTER TABLE MySourceTable DROP COLUMN ColumnA
GO
This will leave a permanent copy of the data from MySourceTable in TempTable.
Regards,
hmscott
That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..
Thanks,
Ramya.
Subscribe to:
Posts (Atom)