Showing posts with label structure. Show all posts
Showing posts with label structure. Show all posts

Thursday, March 29, 2012

Combining two Tables into one TempTable, with a condition.

Hi,

I'm very new to Sql, and need to combine information from two tables into one temp table. All tables have the same structure.

The problem is I want to set a condition that if Field 1 is already in the TempTable, dont include that field from the second table. Along the lines of

Table1 Description = Blue, if Table 2 Description = Blue, dont include this row.

I have been trying to use INSERT..SELECT and UNION. , the only problem I have is that I cannot come up with a working conditional statement to prevent both complete sets of data being written to the TempTable.

I'm using Sql Server 2005 deveoper Edition and VS2005Pro.

Any ideas on how to get around this would be appreciated.

Tailor

Hi Tailor,

I'm not 100% clear on what your problem is. Is it that you wanted to union two tables and insert this result into a temp table only if data for a particular column (pk I presume) doesn't already exist in the temp table, or is it that you want to union the result of two tables but only select (and thus insert) distinct rows?

Can you provide a bit more info?

Cheers

Rob

|||

Hi Rob,

Thanks for your reply, I'll try to make this a little clearer with some code.

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

From Table1

Where (Id = 'STR001')

UNION

SELECT Description,Id,Reference

FROM Table2

WHERE (Id = 'STR001') AND Description != Table1.Description.

Without the AND statement, all data from both tables is inserted in tempTable, however with the And statement, I get the error message.

'The multi-part identifier Table1.Description could not be bound'

If the description from Table2, matches the Description in Table1, I dont want to include the row in the TempTable.

Sorry if i'm not too clear. At 64, trying to learn VS2005, Sql2005, and intergrating excel in my code, and write a reasonable sized application, all in six months is not something I can recommend. There is just so much to learn, and I dont have a good grasp of a lot of the basics.

If you can suggest a workable solution to my problem, it would be much appreciated.

John

|||

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

From Table1

Where (Id = 'STR001')

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

FROM Table2 t left outer join temptable tt on t.description = tt.description

WHERE (t.Id = 'STR001') AND tt.description is null

Regards

|||

This is a bit of a messy solution

either

1. Insert the data from both tables with a UNION, but place the UNION statement into a subquery and alias the columns to be inserted.

2. Insert data from table1. Then do the same insert from table2 where NOT EXISTS in the temp table (or table1, either will work). This is a common task you will need to learn to do in SQL Server

|||

Hi JMattias and SHughes,

Thank you both, very much, for your replies. It not only solved the problem, I learnt a lot more on the way through.

Your assistance is greatly appreciated.

John

sqlsql

Thursday, March 22, 2012

Combining 2 SQL databases

Is there an easy way to combine to SQL databases? Both DBs have the same structure but different data. If there just so happens to be duplicate records what will happen? Does anyone have any idea of where I should start at? :confused:There are a number of ways to skin this cat and there are a number of issues to look out for. Don't use the copy database wizard, it's a real peice of crap and it requires all users to be logged out and all connections to be cut.

Personally I would go into the SQL EM, right click on your database, go to Generate SQL Script and I would script out the database and all objects. Then I would open up the generated file in the QA and change the database name and execute the script.

This is where things get a little sticky. You might have problems with duplicate primary keys because of your 2 sets of tables and any foriegn keys that these primararies populate. Your going to have to figure this one out and without knowing your DB I am going to leave this one to you. Other things you might want to disable or look out for before you move the data are constraints and triggers (especially triggers that insert or update other records).

Once you have all of these little things compensated for, I would then DTS all of the data over from the original 2 DBs.

As for dupes, welcome to the world of the DBA. Your just going to have to clean these up using queries. Regular part of the job.|||OK, I'm really new to MS SQL server....

Whats is SQL EM? And where is the QA? Is there a client SQL version I can use on my workstation to manager all this without having to work on my server? :confused:|||If he's lucky, the databases were designed with GUIDs rather than IDENTITY keys... :)

Couldn't resist! :cool:|||SQL EM is the SQL Enterprise Manager. The QA is the query analyzer. Both of these and Books Online can be installed on your desktop using the installation disks. If you are this new, I would'nt attempt anything as complicated as this until you do a little reading. Buy a book. Take a class. Do something.

But if you insist on moving forward make backups of everything before you start thrashing around.

On your server, and maybe on your desktop you can find these tools by going to Start \ Programs \ Microsoft SQL Server.|||These databases were converted from MS Access. They were originally designed by a company called Tigerpaw Software. They have no support for combining Databases though.|||I'm not new to databases...I used mySQL and phpMyAdmin to manage it. I'm a programmer that got thrown into being a DBA...aint life grand....|||I'm not new to databases...I used mySQL and phpMyAdmin to manage it. I'm a programmer that got thrown into being a DBA...aint life grand....

Welcome to Olympus...

Give a sample fo 2 tables that are identical in both databases.

Script them using Enterprise Manager, and post the DDL

Did you install the sql server client side tools?|||I just jumped behind my SQL server ad generated a script off of one of the DBs. It of course created a .sql file which I can use to re-create the tables. So I need to do that for both. How do I get them in the same db...just change the name of the DB it creates and then run both scripts?|||I would size the database first and create the databse via Enterprise Manager.

I would then run the scripts in Query Analyzer.

But are the objects the same or not?

If they are you'll have trouble.

Also, there is really no need to combine them

You can query each from it's own database using their database names

USE Northwind
GO

SELECT * FROM Employees

SELECT * FROM pubs.dbo.Employee|||The objects are all the same structure, but the data if different for the most part. Some customers are in both databases because we employ a lot of jackasses...(excuse me)...nice people...who don't follow directions. There are two many dups to go in a manually delete all of them. Lucky for us the software we use has a utility to combine two records.

So I take the scripts I have and run them in the query analyzer...

And what do you mean there is no need to combine them?|||Did you see the select with the three part name?

But then I guess the application needs to look at only 1 database...right?

How many tables are we talking about?

I would dump and restore 1 database, then use DTS to append the data to their "cousin" tables...|||There are 50+ tables.....

What do you mean by dump and restore with cousins?

And yes the app has to only see one database...sqlsql

Tuesday, March 20, 2012

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
If not, what is the best way to accomplish this?
Thanks,
Rodjk #613
In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
-Dave
Rodjk #613 wrote:
> Hello,
> I am working with a database that is in use in several locations.
> The structure of the databases are identical, but the data is
> different.
> I am tasked with combining the data into one large database.
> (It is SQL 2000)
> I have .bak files for each location.
> Is there any way to restore a backup into a database, combining the
> data?
> If not, what is the best way to accomplish this?
> Thanks,
> Rodjk #613
>
-Dave Markle
http://www.markleconsulting.com/blog
|||Dave Markle wrote:
> In a word, no. You should probably restore both databases to a single
> machine, and write a script to combine the data.
> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
> -Dave
> Rodjk #613 wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.
My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.
I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.
Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?
Thanks
Rodjk #613
|||Hi

> Hello,
> Thanks, I kinda figured that about the backups.
> About writing the script, what is involved in that?
> I am pretty good with this one database, but am by no means an expert.
> Luckily, the database is the same version, the same structure at each
> location.
> My thoughts are to add additional blank SQL databases, then populate
> them with the bak files.
> then combine the databases into one large database.
> I understand that a MoveTo is the command, but I have never written one
> and have no idea how to start.
> Any information would be appreciated.
> Am I foolish to hope that since the databases have the same tables and
> structure that this will not be very complicated?
> Thanks
> Rodjk #613
>
You have not said if the combined database will have an extra identifiers to
say which database the data originated from. Also you if you have any
identity columns you will need to decide what you are to do with clashing
identities if they are used as foreign keys.
If there are no foreign keys and the structures are the same then you could
just do
exec sp_msforeachtable 'INSERT INTO ? SELECT * FROM [Otherdb].?'
If there are FKs or identity values you can use
exec sp_msforeachtable 'SELECT ''INSERT INTO ? SELECT * FROM [Otherdb].?'''
to get a script that you can then manipulate so it is in an order that will
satify the FK constraints and you can add SET IDENTITY_INSERT statements.
You may also want to look at http://vyaskn.tripod.com/code.htm#inserts
If you have a very large amount of data you may want to consider using BCP
instead.
John
|||> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
There is a tool to *compare* data - SQL Server Comparison Tool.
Dariusz Dziewialtowski.
|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.
If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.
sqlsql

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
If not, what is the best way to accomplish this?
Thanks,
Rodjk #613In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
-Dave
Rodjk #613 wrote:
> Hello,
> I am working with a database that is in use in several locations.
> The structure of the databases are identical, but the data is
> different.
> I am tasked with combining the data into one large database.
> (It is SQL 2000)
> I have .bak files for each location.
> Is there any way to restore a backup into a database, combining the
> data?
> If not, what is the best way to accomplish this?
> Thanks,
> Rodjk #613
>
-Dave Markle
http://www.markleconsulting.com/blog|||Dave Markle wrote:
> In a word, no. You should probably restore both databases to a single
> machine, and write a script to combine the data.
> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
> -Dave
> Rodjk #613 wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.
My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.
I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.
Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?
Thanks
Rodjk #613|||Hi

> Hello,
> Thanks, I kinda figured that about the backups.
> About writing the script, what is involved in that?
> I am pretty good with this one database, but am by no means an expert.
> Luckily, the database is the same version, the same structure at each
> location.
> My thoughts are to add additional blank SQL databases, then populate
> them with the bak files.
> then combine the databases into one large database.
> I understand that a MoveTo is the command, but I have never written one
> and have no idea how to start.
> Any information would be appreciated.
> Am I foolish to hope that since the databases have the same tables and
> structure that this will not be very complicated?
> Thanks
> Rodjk #613
>
You have not said if the combined database will have an extra identifiers to
say which database the data originated from. Also you if you have any
identity columns you will need to decide what you are to do with clashing
identities if they are used as foreign keys.
If there are no foreign keys and the structures are the same then you could
just do
exec sp_msforeachtable 'INSERT INTO ? SELECT * FROM [Otherdb].?'
If there are FKs or identity values you can use
exec sp_msforeachtable 'SELECT ''INSERT INTO ? SELECT * FROM [Otherdb].?
'''
to get a script that you can then manipulate so it is in an order that will
satify the FK constraints and you can add SET IDENTITY_INSERT statements.
You may also want to look at http://vyaskn.tripod.com/code.htm#inserts
If you have a very large amount of data you may want to consider using BCP
instead.
John|||> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
There is a tool to *compare* data - SQL Server Comparison Tool.
Dariusz Dziewialtowski.|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.
If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)

I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?

If not, what is the best way to accomplish this?

Thanks,
Rodjk #613In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.

There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.

-Dave

Rodjk #613 wrote:

Quote:

Originally Posted by

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
>
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
>
If not, what is the best way to accomplish this?
>
Thanks,
Rodjk #613
>


--
-Dave Markle

http://www.markleconsulting.com/blog|||Dave Markle wrote:

Quote:

Originally Posted by

In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
>
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
>
-Dave
>
Rodjk #613 wrote:

Quote:

Originally Posted by

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)

I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?

If not, what is the best way to accomplish this?

Thanks,
Rodjk #613


>
>
--
-Dave Markle
>
http://www.markleconsulting.com/blog


Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.

My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.

I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.

Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?

Thanks
Rodjk #613|||Rodjk #613 wrote:

Quote:

Originally Posted by

Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?


Yes.

Imagine database A has order no 1, being shipped to customer no 1.
Database B also has an order no 1, being shipped to customer no 1.

But in database A customer no 1 is Mr. Black, in B it is Mr. White.

/jim|||There are tools to compare the data in the two databases (Like Red

Quote:

Originally Posted by

Gate's SQL Data Compare), but AFAIK nothing to combine the data.


There is a tool to *compare* data - SQL Server Comparison Tool.

Dariusz Dziewialtowski.|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.

If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
If not, what is the best way to accomplish this?
Thanks,
Rodjk #613In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
-Dave
Rodjk #613 wrote:
> Hello,
> I am working with a database that is in use in several locations.
> The structure of the databases are identical, but the data is
> different.
> I am tasked with combining the data into one large database.
> (It is SQL 2000)
> I have .bak files for each location.
> Is there any way to restore a backup into a database, combining the
> data?
> If not, what is the best way to accomplish this?
> Thanks,
> Rodjk #613
>
-Dave Markle
http://www.markleconsulting.com/blog|||Dave Markle wrote:
> In a word, no. You should probably restore both databases to a single
> machine, and write a script to combine the data.
> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
> -Dave
> Rodjk #613 wrote:
> > Hello,
> > I am working with a database that is in use in several locations.
> > The structure of the databases are identical, but the data is
> > different.
> > I am tasked with combining the data into one large database.
> > (It is SQL 2000)
> >
> > I have .bak files for each location.
> > Is there any way to restore a backup into a database, combining the
> > data?
> >
> > If not, what is the best way to accomplish this?
> >
> > Thanks,
> > Rodjk #613
> >
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.
My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.
I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.
Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?
Thanks
Rodjk #613|||> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
There is a tool to *compare* data - SQL Server Comparison Tool.
Dariusz Dziewialtowski.|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.
If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.

Combine two database

I have a two databse say DB1 and DB2 in sql server 2005 developer edition.
Both Databases are completely different in structure(i.e nothing common)
Both Databases contains Important Data.
I want's to combine them into a new database named dbcombined.{provided that data remains intact}.
How can I do That?
Then Also I have to deploy that db in other system what I have to do?

If objects are unique across databases, you may just use DTS (Import/Export Wizard) or SSMS to import one database objects to another database.

But, if there are no real reasons, I would't recommend doing this at all.

|||

No There is nothing Common in Between two DataBase.(i.e no common table,storedprocedure,view )

Data of both DB is very important to me.

So what Can I Do?

|||

bcp, dts, ssis or snapshot replication your choice. any will do.

|||

I see in DAAB connection always remains open.Could In any way I can close the connection explicitly.
I am checking through activity manager

Following is my simple code

string connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["AdventureWorksDB"].ConnectionString;

SqlDatabase db = new SqlDatabase(connString);

string sqlCommand = "Select EmployeeID, NationalIDNumber, LoginID, Title from HumanResources.Employee";

DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

DataSet dst = db.ExecuteDataSet(dbCommand);

gridEmployees.DataSource = dst.Tables[0].DefaultView;

gridEmployees.DataBind();

Wednesday, March 7, 2012

Column update structure change

hi
I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server

or any good recomendations of a action to accomplish this in the best and
fastest way.

thanks davepDave,

First of all, SQL Server maximum rowsize = 8,060 bytes. Make sure you keep
under that limit when you add the #bytes from all of the other columns.

Access to the table, and possibly other objects that are dependent on that
table will be blocked until the schema change is complete. Also, other
non-dependent processes will also be slowed down significantly. A million
row table on recent sever technology should not take an excessive amount of
time to alter.

-- Bill

"DaveP" <dvs_bis@.sbcglobal.netwrote in message
news:S3Jyh.59511$wc5.47614@.newssvr25.news.prodigy. net...

Quote:

Originally Posted by

hi
I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server
>
or any good recomendations of a action to accomplish this in the best and
fastest way.
>
>
thanks davep
>
>

|||DaveP (dvs_bis@.sbcglobal.net) writes:

Quote:

Originally Posted by

I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server


This is a metadata change, so it will be about instant.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||AlterEgo (alterego55@.dslextreme.com) writes:

Quote:

Originally Posted by

First of all, SQL Server maximum rowsize = 8,060 bytes. Make sure you
keep under that limit when you add the #bytes from all of the other
columns.


This applies to SQL 2000 only. On SQL 2005 rows can span pages. Not so
that you can have three char(8000) on the same page, but three varchar(8000)
is OK.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks for the responses...
dave

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98D24AF9ACFYazorman@.127.0.0.1...

Quote:

Originally Posted by

DaveP (dvs_bis@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I want to increase a varchar(5000)
>to varchar(8000) on a table that
>has approximately million rows....
>What is the impact on the server


>
This is a metadata change, so it will be about instant.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Friday, February 24, 2012

column info from temp table

Hello,
i have a stored proc where i create two temp tables with complete equal
structure. But the structure is different every time the stored proc is
called. Now i want to find all records in temp table 1 which are not in
temp table 2.
I am not sure if the sorting in both tables is equal. So i fear that i have
to compare one record from one table with all records from the other table
field by field.
How can i do this? Or is there a better solution?
thanks,
HelmutIf you are using SQL Server 2005 you can use except operator to compare
tables.
Regards
Amish Shah|||Hi, Helmut
Temporary tables are stored in tempdb.
You can find the columns of temporary tables like this:
CREATE TABLE #T1 (X INT, Y INT)
SELECT c.name FROM tempdb.dbo.sysobjects o
INNER JOIN tempdb.dbo.syscolumns c ON c.id=o.id
WHERE o.name LIKE '#T1%'
However, if tables with the same name are created by two different
processes at the same time you need to consider also the suffix of the
table, which is different for each process (is based on the "connection
number", i.e. the value of @.@.CONNECTION when the connection is made;
also, a part of the suffix is based on @.@.NESTLEVEL). I do not know any
reliable method of determining this suffix in a live environment
(because the value of @.@.CONNECTION may have been changed since the
beginning of the connection).
Razvan|||Helmut,
As you say, the structure may be different each call bot the tables are
identical, so you may well know the likely PK available.
Apart from that point, it may be useful to know what leads to you populating
two tables in any case. Why can you not take the comparison back one step an
d
attempt the comparison there?
If you want to reveal the basic SProc code here it may well be a lot clearer
to assist you,
Tony
"helmut woess" wrote:

> Hello,
> i have a stored proc where i create two temp tables with complete equal
> structure. But the structure is different every time the stored proc is
> called. Now i want to find all records in temp table 1 which are not in
> temp table 2.
> I am not sure if the sorting in both tables is equal. So i fear that i hav
e
> to compare one record from one table with all records from the other table
> field by field.
> How can i do this? Or is there a better solution?
> thanks,
> Helmut
>|||Am 3 Feb 2006 06:48:52 -0800 schrieb amish:

> If you are using SQL Server 2005 you can use except operator to compare
> tables.
> Regards
> Amish Shah
Arrrrgghhh, i can't use SQL 2005, i am bound to SQL 2000 :-(
But good to know that in future this problem not longer exists.
thanks,
Helmut|||Hi, Razvan
thank you for this info, i will see if i can find out more about it. But it
shed some light in my problem :-)
Helmut|||Am Fri, 3 Feb 2006 07:11:07 -0800 schrieb Tony Scott:
...
> Apart from that point, it may be useful to know what leads to you populati
ng
> two tables in any case. Why can you not take the comparison back one step
and
> attempt the comparison there?
The starting point you can see if you look some entries down, it is the
problem building the Oracle MINUS functionality for SQL Server 2000.
I am short before finishing a first solution and then i will post it here
in hope that it will become much more clearer what i need and maybe some
people can help me to make it more universal, more stable, ... and much
more faster :-)
bye,
Helmut|||"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138979281.680288.12130@.o13g2000cwo.googlegroups.com...
> Hi, Helmut
> Temporary tables are stored in tempdb.
> You can find the columns of temporary tables like this:
> CREATE TABLE #T1 (X INT, Y INT)
> SELECT c.name FROM tempdb.dbo.sysobjects o
> INNER JOIN tempdb.dbo.syscolumns c ON c.id=o.id
> WHERE o.name LIKE '#T1%'
> However, if tables with the same name are created by two different
> processes at the same time you need to consider also the suffix of the
> table, which is different for each process (is based on the "connection
> number", i.e. the value of @.@.CONNECTION when the connection is made;
> also, a part of the suffix is based on @.@.NESTLEVEL). I do not know any
> reliable method of determining this suffix in a live environment
> (because the value of @.@.CONNECTION may have been changed since the
> beginning of the connection).
> Razvan
or you can use object_id() function:
SELECT c.name FROM tempdb.dbo.sysobjects o
INNER JOIN tempdb.dbo.syscolumns c ON c.id=o.id
WHERE o.id=object_id('tempdb..#T1')
dean|||Hi, Dean
You are right. It's much better this way, since it eliminates the
problem of multiple temporary tables with the same name.
Razvan|||Am Fri, 3 Feb 2006 19:28:16 +0100 schrieb Dean:

> SELECT c.name FROM tempdb.dbo.sysobjects o
> INNER JOIN tempdb.dbo.syscolumns c ON c.id=o.id
> WHERE o.id=object_id('tempdb..#T1')
Oh yes, perfect.
thanks,
Helmut

Thursday, February 16, 2012

Column Description

Hello, it seems I forgot how to do it,

I want to create a query to get the column description table, I mean get structure without data.

I remember it was select DESC or something like that, donno if im right!!!

regards

select * from information_schema.columns where table_name='foo'

DESC tells you to sort your results in descending order

--
James

|||

Well, I am not sure about DESC, but I used to work with SQL*Plus in Oracle, and I used to do so, it will give me just the columns and datatypes.

So, thanks a lot

regards

|||There are several ways you can do this.
My preferred method is using one of the INFORMATION_SCHEMA views:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable'

Alternately, you could use the sp_columns stored procedure
EXEC sp_columns 'myTable'
You could also directly query the sysobjects, etc. tables but I don't recommend that approach.
|||

Terri, thanks dear a lot.

Regards

|||

Don't you just love vendor extensionsSmile [:)]

standard SQL uses this format to give you the result set in descending order

SELECT * FROM TITLES ORDER BY PRICE DESC

SQL*Plus uses

DESC[RIBE] table_name to give you column information.

Who says you can't reuse keywords ?

|||

Thanks my friend, I used that once, but as I mentioned I forgot how, and whether its applicable in SQL Server.

regards