Populating A DB From 2 Tables (Dynamic)

Apr 27, 2005

Hi

I am quite new to the complexities of MS SQL and have a problem, I would like to resolve. I have 2 tables with a unique identifier in both and want to populate a new table with information from both, but the second table I would like to populate just some fields that have a DOB eg

Table 1:
uniqueId
Name
Address

Table2:
uniqueId
Type
Setting

example of content for Table 2:
uniqueId Type Setting
123 DOB 03/04/74
234 TFN 12345678
567 POA Mr Smith

So the new table needs to be populated with a ll of info in table 1 and has a new field called DOB so only the clients with a DOB should populate this field, if the client in Table 1 has a TFN reference, this record should be added to the new table but no value needs to be entered eg

123 Chris Smith 1 high street 03/04/74
234 Jon brown 2 high terrace <Null>

Cheers
pommoz

View 1 Replies


ADVERTISEMENT

Pre-Populating Report Tables

Nov 28, 2007

Hi. I have a report which has several datasources which require a table to be populated before they read from it.
i.e. The first thing that needs to happen whenever the report is run, is a call to a stored procedure which populates the table the report datasources are based off of. The SP takes several minutes to complete and MUST complete before any of the datasources fetch their data.

How can this be achieved?

I can not find anything in the Visual Studio Report Designer which allows to me to instruct Datasource B to not execute before Datasource A has completed (or any other way to call a data population SP, before the data reader SP's execute).

Thanks.

View 2 Replies View Related

Populating 2 Tables At A Time

Oct 19, 2007



Hi,


I have a table in Sql 2005 called

Customers
CustomerId
CustomerName
CustomerAge
CustomerRank
CustomerStCode


I have to transfer the records into 2 tables


CustomerMaster
CustomerId
CustomerStCode


CustomerDetails
CustomerId
CustomerName
CustomerAge
CustomerRank


I have to pick up a row from Customers and transfer it to CustomerMaster and CustomerDetails. CustomerId of CustomerMaster will be the CustomerId of CustomerDetails while transfer. Similarly for all other rows in Customers.


How to do this?

thanks

View 5 Replies View Related

Automating Populating Tables At A Certain Time.

Apr 21, 2000

Hi,

I am new to SQL Server7. I need to populate some tables from an SQL Server7
database at the end of the day. How can I automate this process?
I also need to export these populated tables to a text file on daily basis.
I know I can use "DTS" to do this. But is there any way to make these
automated also? Or is there any third party tool to do all these?

Thanks in advance.

Mkhan

View 1 Replies View Related

Populating 2 Tables Using Flat File

Dec 12, 2005

Hi!

I'm trying to setup a DTS that reads a flat file uses a Data Driven Query task and then selects ONLY records that does not exist in the database and then INSERT them to DB1.

This works fine but I need to add another functionality.

I need to create a record on another table(DB2) based on the freshly inserted records in DB1 using only some of the fields. How do I do it?

Is setting up a trigger possible so that everytime a record is inerted in DB1 it will automatically a populate DB2?

Flat file:
ID
Name
Phone

DB1
ID
Name
Phone

DB2
ID
PHone
Event (from 00 to 10)
NumActions (initialized to 0)

Please help.

Thanks.


$3.99/yr .COM!
http://www.greatdomains4less.com

View 2 Replies View Related

Populating DataGridView With Data From Two Tables?

Oct 11, 2007

I am sorry for asking such a broad question, but I have been working on this and from what I can gather it can be done. My problem is that much of it has gone right over my head and I am getting more confused the more I read... I'm really, really confused...

Basically, I have a dataGridView that is populated with a number of fields from Table1 (ID, NameID, Status, Phone, Notes). This works fine, BUT I would like to access Table2 and have, where ID in Table2 = NameID in Table1, it load the First Name & Last Name into the dataGridView. I am able to load the information from Table 2 like so: SELECT NameFirst + ' ' + NameLast from Table2", but I can't get both Tables to work correctly.

I would like the dataGridView to be layed out like this:

ID NameID Name (NameFirst + NameLast) Status Phone Notes

I can't for the life of me understand or get this to work (Or for that matter even understand what I am trying to do...

Also, I am using Access 2007.

I would greatly appreciate some help, and possibly some explanation in laymans terms so that I might be able to understand this. I have read a lot about this, but for whatever reason it is just soooooo over my head that I can't follow it whatsoever.

Here is the code as it stands now:

//Populate the DataGridView
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"DB.accdb;Jet OLEDBatabase Password=MyPassword;";

// create and open the connection
OleDbConnection conn = new OleDbConnection(conString);
OleDbCommand command = new OleDbCommand();
command = conn.CreateCommand();

// create the DataSet
DataSet ds = new DataSet();

// run the query
command.CommandText = "SELECT ID AS [#], NameID AS [Name], Status AS [Status], Phone AS [Phone], Notes AS [Notes] FROM Table1 WHERE ID = " + textBox13.Text + ";";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter = new OleDbDataAdapter(command);
adapter.Fill(ds);

// close the connection
conn.Close();

bindingSource1.DataSource = ds.Tables[0];

dataGridView1.DataSource = bindingSource1;

// set the size of the dataGridView Columns
this.dataGridView1.Columns[0].Width = 10;
this.dataGridView1.Columns[1].Width = 100;
this.dataGridView1.Columns[2].Width = 100;
this.dataGridView1.Columns[3].Width = 100;
this.dataGridView1.Columns[4].Width = 176;

Any help and information is greatly appreciated.

Thanks Again,

View 5 Replies View Related

Populating Existing Tables With Excel File

Aug 12, 2005

I need to populate tables in my MS SQL 2000 DB with content from an excel file. I am not sure how this is done or how to format the excel file. If someone could help me with this it would be much appreciated!Thanks!

View 3 Replies View Related

Beginner: Trouble Creating And Populating Tables

Oct 16, 2007

Hey guys, I'm an old DevShed member, but my old account isn't working for some reason, so I had to recreate..

I've recently decided to learn MS SQL, and having some trouble with creating and populating tables. Using MS SQL Express 2005.

Heres the code, I keep reading through my notes on how to do it, but I cant see what I'm doing wrong. This is my first attempt at it, so there may be more wrong that I think.


Code:


drop table Property_rental;
drop table Property_type;
drop table Property_owner;
drop table Staff;
drop table Tenant;
drop table Tenant_category;

create table Tenant_category
(TCATID SMALLINT PRIMARY KEY NOT NULL,
TTYPE NVARCHAR(15))
;
create table Property_type
(PTYPEID SMALLINT PRIMARY KEY NOT NULL,
PTYPE NVARCHAR(20) NULL)
;
create table Property_owner
(POWNERID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20) NULL,
SNAME NVARCHAR(20) NULL,
CONTACT NVARCHAR(15) NULL,
ADDR NVARCHAR(50) NULL)
;
create table Staff
(STAFFID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20))
;
create table Property_rental
(ID SMALLINT PRIMARY KEY NOT NULL,
PTYPEID SMALLINT NOT NULL,
STAFFID SMALLINT NOT NULL,
POWNERID SMALLINT NOT NULL,
CONSTRAINT Prop_Type_fk FOREIGN KEY(PTYPEID) REFERENCES Property_type(PTYPEID),
CONSTRAINT Prop_Staff_fk FOREIGN KEY(STAFFID) REFERENCES Staff(STAFFID),
CONSTRAINT Prop_Owner_fk FOREIGN KEY(POWNERID) REFERENCES Property_owner(POWNERID))
;
create table Tenant
(TENANTID SMALLINT NOT NULL,
TCATID SMALLINT NOT NULL,
ID SMALLINT NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20),
COMMENTS NVARCHAR(20),
CONSTRAINT Ten_Cat_fk FOREIGN KEY(TCATID) REFERENCES Tenant_category(TCATID),
CONSTRAINT Ten_Prop_fk FOREIGN KEY(ID) REFERENCES Property_rental(ID),
CONSTRAINT Ten_pk PRIMARY KEY (TENANTID, TCATID, ID))
;




Error messages I'm getting;


Code:


Msg 547, Level 16, State 0, Line 55
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 56
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 57
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 58
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 59
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 60
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 61
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 82
The INSERT statement conflicted with the FOREIGN KEY constraint "Prop_Staff_fk". The conflict occurred in database "master", table "dbo.Staff", column 'STAFFID'.
The statement has been terminated.

View 6 Replies View Related

Test - Populating Tables With Dummy Data

Aug 24, 2006

In 2000, BCP seemed the way to go. DTS packages would also work. My question is, in 2005, what is the best choice? I seem to remember that BCP ignored all referential integrity constraints, and applying them afterwords was a royal pain. I'm not a BCP expert by any means. Running this at the command line means using the DOS prompt correct?

What is 2005's answer to this?

View 4 Replies View Related

T-SQL (SS2K8) :: Populating Tables With Entries From Windows Folders?

May 18, 2015

I have 14 Windows folders containing a mix of Word and PDF documents. Each folder contains up to 500,000 files and these documents are the source for a document management system.

I need to create an audit table which can take the file names and date modified for every document in each folder but I want to avoid having to do a DOS command like dir *.* > filenames.txt then importing as a text file 14 times. Is there a way of automating this in T-SQL?

Each Windows folder is named by year e.g. 2002Docs, 2003Docs, 2004Docs etc.

Documents within the folders are named like this - 20020401_doc1.doc, 20020401_doc2, 20020401_doc678.pdf etc.

View 9 Replies View Related

Fact Table With 3 Keys From Dimension Tables - Avoid Populating NULLs

Jun 10, 2014

I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..

View 3 Replies View Related

Data Warehousing :: Populating Fact Tables With Surrogate Key From Dimension Table?

Sep 11, 2015

How do I correctly populate a fact table with the surrogate key from the dimension table?

View 4 Replies View Related

Dynamic Tables Names And Temporary Tables Options

Oct 5, 2007

Firstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks

View 2 Replies View Related

Dynamic Tables.

Apr 30, 2004

hello everyone,

Thank you Darrell for your help on my "Firehose" problem.

Well, I have another question for everyone. I have three tables with the same columns: Products, Products_Sold, and Products_StandBy. I have a Stored Procedures that gets the products: Products_Get, Products_Sold_Get, and Products_StandBy_Get. I want to be able to only have one stored procedure that gets from all tables depending on what @Table variable I give it.

I tried the following, but it didn't work:


CREATE Procedure CMRC_Products_Get
@Table
AS
CREATE TABLE #TempTable
(
ProductID int,
Chest nvarchar (50),
ItemSize nvarchar (50)
)

INSERT INTO #TempTable
(
ProductID,
Chest,
ItemSize
)
SELECT
ProductID,
@Table.Chest,
CMRC_WomensSizes.ItemSize
FROM
@Table INNER JOIN
CMRC_WomensSizes ON @Table.Chest = CMRC_WomensSizes.Chest


I get an error message on the line: "@Table.Chest,"

So, my question would now be, how do I go about doing what I'm attempting here? Anybody? Thanks ahead for any help.

Sincerely,


Alec

View 1 Replies View Related

Dynamic Tables???

Jun 20, 2008

hi all.

i just want to know if i can create a dynamic table. i mean a table which expands itself based on the requirement.

or a table can only be static???





Cheers!!!
Bel.

View 5 Replies View Related

How To Access Tables From A Dynamic

Jun 9, 2004

I have a procedure that takes database name and queries some tables in the database specified. I am trying to access tables like this:

SELECT COUNT(id) FROM @dbname..sysobjects WHERE type='U'

How can I access the tables while the database name is in a variable.

Thanx.

View 2 Replies View Related

Static And Dynamic Tables

Sep 26, 2006

dear experts,

i heard in a session, that the tables which are modifying continuously are known as dynamic tables.and which are not are known as static tables.

my question is how to find the statics to judge static and dynamic tables?

View 4 Replies View Related

Ad-hoc Dynamic Query With Multiple Tables

Feb 8, 2005

Hi:
I'm trying to create an ad-hoc query on a Asp.net page for user. Besides the usual Boolean operators, Field Names, Comparison operators & Field Values, the ad-hoc query also involves multiple tables, eg [Customers] , [Members] & [Orders].

Now I have difficulties on writing a TSQL sp on how to take the dynamic query with different tables under consideration. User might simply query each individual tables (eg, Customers with age > 25) or combination of tables (eg, Membered Customers with Orders Amt > 1000 between 1/1/2005 - 1/31/2005)

I have look up a lot dynamic query on the net but all are with only 1 single table to hit. Could anyone give me a direction on how to write a dynamic query script with multiple tables under consideration? Much appreciated.

ps: The ad-hoc query only contains these defined tables, no other table will be involved.

View 3 Replies View Related

SQL 2012 :: How To Do Dynamic Audit For The Tables

Jul 21, 2014

I would like to do a dynamic audit for the tables ( columns which needs to audited will also change dynamically). For example,

I am having 3 tables, table1 table2 table3

User can select Table 1 ( col 1, col3, col4) and Table2 ( col 3 and col 5).

I want to achieve this functionality without any triggers or output clause, so only one option which is left is "ChangeDataCapture" ( to my knowledge). So i did a small POC. The problem here is, system captures all the field data even though value doesn't change, for example,

TableA

CountryId CountryCode CountryName
1 IND INDIA
Update TableA
Set CountryCode = 'INDI', CountryName = 'INDIA'
Where CountryId = 1

On running the above query, CDC stores the value for both the columns, but i don't want this. I only wanted to store the CountryCode value alone.

CREATE TABLE [dbo].tCountry(
CountryId bigint IDENTITY(1,1) NOT NULL Primary Key,
CountryName [varchar](50) NULL,
CountryCode [varchar](50) NULL,
CreateBy bigint
)
EXEC sys.sp_cdc_enable_db

[code]....

--See here, i want to display CountryName as NULL, because i didnt do any changes in this column

--Why am i asking this bcoz, Frm my front end application, i will send the complete list of columns and the values to the update query.

Select * from cdc.dbo_tCountry_CT Where __$operation in (3,4)

View 3 Replies View Related

Dynamic Sql - How To Use 'if Exists' With Variable Tables..?

Jul 20, 2005

Hi allIn the SP below im (trying to) do some dynamic sql. As you can see the tableto use is set as a variable and the 'exec' method used to run thesqlstatements.My problem is that the 'if exists' method is not doing what i was hoping itcould do.The @presql command returns somewhere between 0 or 50 rows (give and take) -i just want the 'if exists' part to determine if the select statementreturns something or not since i then will have to update a current row - orinsert a new one.Even if there is no rows returned, the 'if exists' command will return true:-/Any suggestions to a different way of approach...?Thanks in advance :-)######## Stored procedure start ########[various @ variables]....declare @presql varchar(200)select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+cast(@SiteID as varchar(6))+''IF exists((@presql))BEGINdeclare @UpdateSQL varchar(400)set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate asvarchar(12))+''''exec(@UpdateSQL)ENDELSEBEGINdeclare @InsertSQL varchar(2000)select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '(btDate,btTime)VALUES('''+ cast(@FileDate as varchar(12)) + ''','+ cast(@ImportTime as varchar(6)) + ')'EXEC(@InsertSQL)END######## Stored procedure end ########

View 2 Replies View Related

SSIS DYnamic Temp Tables

Nov 27, 2006

Hi,

I have a database with serveral tables, for example 'customer', I want to update this table with a SSIS package. However, to ensure we don't have issues if the update fails then I've put in an intermediate stage

Using an Execute SQL Task I create temporary tables, for example 'customer_tmp'. Data is then imported into these tables. When all the data is imported successfully the original tables are dropped and the temporary tables are renamed, removing the '_tmp'

This works fine and I'm happy with it. However, if someone adds a column to one of the tables in SQL server it is lost on the next upload.

Similarly I have to hard code creating the indexes into the package as well.

Does anyone know how I could copy the original table definitions and create the temporary tables dynamically. So that any new columns would be picked up?

And indeed is it possible to copy the indexes from one table to another before the drop and rename trick?

Thanks in advance.

Iain

View 4 Replies View Related

Cannot Update 1 Of 2 Tables - Dynamic SQL Generation Is Not Supported

Jan 14, 2008

Hi, I'm having a problem with my code,I am doing the following - retrieving a field from a table, which is linked to another table, i'm only updating one of the fields in one of the two tables...
Code: ( text )

View 1 Replies View Related

Has Any One Know How To Create Temporary Tables Inside Dynamic SQL?.

Jul 28, 2000

it seems like i am able to create it. But when i try to access that
temporary table, i get an error "Invalid object".
this is happening only when i try to create local temporary table.
Global temporary table works fine inside the dynamic SQL.

Any Help appreciated.

View 1 Replies View Related

Dynamic List Of Tables In Integration Services

Apr 27, 2006

I need to report on data from several databases on several servers. They are all SQL Server 2005 databases. I am trying to created an Integration Services task to consolidate and transform this data for easy reporting. The problem I am having is one database in particular. It has tables like this:

tblLookupData_Customer1
tblLookupParseData_Customer1
tblLookupData_Customer2
tblLookupParseData_Customer2
tblLookupData_Customer3
tblLookupParseData_Customer3

I want to use only the tables of the form "tblLookupParseData*" for this list. I can do this in Stored Procedures by dynmacally building up the sql query. I cannot find out how to do this in Integration Services. When I make Datasource Views, they seem to expect me to pick from a list of known tables. This list of tables grows as Customers are added to the system.

NOTE: The way the tables are structured was NOT my idea. I hate storing "data" in the structure of the database. Many people also do this when they create "period" tables such as "CustomerData_05_2005". It speeds up writing the data, and querying a specific table, but it is a nightmare for reporting. I cannot change this as it is not in my responsibility.

View 1 Replies View Related

Creating /accessing Tables With Dynamic Names

May 17, 2004

Hi
I have developed an application in ASP/SQL server 7.
Ths system is single user. One of the tables is updated by usr actions( say Table A).
To make it multi user. I want to create table such as A_Username.
How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username .
Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option?
pls suggest

View 6 Replies View Related

Dynamic Pivot Table With Multiple Tables

May 19, 2015

How to pass dynamic values in xml path query?

WITH TEST AS (
SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID,
SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG,
T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI  
INNER JOIN TERMINALGROUP TG  ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TL  ON TG.GROUP_ID = TL.GROUP_ID

[Code] ....

I need to pass dynamic values in FOR SCHEDULER_ID COLUMN. Because I have huge data.

View 7 Replies View Related

SQL Server 2012 :: Join Two Dynamic Pivot Tables

Dec 11, 2013

I have two dynamic pivot tables that I need to join. The problem I'm running into is that at execution, one is ~7500 characters and the other is ~7000 characters.

I can't set them both up as CTEs and query, the statement gets truncated.

I can't use a temp table because those get dropped when the query finishes.

I can't use a real table because the insert statement gets truncated.

Do I have any other good options, or am I in Spacklesville?

View 7 Replies View Related

SQL Server 2008 :: Dynamic Dataset And Tables In SSRS

Jul 27, 2015

Need to change a Excel report to SSRS.

Excel report has around 15 tables all with different columns.

Is there a way , I can show all data in SSRS by avoiding creation of 15 datasets and 15 tables.

Note -- All 15 tables have differnt columns list.

View 3 Replies View Related

Including Single Quotes In Dynamic Pivot Tables?

Feb 26, 2015

I have this pivot table (I only post the static version as the problem only regards the single quotes)

SELECT * from(
select DATEPART(year,DeliverydatePackingSlip) as Year,
CASE WHEN DiffPromiseDateFirst < 0 Then '1 - too early'
WHEN DiffPromiseDateFirst = 0 Then '2 - on time'
ELSE '3 - too late' END as Delivery
from iq4bisprocess.FactOTDCustomer
WHERE OTD_Exclusion = 0)a
PIVOT ( COUNT(Year)
For Year

in ([2012],[2013],[2014],[2015])) as pvtNow, packing everything in a string parameter I always stumble over the single quotes. I tried to replace them with CHAR(39), I tried to define a parameter for each occurrence, but always get a syntax error. What am I doing wrong?declare @sql nvarchar(max)

declare @title1 nvarchar(20)
declare @title2 nvarchar(20)
declare @title3 nvarchar(20)
set @title1 = '1 - too early'
set @title2 = '2 - on time'
set @title3 = '3 - too late'

[Code] .....

exec sp_executesql @sqlThis would throw:Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'early'.

View 2 Replies View Related

System Tables, Dynamic Management Views: Confused

Feb 4, 2008

As im diving into my new DBA role and reading as much as I can, I am a little confused on DMV's and system tables.
I've been reading through the book "SQL Server 2005 Bible", which has been very helpful. I tend to use it in conjunction with BOL whenever I come across something I want to learn more.

Last Friday, I tinkered around with DMV's, which was really cool, but I ran into something today that confused me.

Basically, it was finding out the recovery model for all the DB's on the server. The code in the book was:


SELECT [name], recovery_model_desc
FROM sys.databases;


Which turned exactly what it says it will.

however, I am confused.
I initially thought I needed to specify something in the "[name]" section, but realized, that is not the case.
My question is, why is that?
How do I know when to use [] around something?

I found this article:

http://www.databasejournal.com/features/mssql/article.php/3587906

I have been reading it.
I guess I am just really young and raw to T-SQL to know when to use the language corectly.

Is it due to the fact that the rules or syntax is a little different when using system tables?

Hope that makes sense.

Thanks.

TCG

View 5 Replies View Related

[SQL2k5] Dynamic SQL Query Select On All User Tables

Jul 17, 2006

In one query, I would like to query every user table in a specified database for

SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC

How is this possible?

Please give examples, I am a beginner.

Assume every table has the same structure for columns event count, target ip, and time.


View 3 Replies View Related

Dynamic Creation Of Temp Tables Using Managed Code

Jan 23, 2008



Hi,
I have a requirement to create #Temp table in database and insert values to it.

I use following code:

DbCommand dbCreateTable;

dbCreateTable = provider.CreateCommand();

dbCreateTable.Connection = conn;

dbCreateTable.CommandText ="Create table #MyTemp (Id varchar(10))";

dbCreateTable.ExecuteNonQuery();

string[] insertValues = {"Insert into #Mytemp values ('TestString1')",

"Insert into #Mytemp values ('TestString2')"};

DbCommand dbInsertData = provider.CreateCommand();

dbInsertData.Connection = conn;

foreach (String insertStr in insertValues)

{

dbInsertData.CommandText = insertStr;

dbInsertData.ExecuteNonQuery();

}

Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found".
Reason can be Create and Insert statement gets executed as 2 different sessions.
How to get the above requirement work fine?
Thank you.
HV

View 4 Replies View Related

Integration Services :: Creating Dynamic Server Tables Through SSIS As Per XML Data Files Metadata

Feb 15, 2011

I have a scenario, need to create SQL server Tables dynamically.

I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.

Hence the approach is that,

1. Pick first file from that location
2. Create a table according to that xml data file metada
3.  load data on newly created table.  
4. Pickup the next xml data files.
5. loop through, till the XML data files are exists on that location.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved