Copy table to a new database

Hi, I have a old DB "TrackOLD" and have instald a new "TrackNEW"
I need som data in the "TrackOLD" DB
How do I copy a table from "TrackOLD" to "TrackNEW"?
Both DB are on the same server.
Thanksmsnews.microsoft.com wrote:
> Hi, I have a old DB "TrackOLD" and have instald a new "TrackNEW"
> I need som data in the "TrackOLD" DB
> How do I copy a table from "TrackOLD" to "TrackNEW"?
> Both DB are on the same server.
> Thanks
>
To insert the "old" data into a new table in the "new" DB, do this:
SELECT *
INTO NewDB.dbo.NewTable
FROM OldDB.dbo.OldTable
(note that this WILL NOT bring over indexes, defaults, triggers,
constraints, or anything else related to the table, JUST the data)
To insert the "old" data into an existing table in the "new" DB, do this:
INSERT INTO NewDB.dbo.NewTable
SELECT *
FROM OldDB.dbo.OldTable
(you may encounter problems due to trying to insert IDENTITY values - if
so, replace the SELECT * with the explicit list of columns that you want
to copy over, excluding the IDENTITY column)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, I have copy all the data I need.
Gjerde
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A4E831.7020904@.realsqlguy.com...
> msnews.microsoft.com wrote:
> To insert the "old" data into a new table in the "new" DB, do this:
> SELECT *
> INTO NewDB.dbo.NewTable
> FROM OldDB.dbo.OldTable
> (note that this WILL NOT bring over indexes, defaults, triggers,
> constraints, or anything else related to the table, JUST the data)
> To insert the "old" data into an existing table in the "new" DB, do this:
> INSERT INTO NewDB.dbo.NewTable
> SELECT *
> FROM OldDB.dbo.OldTable
> (you may encounter problems due to trying to insert IDENTITY values - if
> so, replace the SELECT * with the explicit list of columns that you want
> to copy over, excluding the IDENTITY column)
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copy table to a new database

Hi, I have a old DB "TrackOLD" and have instald a new "TrackNEW"
I need som data in the "TrackOLD" DB
How do I copy a table from "TrackOLD" to "TrackNEW"?
Both DB are on the same server.
Thanksmsnews.microsoft.com wrote:
> Hi, I have a old DB "TrackOLD" and have instald a new "TrackNEW"
> I need som data in the "TrackOLD" DB
> How do I copy a table from "TrackOLD" to "TrackNEW"?
> Both DB are on the same server.
> Thanks
>
To insert the "old" data into a new table in the "new" DB, do this:
SELECT *
INTO NewDB.dbo.NewTable
FROM OldDB.dbo.OldTable
(note that this WILL NOT bring over indexes, defaults, triggers,
constraints, or anything else related to the table, JUST the data)
To insert the "old" data into an existing table in the "new" DB, do this:
INSERT INTO NewDB.dbo.NewTable
SELECT *
FROM OldDB.dbo.OldTable
(you may encounter problems due to trying to insert IDENTITY values - if
so, replace the SELECT * with the explicit list of columns that you want
to copy over, excluding the IDENTITY column)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, I have copy all the data I need.
Gjerde
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A4E831.7020904@.realsqlguy.com...
> msnews.microsoft.com wrote:
>> Hi, I have a old DB "TrackOLD" and have instald a new "TrackNEW"
>> I need som data in the "TrackOLD" DB
>> How do I copy a table from "TrackOLD" to "TrackNEW"?
>> Both DB are on the same server.
>> Thanks
> To insert the "old" data into a new table in the "new" DB, do this:
> SELECT *
> INTO NewDB.dbo.NewTable
> FROM OldDB.dbo.OldTable
> (note that this WILL NOT bring over indexes, defaults, triggers,
> constraints, or anything else related to the table, JUST the data)
> To insert the "old" data into an existing table in the "new" DB, do this:
> INSERT INTO NewDB.dbo.NewTable
> SELECT *
> FROM OldDB.dbo.OldTable
> (you may encounter problems due to trying to insert IDENTITY values - if
> so, replace the SELECT * with the explicit list of columns that you want
> to copy over, excluding the IDENTITY column)
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

copy som data from one field split it up into 3 new fields

Hey there
I have to copy som data from one field and split it up into 3 seperat new
fields in the same table...
Eks.:
---
Data column Artis (Old field)
---
Artist
Larsen, Kim & Kjukken
Larsen, Kim
Larsen, Kim - Kjukken
---
Three new data columns
---
Firstname Lastname Band
Kim Larsen & Kjukken
Kim Larsen
Kim Larsen - Kjukken
My SQL Statement looks like this now, I just have to add the Band column and
the other - and & checks:
UPDATE Test_Products
SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
LEFT(artist, CHARINDEX(' ',artist) - 1)
ELSE
RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
END,
Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
ELSE
LEFT(artist, CHARINDEX(',',artist) - 1)
END
I hope hearing from you soon, because my project is totally jamed up until I
get this working and done.
Regards,
Lucien
Mvh
DC
--
Greetings ecoderHi
I'd prefer doing such thing on the client
create table #test
(
col varchar (50)
)
insert into #test values ('Larsen, Kim & Kjukken')
insert into #test values ('Larsen, Kim')
insert into #test values ('Larsen, Kim - Kjukken')
select LastName, FirstName, coalesce(Band,FirstName) Band
from (
select
col,
substring(col,1,Comma-1) LastName,
substring(col,Comma+1,Spce-4) FirstName,
nullif(substring(col,Spce+4,40),'') Band
from (
select
col,
charindex(',',col) Comma,
charindex(' ',col+space(1),charindex(',',col)) Spce
from #test
) D
) SplitNames
"ecoder" <ecoder@.discussions.microsoft.com> wrote in message
news:1B244528-6EBE-42DF-B1DB-3194BB9A2D7E@.microsoft.com...
> Hey there
> I have to copy som data from one field and split it up into 3 seperat new
> fields in the same table...
> Eks.:
> ---
> Data column Artis (Old field)
> ---
> Artist
> Larsen, Kim & Kjukken
> Larsen, Kim
> Larsen, Kim - Kjukken
> ---
> Three new data columns
> ---
> Firstname Lastname Band
> Kim Larsen & Kjukken
> Kim Larsen
> Kim Larsen - Kjukken
> --
> My SQL Statement looks like this now, I just have to add the Band column
> and
> the other - and & checks:
> UPDATE Test_Products
> SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> LEFT(artist, CHARINDEX(' ',artist) - 1)
> ELSE
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> END,
> Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> ELSE
> LEFT(artist, CHARINDEX(',',artist) - 1)
> END
> I hope hearing from you soon, because my project is totally jamed up until
> I
> get this working and done.
> Regards,
> Lucien
> Mvh
> DC
> --
> Greetings ecoder|||See if this helps:
select
c1,
left(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' ') - 1) as firstname,
left(c1, charindex(',', c1) - 1) as lastname,
parsename(stuff(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' '), 1, '.'), 1) as band
from
(
select 'Larsen, Kim & Kjukken'
union all
select 'Larsen, Kim'
union all
select 'Larsen, Kim - Kjukken'
) as t1(c1)
go
AMB
"ecoder" wrote:

> Hey there
> I have to copy som data from one field and split it up into 3 seperat new
> fields in the same table...
> Eks.:
> ---
> Data column Artis (Old field)
> ---
> Artist
> Larsen, Kim & Kjukken
> Larsen, Kim
> Larsen, Kim - Kjukken
> ---
> Three new data columns
> ---
> Firstname Lastname Band
> Kim Larsen & Kjukken
> Kim Larsen
> Kim Larsen - Kjukken
> --
> My SQL Statement looks like this now, I just have to add the Band column a
nd
> the other - and & checks:
> UPDATE Test_Products
> SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> LEFT(artist, CHARINDEX(' ',artist) - 1)
> ELSE
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> END,
> Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> ELSE
> LEFT(artist, CHARINDEX(',',artist) - 1)
> END
> I hope hearing from you soon, because my project is totally jamed up until
I
> get this working and done.
> Regards,
> Lucien
> Mvh
> DC
> --
> Greetings ecoder|||Hi ,Alejandro
Hehe, I have faced the same "problem". A Band column for Kim is NULL.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:22AF2E13-05F9-4DCA-BA56-D50D2A2A1031@.microsoft.com...
> See if this helps:
> select
> c1,
> left(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
> ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' ') - 1) as firstname,
> left(c1, charindex(',', c1) - 1) as lastname,
> parsename(stuff(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex('
> ',
> ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' '), 1, '.'), 1) as band
> from
> (
> select 'Larsen, Kim & Kjukken'
> union all
> select 'Larsen, Kim'
> union all
> select 'Larsen, Kim - Kjukken'
> ) as t1(c1)
> go
>
> AMB
> "ecoder" wrote:
>|||Hi Uri
When I run it in query analyzer the field without a band looks like this:
Firstname Lastname Band
Kim Larsen Kim
Why is that and what should I change to correct this?
Regards,
ecoder
"Uri Dimant" wrote:

> Hi
> I'd prefer doing such thing on the client
>
> create table #test
> (
> col varchar (50)
> )
> insert into #test values ('Larsen, Kim & Kjukken')
> insert into #test values ('Larsen, Kim')
> insert into #test values ('Larsen, Kim - Kjukken')
>
> select LastName, FirstName, coalesce(Band,FirstName) Band
> from (
> select
> col,
> substring(col,1,Comma-1) LastName,
> substring(col,Comma+1,Spce-4) FirstName,
> nullif(substring(col,Spce+4,40),'') Band
> from (
> select
> col,
> charindex(',',col) Comma,
> charindex(' ',col+space(1),charindex(',',col)) Spce
> from #test
> ) D
> ) SplitNames
>
> "ecoder" <ecoder@.discussions.microsoft.com> wrote in message
> news:1B244528-6EBE-42DF-B1DB-3194BB9A2D7E@.microsoft.com...
>
>|||Uri,
Let him / her to decide what to do with null values. The help you provided
is a good start, the rest is up to him / her.
AMB
"Uri Dimant" wrote:

> Hi ,Alejandro
> Hehe, I have faced the same "problem". A Band column for Kim is NULL.
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:22AF2E13-05F9-4DCA-BA56-D50D2A2A1031@.microsoft.com...
>
>|||Lookup LTRIM(),RTRIM() functions in the BOL
"ecoder" <ecoder@.discussions.microsoft.com> wrote in message
news:EABB8DE8-1DD3-44E0-875E-51B775A85038@.microsoft.com...
> Hi Uri
> When I run it in query analyzer the field without a band looks like this:
> Firstname Lastname Band
> Kim Larsen Kim
> Why is that and what should I change to correct this?
> Regards,
> ecoder
>
> "Uri Dimant" wrote:
>|||It's all working well becides when to band column is null
This is a urgent one...
If the band column is empty it copies the firstname Kim into the band column
.
What should I change....?
And thanks Uri and Alejandro for your help.
Greetings ecoder
"Alejandro Mesa" wrote:
> Uri,
> Let him / her to decide what to do with null values. The help you provided
> is a good start, the rest is up to him / her.
>
> AMB
> "Uri Dimant" wrote:
>|||If you are talking about Uri's solution, then use an empty string for the
second argument of the "coalesce" function or do not use the "coalesce"
function at all.

> select LastName, FirstName, coalesce(Band,FirstName) Band
select LastName, FirstName, coalesce(Band,'') Band
...
-- or
select LastName, FirstName, Band
...
AMB
"ecoder" wrote:
> It's all working well becides when to band column is null
> This is a urgent one...
> If the band column is empty it copies the firstname Kim into the band colu
mn.
> What should I change....?
> And thanks Uri and Alejandro for your help.
> --
> Greetings ecoder
>
> "Alejandro Mesa" wrote:
>|||God morning...
I have one last question though. I have to update an existing database table
with lots off records.
How do I change your or Uri examples to UPDATE instead of INSERT.
Sorry I'm not that hardcore in T-SQL!
:-)
But i'm trying...
Hope hearing from you.
Regards,
ecoder
"Alejandro Mesa" wrote:
> If you are talking about Uri's solution, then use an empty string for the
> second argument of the "coalesce" function or do not use the "coalesce"
> function at all.
>
> select LastName, FirstName, coalesce(Band,'') Band
> ...
> -- or
> select LastName, FirstName, Band
> ...
>
> AMB
>
> "ecoder" wrote:
>