Query for create table

Hai All,
I have table t1 in db1.
I want to create a table t1 in DB2 with the same structure as t1 of
db1.
Can anyone tell me the query?
Urgent... Looking forward for the response...Well you use QA, right click on the table t1 in db1 and choose option "scrip
t
object to new window.. and then change the database to execute the script.
But if you want a query, then try this.
use db2
select * into t1 from db1.dbo.t1
--replace the * with the column list
But in this case you wo't get the indexes and constraints. Just the skeleton
.
Hope this helps.|||There's a difference between these two techniques. The first (script db)
will give you just the structure, as the OP asked for, but the second will
copy all the DATA too. If that is what is wanted, great, but if not, you
might end up with a process that takes a long time, and a lot of logging
going on in DB2 and a lot of space being wasted.
To copy a table without copying the data, you need a select statement that
is guaranteed to return no rows, something like this:
use db2
select * into t1 from db1.dbo.t1
where 1 = 0
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:78E120AB-5BC0-428C-9AC9-AC802A590532@.microsoft.com...
> Well you use QA, right click on the table t1 in db1 and choose option
> "script
> object to new window.. and then change the database to execute the script.
> But if you want a query, then try this.
> use db2
> select * into t1 from db1.dbo.t1
> --replace the * with the column list
> But in this case you wo't get the indexes and constraints. Just the
> skeleton.
> Hope this helps.
>|||Oops. I did intend to give the where clause 1=0. Dunno How I missed/messed i
t
:)
thanks for pointing it out.
--
"Kalen Delaney" wrote:

> There's a difference between these two techniques. The first (script db)
> will give you just the structure, as the OP asked for, but the second will
> copy all the DATA too. If that is what is wanted, great, but if not, you
> might end up with a process that takes a long time, and a lot of logging
> going on in DB2 and a lot of space being wasted.
> To copy a table without copying the data, you need a select statement that
> is guaranteed to return no rows, something like this:
> use db2
> select * into t1 from db1.dbo.t1
> where 1 = 0
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:78E120AB-5BC0-428C-9AC9-AC802A590532@.microsoft.com...
>
>|||Kalen
> use db2
> select * into t1 from db1.dbo.t1
> where 1 = 0
I think if the OP asked for table's structure the above technique is not
perfect because it does not move a Primary Keys as well as CONSTRAINS
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23%23Qyo6pdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> There's a difference between these two techniques. The first (script db)
> will give you just the structure, as the OP asked for, but the second will
> copy all the DATA too. If that is what is wanted, great, but if not, you
> might end up with a process that takes a long time, and a lot of logging
> going on in DB2 and a lot of space being wasted.
> To copy a table without copying the data, you need a select statement that
> is guaranteed to return no rows, something like this:
> use db2
> select * into t1 from db1.dbo.t1
> where 1 = 0
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:78E120AB-5BC0-428C-9AC9-AC802A590532@.microsoft.com...
>|||Yes, I meant to just extend to what Omnibuzz said. He mentioned that this
solution wouldn't give you indexes and constraints, but I was only pointing
out that it WOULD give you the data, if you don't include the where clause
that is always false.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23HuxIZxdGHA.4532@.TK2MSFTNGP02.phx.gbl...
> Kalen
> I think if the OP asked for table's structure the above technique is not
> perfect because it does not move a Primary Keys as well as CONSTRAINS
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23%23Qyo6pdGHA.3364@.TK2MSFTNGP05.phx.gbl...
>

Query Engine Error

Hi
We have a table change in the DB2 database. I mean an old table
is being replaced with a new one. I updated the reports by
replacing the old table column names with the new ones in all
the reports. all the reports are running fine. there's one
report which is giving Query Engine Error. it pops up a window
which says 'The Query can not be performed. An illegal link
cycle was detected'.Please help me in finding out the issue.
Thanks in Advance

Regards
MadhaviDid you verify database inside your report file?|||I did verify. And its not showing any error. But i am not able to see the SQL also.
When i say 'Show SQL' i get the same error as when i run the report.|||I did verify. And its not showing any error. But i am not able to see the SQL also.
When i say 'Show SQL' i get the same error as when i run the report.
If you couldn't see the SQL statement, it means that table structure inside
rpt file corrupted. You need to recreate them.

Query DB2 from SSRS and Convert Integer to Time

Hi,

I am querying DB2 from SSRS. I get an interger back that represents a time like this: HHMMSS

(The data type in DB2 is an integer.) I would like to make this representation of the time display a little more friendly. Does anyone have a good idea on how I can change the query or SSRS format to display semi-colons to break up the hours, minutes and seconds? Also, the other issue is that since I don't get leading zeros back....they probably need to be added to this value if the value is not a full 6 characters.

Here is one approach to this issue.

DECLARE @.MyTime int
SET @.MyTime = 63000

SELECT cast( stuff( stuff( right( '0' + cast( @.MyTime AS varchar(6) ), 6 ), 3, 0 , ':'), 6, 0, ':' ) AS datetime )

If this is a regular occurance, you may wish to create a FUNCTION for this task.

|||

Thanks. It looks like DB2 does not like the STUFF command, so I will try to apply this concept after I find a similar DB2 command.

query around databases

I need to query tables on different databases, which all reside on the same database server. How to do this?

is it db1.tablex inner join db2.tabley ?

Hi dear,

try this

db1.dbo.tablex inner join db2.dbo.tabley

Thanks

Best Regards,

Muhammad Akhtar Shiekh