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 row from tableA to tableB

I want to copy all columns from tblA into tblB where tblA.UniqueID =
parameter @.ID without having to list each column name in an insert
statement. Is this possible?
Thanks,
lqI forgot to mention all column names and data types are identical in
tblA and tblB except for the UniqueID which is PK in tblA and not PK in
tblB.
lq|||Sure,

INSERT INTO tblB
SELECT *
FROM tblA
WHERE UniqueID = @.ID

Stu|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I want to copy all columns from tblA into tblB where tblA.UniqueID =
> parameter @.ID without having to list each column name in an insert
> statement. Is this possible?

Under some circumstances, yes.

Then again, in application code, I think it is very bad practice to say:

INSERT tbl2 SELECT * FROM tbl1

SELECT * itself is bad practice, so are INSERT statements without listing
of target columns.

Why are this bad:

* You cannot see if a column is actually used.
* If the table defintion changes, the result of the SELECT changes.
* And for INSERT it is likely to result in the INSERT statment failing.

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

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

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 Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris
|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!
|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.WithDependencies = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.DestinationDatabase = "Clarina_N";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.TransferData();

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

And same problem with defaults even when
xfr.Option.Default = true;

|||This has nothing to do with SSIS. You should try posting to the SMO forum.

Thanks,
Mattsqlsql

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy comma separated text file into a Sql server table

Hi
I have a comma separated text file which I would like to import into a table. There are some columns which are enclosed in double quotes and others which are not. I've tried bcp - it didn't work.
Can anyone help ? An example would be helpful.
Thanks
YogeshUse the DTS import wizard.
Source: Text File, enter your text file location
text qualifier: Double Quote {"}

Copy and Paste from access into columns

Is there a way to copy and paste data into columns when view the table columns?

I would like to be able to copy and paste data from an access database.

TIA

I don't believe SSMSE supports this functionality. If you need to move data from Access (Jet) to SQL Server, you have a number of choices:

Export the data from within Access.|||Thanks Mike.

Converting Rows to Columns in SQL7

I do a:
SELECT * FROM xxx

And Get:
Date Place Sum
A M 1
A O 3
A P 2
B O 5
B M 4
B P 2

And I want it to look like:

Date M O P
A 1 3 2
B 4 5 2

Can you think of an EASY way to do this?
I can do it with a cursor that constructs a SQL statement, which I EXEC, but the 8000 character limit may prove to be a limiting factor.
sp_execsql is somewhat messy for the nature of this issue.
Any input is appreciated.

Thanks in advance."Brad Joss" <bradjoss@.hotmail.com> wrote in message news:<bgq7p7$m03$1@.news01.intel.com>...
> I do a:
> SELECT * FROM xxx
> And Get:
> Date Place Sum
> A M 1
> A O 3
> A P 2
> B O 5
> B M 4
> B P 2
> And I want it to look like:
> Date M O P
> A 1 3 2
> B 4 5 2
> Can you think of an EASY way to do this?
> I can do it with a cursor that constructs a SQL statement, which I EXEC,
> but the 8000 character limit may prove to be a limiting factor.
> sp execsql is somewhat messy for the nature of this issue.
> Any input is appreciated.
> Thanks in advance.
> --

Assuming that (Date, Place) is a unique combination, then this should work:

select
Date,
sum(case when Place = 'M' then Sum else NULL end) as 'M',
sum(case when Place = 'O' then Sum else NULL end) as 'O',
sum(case when Place = 'P' then Sum else NULL end) as 'P'
from
xxx
group by
Date

Simon|||i tried something but couldnt do anything about it. only if you send
me the solution you have it will help me(though with the limiting
factor)

Else i think at any point of time the 8000 character limit will be a
factor that will not allow you to do this always......
RVG

"Brad Joss" <bradjoss@.hotmail.com> wrote in message news:<bgq7p7$m03$1@.news01.intel.com>...
> I do a:
> SELECT * FROM xxx
> And Get:
> Date Place Sum
> A M 1
> A O 3
> A P 2
> B O 5
> B M 4
> B P 2
> And I want it to look like:
> Date M O P
> A 1 3 2
> B 4 5 2
> Can you think of an EASY way to do this?
> I can do it with a cursor that constructs a SQL statement, which I EXEC,
> but the 8000 character limit may prove to be a limiting factor.
> sp execsql is somewhat messy for the nature of this issue.
> Any input is appreciated.
> Thanks in advance.
> --

converting rows to columns

have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.

Can somebody help me out
PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZHere's one solution:

CREATE TABLE MyTable
(
begin_time smalldatetime NOT NULL,
end_time smalldatetime NOT NULL,
Name varchar(10) NOT NULL,
Pieces int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
(
begin_time,
end_time,
Name
)
)

INSERT INTO MyTable
SELECT '10:00', '10:15', 'PopCorn', 3
UNION ALL SELECT '10:15', '10:30', 'Biscuits', 5
UNION ALL SELECT '10:30', '10:45', 'PopCorn', 2

SELECT
CONVERT(char(5), a.begin_time, 108) AS begin_time,
CONVERT(char(5), a.end_time, 108) AS end_time,
ISNULL(SUM(b.Pieces), 0) AS PopCorn,
ISNULL(SUM(c.Pieces), 0) AS Biscuits
FROM
(
SELECT DISTINCT
begin_time,
end_time
FROM MyTable
) AS a
LEFT JOIN MyTable b ON
b.begin_time = a.begin_time AND
b.end_time = a.end_time AND
b.Name = 'PopCorn'
LEFT JOIN MyTable c ON
c.begin_time = a.begin_time AND
c.end_time = a.end_time AND
c.Name = 'Biscuits'
GROUP BY
a.begin_time,
a.end_time
ORDER BY
a.begin_time
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pooj" <poojaahirrao@.hotmail.com> wrote in message
news:94541780.0401100526.5d3178ae@.posting.google.c om...
> have a urgent requirement. Please somebody help me.
> I have a table departinfo with following records
> begin_time end_time Name Pieces
> 10:00 10:15 PopCorn 3
> 10:15 10:30 Biscuits 5
> 10:30 10:45 PopCorn 2
> Now I need to run a sql query and the output should be as below :
> begin_time end_time PopCorn Biscuits
> 10:00 10:15 3 0
> 10:15 10:30 0 5
> 10:30 10:45 2 0
> Please note that only one column i.e. PopCorn is created in spite of
> having multiple records in the table. Similarly the records are not
> fixed. I mean that
> there can be n number of records and the columns should be uniquely
> created.
>
> Can somebody help me out
PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ|||Hi

Check out a crosstab query that will transform your rows to columns:

http://support.microsoft.com/defaul...b;EN-US;q175574

There are many posts about pivot tables or crosstab queries.. Search google
for more.

John

"Pooj" <poojaahirrao@.hotmail.com> wrote in message
news:94541780.0401100526.5d3178ae@.posting.google.c om...
> have a urgent requirement. Please somebody help me.
> I have a table departinfo with following records
> begin_time end_time Name Pieces
> 10:00 10:15 PopCorn 3
> 10:15 10:30 Biscuits 5
> 10:30 10:45 PopCorn 2
> Now I need to run a sql query and the output should be as below :
> begin_time end_time PopCorn Biscuits
> 10:00 10:15 3 0
> 10:15 10:30 0 5
> 10:30 10:45 2 0
> Please note that only one column i.e. PopCorn is created in spite of
> having multiple records in the table. Similarly the records are not
> fixed. I mean that
> there can be n number of records and the columns should be uniquely
> created.
>
> Can somebody help me out
PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<btp2ft$h0i$1@.titan.btinternet.com>...
> Hi
> Check out a crosstab query that will transform your rows to columns:
> http://support.microsoft.com/defaul...b;EN-US;q175574
> There are many posts about pivot tables or crosstab queries.. Search google
> for more.
> John
> "Pooj" <poojaahirrao@.hotmail.com> wrote in message
> news:94541780.0401100526.5d3178ae@.posting.google.c om...
> > have a urgent requirement. Please somebody help me.
> > I have a table departinfo with following records
> > begin_time end_time Name Pieces
> > 10:00 10:15 PopCorn 3
> > 10:15 10:30 Biscuits 5
> > 10:30 10:45 PopCorn 2
> > Now I need to run a sql query and the output should be as below :
> > begin_time end_time PopCorn Biscuits
> > 10:00 10:15 3 0
> > 10:15 10:30 0 5
> > 10:30 10:45 2 0
> > Please note that only one column i.e. PopCorn is created in spite of
> > having multiple records in the table. Similarly the records are not
> > fixed. I mean that
> > there can be n number of records and the columns should be uniquely
> > created.
> > Can somebody help me out
> PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
> ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ

Another solution would be using a union statement

select * from departinfo where popcorn = 0
union select * from departinfo where bisuits = 0
order by begin_time|||Hello Dan,
Thanks for the quick response !
The solution you have provided wil not solve my problem due to
following reason :

The departinfo table records are not fixed as shown below. The time
difference can vary also the remainig data. The table will be
populated in the following format. Only the format is fixed the
records are not.

begin_datetime end_datetime Name Pieces

So my requirement is to convert the Name field into unique distinct
columns and match the pieces.
for example if the Name field contains items like Biscuits, Pizza,
PopCorn
then these many columns should be created and matched with the pieces.
Please help !
Thanks
Pooj

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<8HTLb.1542$i4.11@.newsread1.news.atl.earthlink.net>...
> Here's one solution:
> CREATE TABLE MyTable
> (
> begin_time smalldatetime NOT NULL,
> end_time smalldatetime NOT NULL,
> Name varchar(10) NOT NULL,
> Pieces int NOT NULL
> CONSTRAINT PK_MyTable PRIMARY KEY
> (
> begin_time,
> end_time,
> Name
> )
> )
> INSERT INTO MyTable
> SELECT '10:00', '10:15', 'PopCorn', 3
> UNION ALL SELECT '10:15', '10:30', 'Biscuits', 5
> UNION ALL SELECT '10:30', '10:45', 'PopCorn', 2
> SELECT
> CONVERT(char(5), a.begin_time, 108) AS begin_time,
> CONVERT(char(5), a.end_time, 108) AS end_time,
> ISNULL(SUM(b.Pieces), 0) AS PopCorn,
> ISNULL(SUM(c.Pieces), 0) AS Biscuits
> FROM
> (
> SELECT DISTINCT
> begin_time,
> end_time
> FROM MyTable
> ) AS a
> LEFT JOIN MyTable b ON
> b.begin_time = a.begin_time AND
> b.end_time = a.end_time AND
> b.Name = 'PopCorn'
> LEFT JOIN MyTable c ON
> c.begin_time = a.begin_time AND
> c.end_time = a.end_time AND
> c.Name = 'Biscuits'
> GROUP BY
> a.begin_time,
> a.end_time
> ORDER BY
> a.begin_time
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pooj" <poojaahirrao@.hotmail.com> wrote in message
> news:94541780.0401100526.5d3178ae@.posting.google.c om...
> > have a urgent requirement. Please somebody help me.
> > I have a table departinfo with following records
> > begin_time end_time Name Pieces
> > 10:00 10:15 PopCorn 3
> > 10:15 10:30 Biscuits 5
> > 10:30 10:45 PopCorn 2
> > Now I need to run a sql query and the output should be as below :
> > begin_time end_time PopCorn Biscuits
> > 10:00 10:15 3 0
> > 10:15 10:30 0 5
> > 10:30 10:45 2 0
> > Please note that only one column i.e. PopCorn is created in spite of
> > having multiple records in the table. Similarly the records are not
> > fixed. I mean that
> > there can be n number of records and the columns should be uniquely
> > created.
> > Can somebody help me out
> PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
> ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ|||Hi John,

Thanks for the quick reply.I checked the link u have provided but it
seems it
won't solve my problem b'coz DaypartInfo table records are not fixed.
They can vary E.g.my table format is as below:
begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

In this table I want to convert the Nae field records as columns i.e.
Name field may contain PoCorn ,Pizza ,Biscuits,Cheese etc . Basically
these records can vary and those should be converted as columns at
runtime...

Please help...

Thanks,
Pooj.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<btp2ft$h0i$1@.titan.btinternet.com>...
> Hi
> Check out a crosstab query that will transform your rows to columns:
> http://support.microsoft.com/defaul...b;EN-US;q175574
> There are many posts about pivot tables or crosstab queries.. Search google
> for more.
> John
> "Pooj" <poojaahirrao@.hotmail.com> wrote in message
> news:94541780.0401100526.5d3178ae@.posting.google.c om...
> > have a urgent requirement. Please somebody help me.
> > I have a table departinfo with following records
> > begin_time end_time Name Pieces
> > 10:00 10:15 PopCorn 3
> > 10:15 10:30 Biscuits 5
> > 10:30 10:45 PopCorn 2
> > Now I need to run a sql query and the output should be as below :
> > begin_time end_time PopCorn Biscuits
> > 10:00 10:15 3 0
> > 10:15 10:30 0 5
> > 10:30 10:45 2 0
> > Please note that only one column i.e. PopCorn is created in spite of
> > having multiple records in the table. Similarly the records are not
> > fixed. I mean that
> > there can be n number of records and the columns should be uniquely
> > created.
> > Can somebody help me out
> PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
> ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ|||Hi

This may help...

http://www.sqlteam.com/item.asp?ItemID=2955

John|||You can do this very easily and without any
sql coding with the RAC utility for S2k.
It can be used to generate dynamic crosstabs
and solve all types of problems in an easy way.
It's similar in concept to Access crosstab but
much more powerful.

RAC v2.2 and QALite @.
www.rac4sql.net

Converting Rows to Columns

Hi,
I have this resultset :
Mem_id Specialization
19001 Internal medicine
19001 Endoscopy
19001 Oncology

I need to output this like below--(in 1 row)

19001 Internal medicine Endoscopy Oncology

I am using SQL2005. Can anyone please help asap.

ThanksUse Pivot function on SQL2005.

converting rows into columns when the count is not known

i have 2 rows with 3 columns each. ( each rows has a colun called ScoreType which contains different data like Performancelevel,scaledscore

Subject Marks ScoreType
A 2 Performancelevel
A 123 scaledscore
B 4 Performancelevel
B 678 scaledscore

i want this data as

Subject PerformanceLevel ScaledScore
A 123 2
B 678 4

how can i acheive this, Please not that the number of scoretypes is not constant, could be more also

Quote:

Originally Posted by praneethraj

i have 2 rows with 3 columns each. ( each rows has a colun called ScoreType which contains different data like Performancelevel,scaledscore

how can i acheive this, Please not that the number of scoretypes is not constant, could be more also

you didn't point your Database versions. anyway, you can use Cross Tab query.

Converting Rows into Columns MS SQL 2K

I have a SP that returns the information I want but it returns it in 2
separate queries.

Example:
Query 1
Name, Number, Class
Row 1- Mike Phillips, 154AA, AA
and
Query 2
Time, Manual
Row 1 -12:45:22,0
Row 2 -13:04:56,0

What I want it to look like is:
Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2
Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0

Here is the query I'm using:
DECLARE Class cursor

FOR
--here we get a list of distinct classes to pass to the Class cursor
select Distinct(class_ID) from kt_member_lap
where Race_ID = 83
order by Class_ID;

OPEN Class;

DECLARE @.RaceID int
DECLARE@.RacerCount int
DECLARE @.ClassID char(50)
DECLARE @.classcount
DECLARE @.Racer char(50)
DECLARE @.i int

SET @.RaceID = 83
--this is where we loop through the classes
FETCH NEXT FROM Class INTO @.ClassID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN

IF (@.@.FETCH_STATUS <> -2)

DECLARE Lap cursor

FOR
Select DISTINCT(Member_ID) from KT_MEMBER_LAP
Where class_ID = @.classID and race_id = @.RaceID

OPEN Lap;

--this is to begin counting from the first lap
SET @.i = 1;

FETCH NEXT FROM Lap INTO @.Racer
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN

IF (@.@.FETCH_STATUS <> -2)
SELECT KT_MEMBER.MEMBER_FNAME + ' ' +
KT_MEMBER.MEMBER_LNAME As MemberName,
CONVERT(nvarchar(3),
KT_MEMBER_CLASS.MEMBER_CLASS_BIKE_NUM) + KT_CLASS.CLASS_LETTER As
BikeNumber,
KT_CLASS.CLASS_DESC
FROM KT_CLASS INNER JOIN
KT_MEMBER_CLASS ON KT_CLASS.CLASS_ID =
KT_MEMBER_CLASS.CLASS_ID INNER JOIN
KT_MEMBER ON KT_MEMBER_CLASS.MEMBER_ID =
KT_MEMBER.MEMBER_ID
WHERE KT_MEMBER.MEMBER_ID = @.Racer and KT_CLASS.CLASS_ID =
@.ClassID

--SELECT @.Racer, @.ClassID

Select MEMBER_LAP_TIME_REAL, member_lap_manual from KT_MEMBER_LAP
Where Member_ID = @.Racer and class_ID = @.classID and race_id =
@.RaceID
ORDER BY MEMBER_LAP_TIME_REAL

--here I count up for the next lap
SET @.i = @.i + 1;

FETCH NEXT FROM Lap INTO @.Racer
END

CLOSE Lap;
DEALLOCATE Lap;

FETCH NEXT FROM Class INTO @.ClassID
END

CLOSE Class;
DEALLOCATE Class;

Any help would be appreciated.[posted and mailed, please reply in news]

dare197 (daniel.white@.perceptivetech.com) writes:
> I have a SP that returns the information I want but it returns it in 2
> separate queries.
> Example:
> Query 1
> Name, Number, Class
> Row 1- Mike Phillips, 154AA, AA
> and
> Query 2
> Time, Manual
> Row 1 -12:45:22,0
> Row 2 -13:04:56,0
> What I want it to look like is:
> Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2
> Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0

Could there be any number of Time, Manual rows or is there never more
than two? I will assume that you always have two. Then you can try
this query:

SELECT m.MEMBER_FNAME + ' ' + m.MEMBER_LNAME As MemberName,
CONVERT(nvarchar(3), mc.MEMBER_CLASS_BIKE_NUM) +
c.CLASS_LETTER As BikeNumber,
c.CLASS_DESC,
ml1.MEMBER_LAP_TIME_REAL AS "Time 1",
ml1.member_lap_manual AS "Manual 1",
ml2.MEMBER_LAP_TIME_REAL AS "Time 2",
ml2.member_lap_manual AS "Manual 2",
FROM KT_CLASS c
JOIN KT_MEMBER_CLASS mc ON c.CLASS_ID = mc.CLASS_ID
JOIN KT_MEMBER m ON mc.MEMBER_ID = m.MEMBER_ID
JOIN KT_MEMBER_LAP ml1 ON ml1.MEMBER_ID = m.MEMBER_ID
AND mll.CLASS_ID = mc.CLASS_ID
JOIN KT_MEMBER_LAP ml2 ON ml2.MEMBER_ID = m.MEMBER_ID
AND ml2.CLASS_ID = mc.CLASS_ID
AND ml2.member_lap_manual > ml1.member_lap_manual
WHERE m.MEMBER_ID = @.Racer
AND c.CLASS_ID = @.ClassID
ORDER BY c.CLASS_ID, m.MEMBER_ID

Here I have collapsed everything into one query, without any cursor, as
I could see no need for a cursor. Cursors can be a magnitude slower than
set-based statements, so there all reasons to avoid them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

COnverting Numeric data type (Oracle) to Date Data type using SSIS

We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.

I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.

On Error, If I fail the component, then the error is :

There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Regards

RH

If you are using a query to go against Oracle, you'll likely want to cast that as a varchar and then work with it in SSIS. Not sure that YYYYMMDD will cast to DT_DATE. You'll likely have to substring pieces of that to get it into a date. There are plenty of examples here on this forum for doing that. Just search for "YYYYMMDD."|||

Thanks for the reply. I used SQL on the OLEDB Source and added a TO_DATE expression in the SQL and use that column as input to my destination column.

This worked fine.

RH