Create Table with Unknown Table Name?

Hi, question:

I want to create a cursor that will loop through a table and find all the distinct county names for some address records. Then, it will create a new table with each of these county names as it loops through the cursor pulling each of the records associated with these records.

My question: How do you use the INTO syntax in Microsoft Access to create a new table when you don't know the name of the table you're creating until it finds it in the database?

My code thus far: (untested, so there might be some minor syntax errors)

DECLARE myCursor CURSOR FOR
SELECT DISTINCT CountyName FROM [ALL_RECORDS]

DECLARE @.UniqueCounty

OPEN myCursor

FETCH NEXT FROM myCursor INTO @.UniqueCounty

WHILE (@.@.FETCH_STATUS=0)
BEGIN
SELECT * FROM [ALL_RECORDS] INTO @.UniqueCounty /* <-- HERE IS THE PROBLEM!!! */
FETCH NEXT FROM myCursor INTO @.UniqueCounty
ENDAside from any questions of if or why you want to do this, you will need to use dynamic sql (aka string concatenation) to accomplish your goal.

Example:

create table #ALL_RECORDS (pk int primary key, CountyName varchar(128))
insert into #ALL_RECORDS
values (1,'del_1')
insert into #ALL_RECORDS
values (2,'del_2')
insert into #ALL_RECORDS
values (3,'insertion_attack] from (select ''Gotcha'' as val ) as tab_alias; select * from master.dbo.sysxlogins -- ')
Declare @.sql nvarchar(4000)
DECLARE @.UniqueCounty sysname
DECLARE myCursor CURSOR FOR
SELECT DISTINCT CountyName FROM #ALL_RECORDS

OPEN myCursor
FETCH NEXT FROM myCursor INTO @.UniqueCounty
WHILE (@.@.FETCH_STATUS=0)
BEGIN
set @.sql = '
SELECT * INTO [' + @.UniqueCounty + ']FROM #ALL_RECORDS '
exec (@.sql)
FETCH NEXT FROM myCursor INTO @.UniqueCounty
END

/*
Note that entry 3 in #all_records demonstrates one of the perils of this method, namely that your are executing a string the exact contents of which you do not know, leaving your system vulnerable to an insertion attack.
*/|||Thanks, I will try it.

The WHY is because I need smaller source tables refreshed every night from a new gigantic database that gets refreshed every night.

At least I have a starting point, thanks!sql

Create table using data from another table

Hi,
How could I create a new table dynamically where columns names are data
from another table?
Example:
I have a table "Table1" with one column "T1Col"
The column contains following data:
"Row1"
"Row2"
"Row3"
Now I would like to "read" data from Table1 and create a new table
Table2 which will contain columns "Row1", "Row2" and
"Row3".
Any help will be appreciated.
Thank you in advance.Google for "transpose" or "cross-tab" or search this newsgroup.
ML
http://milambda.blogspot.com/sql

Create script in 2005 without the [] delimiters around the object names

Anyone know how to permanently set up SQL 2005 Server Management Studio to NOT put those silly [ and ] delimiters around object names when using the CREATE SCRIPT to... funtionality from object explorer?

Moving thread to the Tools General forum because they'll be better able to answer your question.

-Jeffrey

Create report items dynamically - how?

Hi, All,
I have a report with a parameter (a combo box with two names), that only
show the CompanyName on the report. When I choose Name No1, I would like to
show 4 textboxes, and choosing the name No2, I would like to show another
one, and hide the pevious four.
I'm going to make these textboxes dynamically.
Can I do it? Or is there another solution?
Thanks,
SzabtiYou can write an Expression in the "Hidden" part of the "Visibility" property
of the fields involved. Let this expression evaluate your parameter and have
it return a boolean value. That's all!
D.P.
"szabti" wrote:
> Hi, All,
> I have a report with a parameter (a combo box with two names), that only
> show the CompanyName on the report. When I choose Name No1, I would like to
> show 4 textboxes, and choosing the name No2, I would like to show another
> one, and hide the pevious four.
> I'm going to make these textboxes dynamically.
> Can I do it? Or is there another solution?
> Thanks,
> Szabti
>
>|||Yes, I've done it at first. But here was a great problem with rendering:
after hiding the first 4 elements and showing the only one, remaining part
of my report (these are textboxes with summary data) was fall apart: some
textboxes moved to top of another and the value became unreadable.
So it is why I've thought to draw these elements dynamically...
szabti
"D.P." <DP@.discussions.microsoft.com> az alábbiakat írta a következõ
hírüzenetben: 896D6245-F152-49CD-9B38-7AFDF961C7C5@.microsoft.com...
> You can write an Expression in the "Hidden" part of the "Visibility"
> property
> of the fields involved. Let this expression evaluate your parameter and
> have
> it return a boolean value. That's all!
> D.P.
> "szabti" wrote:
>> Hi, All,
>> I have a report with a parameter (a combo box with two names), that only
>> show the CompanyName on the report. When I choose Name No1, I would like
>> to
>> show 4 textboxes, and choosing the name No2, I would like to show another
>> one, and hide the pevious four.
>> I'm going to make these textboxes dynamically.
>> Can I do it? Or is there another solution?
>> Thanks,
>> Szabti
>>
>>

Create list of table names and size for a database

Hi there,

I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB

E.g

Table1 1111KB
Table2 123300MB
Table3 120448KB

etc for all the tables in a particukar DB

I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.

can anyone help please!!

From

NewToSQLIf you don't do an UPDATE STATISTICS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_1mpf.asp) you'll probably be dealing with GIGO, but you could use:SELECT
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
, 8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb
, Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb
, so.name
FROM dbo.sysobjects AS so
JOIN dbo.sysindexes AS si
ON (si.id = so.id)
WHERE 'U' = so.type
GROUP BY so.name
ORDER BY so.name-PatP|||Or...

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO

DECLARE @.tablename nvarchar(128)
, @.maxtablename nvarchar(128)
, @.cmd nvarchar(1000)
SELECT @.tablename = ''
, @.maxtablename = MAX(name)
FROM sysobjects
WHERE xtype='u'

WHILE @.tablename < @.maxtablename
BEGIN
SELECT @.tablename = MIN(name)
FROM sysobjects
WHERE xtype='u' and name > @.tablename

SET @.cmd='exec sp_spaceused['+@.tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @.cmd
END

SET NOCOUNT OFF
GO

SELECT * FROM #SpaceUsed
GO

DROP TABLE #SpaceUSed
GO|||I generally use

dbcc updateusage(0)
go

select sum(reserved)*8 as "Size in KB", object_name(id)
from sysindexes
where indid in (0, 1, 255)
group by id
order by 1 desc

The usage statistics tend to decay over time, as Pat pointed out.|||Is that just the index or the index and the datapage?|||Both, and text. The indid is what determines it.
indid = 0 = heap
indid = 1 = clustered index
indid = 255 = text/image

What I need is a way to subtract the nonclustered indexes, in the case that they happen to be on separate filegroups. Then I can get a script together to monitor space usage on a multi-filegroup system.|||If you look at my original posting, the non-clustered indicies are what are reported as index_kb. The data pages are either the heap or the clustered index, and the blob (TEXT and IMAGE) pages are just that, the index_kb are what are left.

-PatP|||I can't be too certain, but I think that M$ drops all of the index pages into the reserved count for indids 0 and 1. For a test, I created a table, loaded a bit of data into it, ran dbcc updateusage(0), and ran both our scripts. Then create an index on the table, run the dbcc again, run both scripts, and see what changes. In the second instance, it looks like the data page count goes up for the table.
This sort of thing just makes me a little more jealous of the Oracle DBA, who can get all of his size statistics with a lot less back-bending.|||Was the index you created clustered, or non-clustered? If it was clustered, then that is exactly the behavior that I would expect. If not, then hmmmm...

-PatP|||Thanks for your help, this worked a treat!!!!|||I'm just curioius, but which suggestion did you end up using?

-PatP|||I used Brett's in the end as it was similar to what I had originally tried to do. because I then put the contents of the table to an excel spreadsheet|||Pat: I added a nonclustered index. The definitions of the columns reserved, used, and dpages in books online are almost infuriating. I don't think there is any way to derrive the number of reserved pages for a particular index on a separate filegroup from the data. I thought about just rounding the dpages value up to the next multiple of 8, but that does not take into account highly fragmented indexes. It may be that you have to run dbcc showcontig to get the actual values, but that is too resource intensive for a simple monitor.

Create index of values from different columns

Hi i have a table with different columns:
columns names: jobA, jobB, jobE....
I want to create and index with all the values(but not duplicates)
contained in all columns.
Can anybody give an idea on how to do this?
ThanksAn index? I guess you want to list all the distinct values in one column.
Like this, maybe?
select jobA
from <table>
union
select jobB
from <table>
union
select jobE
from <table>
union
...
If you wanted something else, next time please explain exactly what you
need. See this:
http://www.aspfaq.com/etiquette.asp?id=5006
ML

Create element names from data in "FOR XML PATH" query?

Hi, all. I am writing a stored procedure to create an XML-formatted export from a relational database. I am succeeding for the most part with "FOR XML PATH" queries, thanks to help from these forums, but I've hit a new issue.

I have a table we'll call "facet", and here is a subset of the table's columns:

- facet_id (nvarchar(10))

- facet_type (nvarchar(3))

- facet_value (nvarchar(255))

Part of the XML schema requires that I list these facets, and the element ID is the facet ID. I need to create this:

<facet_id>facet_value</facet_id>

<facet_id>facet_value</facet_id>

...with, of course, both "facet_id" and "facet_value" populated from the database columns. This part of the extract creates subelements to the facet owners, and there is a lower level that contains subelements to some of the facets.

Is there any way to do this? The only alternative I can see is to create a table function to pivot the "facet" table into a horizontal version of itself, but this is ugly for two reasons: performance and the complications it will create when I have to create the subelements to the facets themselves.

Thanks!

I have a similar need. I have a table-valued function that I want to return XML in which the field name itself is defined by data. In my case, these are phone numbers and I want to query a table and return a list

<PrimaryPhone>444-444-4444</PrimaryPhone>
<HomePhone>555-555-5555</HomePhone>

etc., where the node name is defined in a link table.

Best I can come up with so far is something like:

SELECT
'<' + cpt.DisplayName + 'Phone>'
+ ltrim(rtrim(cp.PhoneNumber))
+ '</' + cpt.DisplayName + 'Phone>' AS "node()"
FROM Customer c
INNER JOIN CustomerPhone cp ON cp.CustomerId = c.CustomerId
INNER JOIN CustomerPhoneType cpt ON cpt.CustomerPhoneTypeId = cp.CustomerPhoneTypeId
WHERE c.CustomerId = @.customerId
FOR XML PATH(''), TYPE

But the special symbols (<, >, etc) are automatically converted to their escaped equivalents so that will not work.

Any advice?|||

cast the string expression to xml should work:

SELECT cast
( '<' + cpt.DisplayName + 'Phone>'
+ ltrim(rtrim(cp.PhoneNumber))
+ '</' + cpt.DisplayName + 'Phone>' as xml) AS "node()"
FROM Customer c
INNER JOIN CustomerPhone cp ON cp.CustomerId = c.CustomerId
INNER JOIN CustomerPhoneType cpt ON cpt.CustomerPhoneTypeId = cp.CustomerPhoneTypeId
WHERE c.CustomerId = @.customerId
FOR XML PATH(''), TYPE