Create table help

Hi there,

i'd like to create a table from other tables. My new table will only have two columns. Let's call the new table 'C', and it's created by a Select on 'A' and 'B'

A.control_id and B.account_id

one control can have many accounts, ok. However, I'd like to create 'C' with the control_id and a concatenation of all associated account_id's.

eg:

control_id linked_accounts
----------
1 account_id1, account_id2, account_id3

Any help would be appreciated.

Regards,Have you looked into the "INSERT INTO" statement?|||It's the concatenation that's getting me. I was doing this with a function, but as soon as the report I'm creating references the function, performance goes pear-shaped.

I was doing this...

BEGIN
DECLARE @.RowCnt int
DECLARE @.MaxRows int
DECLARE @.linkedaccounts varchar(256)
DECLARE @.global_control_id int
DECLARE @.accountstr varchar (1000)

SELECT @.RowCnt = 1
SET @.accountstr=''

DECLARE @.Import TABLE ( rownum int IDENTITY (1, 1) Primary key NOT NULL,
Control_id int,global_control_id int, Title varchar(256) )

INSERT INTO @.Import
SELECT DISTINCT dbo.NTH_RPT_CONTROLS.CONTROL_ID, dbo.NTH_RPT_CONTROLS.GLOBAL_CONTROL_ID, dbo.NTH_RPT_ACCOUNT.TITLE
FROM dbo.NTH_RPT_ACCOUNT_CONTROL INNER JOIN dbo.NTH_RPT_ACCOUNT ON dbo.NTH_RPT_ACCOUNT_CONTROL.ACCOUNT_ID=dbo.NTH_RPT _ACCOUNT.ACCOUNT_ID
AND dbo.NTH_RPT_ACCOUNT_CONTROL.LABEL_ID=dbo.NTH_RPT_A CCOUNT.LABEL_ID
INNER JOIN dbo.NTH_RPT_CONTROLS
ON dbo.NTH_RPT_ACCOUNT_CONTROL.CONTROL_ID=dbo.NTH_RPT _CONTROLS.CONTROL_ID
AND dbo.NTH_RPT_ACCOUNT_CONTROL.LABEL_ID=dbo.NTH_RPT_C ONTROLS.LABEL_ID
WHERE dbo.NTH_RPT_CONTROLS.CONTROL_DID = @.control_did

SELECT @.MaxRows=count(*) from @.Import

WHILE @.RowCnt <= @.MaxRows

BEGIN

SELECT @.linkedaccounts = title, @.global_control_id = global_control_id
FROM @.Import
WHERE rownum = @.RowCnt

SELECT @.RowCnt = @.RowCnt + 1
SET @.accountstr = @.accountstr + @.linkedaccounts +', '

END
RETURN @.accountstr
END

-----------
then i created a view...
-----------

CREATE VIEW dbo.VIEW_LINKED_ACCOUNTS
AS
SELECT control_did, dbo.f_linked_accounts(dbo.nth_rpt_controls.control _did) AS account_names
FROM dbo.nth_rpt_controls

-----------

But performance is bad. so i'd like to create a permanent table that is updated by a trigger on the underlying tables.|||Im thinking keeop the function, it's still useful - but create my new table and use a trigger to execute the function and in the function send the concatenated string to the new table.

Create table from text file, extract data, create new table from extracted data.

Hello all,

Please help...

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem...

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.select *
INTO ExtractedDataFile
from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%'|||Thanks so much Brett!

I have more question though......

I will be needing to do the importation of text file & extraction of data at least once a month. Then after I import & extract data I will need to append the extracted data into the table ExtractedDataFile. But I will only need to append the data if there is no duplicate DF_SC_Case_Nbr.

Can all this be done in just one stored procedure? How will I do this?

Thanks again.|||What's a duplicate?

Do you have books online?

Open it up, and leave it open...

Look into bcp

create store procedure to incremental populate full text index tab

I need to create a store procedure to perform incremental population of full
text index table. The store procedure will be call from my program.
Any ideas ?
xxx
This is probably what you want.
exec sp_fulltext_table N'[dbo].[authors]', N'start_incremental'
I suggest you have a look at change tracking and schedule the index updates.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:AEAD8DB8-91F6-484A-AD2C-C6AB06E5B38B@.microsoft.com...
>I need to create a store procedure to perform incremental population of
>full
> text index table. The store procedure will be call from my program.
> Any ideas ?
> --
> xxx

Create Procedure syntax error

Hi,
I'm having a problem where a CREATE PROCEDURE call with syntax error in
it is not being reported when included in a large script.
If I create a script of the individual CREATE PROCEDURE call on it's
own and run it, the syntax error is reported.
I am running the scripts using Query Analyzer, SQL Server 2000. Does
this problem sound familiar to anyone?
Thanks,
NeelDoes it have a GO before and after? Do you get the error if you try to run
the script from the previous create to the next object?
<neelpunna@.hotmail.com> wrote in message
news:1126724981.896558.20590@.z14g2000cwz.googlegroups.com...
> Hi,
> I'm having a problem where a CREATE PROCEDURE call with syntax error in
> it is not being reported when included in a large script.
> If I create a script of the individual CREATE PROCEDURE call on it's
> own and run it, the syntax error is reported.
> I am running the scripts using Query Analyzer, SQL Server 2000. Does
> this problem sound familiar to anyone?
> Thanks,
> Neel
>

create default tables, procedures, etc. on newly connected remote SQL server

I have a website I'm ready to test on the server it will call home. I just got connected to the remote SQL server that it will be using. As I've been creating the site, I've been using the default SQL Express set-up in Visual Studio. Is there a way to have Visual Studio create all those default tables, procedures, etc. OR is there a way to copy all of that stuff from the SQL Express running on my machine to the remote SQL Server 2005?

-Mathminded

There are many ways to move the database, but the best is Backup and Restore because it moves everything including permissions, you Backup the database locally and put the .bak file in the location below in the remote server and use the Backup and Restore wizard SQL Server will create the file path just make sure the path is correct don't try to create the path because SQL Server gets confused if you do. I have created a FAQ that covers all the ways to move a database, if you use another method remember to also move the permissions. Hope this helps.

C:\Program Files\Microsoft SQL Server\MSSQL\Backup

http://forums.asp.net/thread/1454694.aspx|||

Thanks, Caddre. Before I got your message I had just found this link (http://www.c-sharpcorner.com/uploadfile/dsdaf/104012006083052am/1.aspx) and followed those instructions. It created all the tables, views, and stored procedures that had been created on my local SQL Express installation. From what I understand of the Backup and Restore method, I have to be able to load the backup file on the remote server, right? I don't think I have access to do that. I'm planning to call the database administrator tomorrow, anyway, because the Website Administration Tool in VS2005 says I don't have permission to execute 'aspnet_CheckSchemaVersion' so I'm not able to create new users. Crazy. :-)

For future readers of this thread, if you end up using the link in this message, and you have a problem running the tool like I did, you may be interested in this quote fromhttp://quickstarts.asp.net/QuickStartv20/aspnet/doc/management/tools.aspx :

"To configure and install databases on a SQL server to use these ASP.NET features, you can use theaspnet_regsql tool. This tool can be found in the version-specific framework directory, under theMicrosoft.NET\Framework subdirectory of your Windows system folder."

As always, thank you for your help, Caddre.

-Mathminded

|||

FYI for anyone who runs into the problem of not having permission to execute that stored procedure. I used the Microsoft SQL Server Management Studio Express to add my database user to the aspnet_Membership_FullAccess role and that took care of it. I think the backup and restore method that Caddre mentioned probably takes care of problems like that, but unfortunately I didn't have access to load files on the remote server. If you can do it her way, I suggest doing that since she has about a million times more experience than I do (that's probably an understatement . . . it's probably closer to a billion or so). But, if you're in the situation I was in, this did work for me.

Happy SQLing!

-Mathminded

|||

addition to my previous post:

I had to add my database user to ALL of the roles ending in "_FullAccess" in order to fully use the Website Administration Tool.

create data-driven subscription programatically

What is the best/easiest way to create a data-driven subscription
programatically? Can I create a C# console application to call the
ReportingService class?Yes you can - I use Soap to deliver and set reports via the report service.
See the documentation - lots of examples.
=-Chris
"Bucky" <uw_badgers@.mail.com> wrote in message
news:1109188603.308235.11520@.z14g2000cwz.googlegroups.com...
> What is the best/easiest way to create a data-driven subscription
> programatically? Can I create a C# console application to call the
> ReportingService class?
>