Create table,fields come from csv file

I want to create a table automatically,and fields come from a csv file

any idea? TIA

One way is using two packages and a configuration file.

I'm assuming that you are constructing the name of the table on the fly. Note that the table MUST always have the same format.

First create a sample of what you want your table to look like.

Package 1:
Create an SSIS package that loads data from a CSV file into that table.
Make the tablename come from a variable.
Put the variable in a configuration table

Package 2:
Create a variable that will contain the table name.
Create a variable expressions that has the sql to create the table using the variable previously defined.
Create a variable expression that has the sql to update the variable in the configuration table from Package 1
Create a SQL Task to create the table
Create a SQL Task to update the configuration table variable
Create an Execute SSIS package task to execute Package 1

Similarly you can extend this to the source by scanning directories for files and loading different CSV files into different tables.

Hope this helps,

Larry

Create table, concatenate data

I am trying to concatenate four fields from one table: org.address1,
org.city, org.state, org.zip with at least four fields from another table:
url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
would fire whenever a new record was created. The end stored result would be
an address used for Yahoo maps and would look like this:
http://maps.yahoo.com/py/maps.py?Pyt...et+Map=Get+Map
The data in the url table would have this data in it and really should just
be one row used over and over again.
url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=
url.plus_sign = +
url.http_tail = &csz=
url.http_end = &Get+Map=Get+Map
I've come at this from a few different angles but I'm afraid my skills are
not up to what I am looking for; I would appreciate anyone's input.
I dont't unserstand the "plus_sign" thing, but I think you want something
like this. Since the tables are not related, you cannot really join on them
and you most likely want to store the "http" values in local variables:
DECLARE @.start varchar (100)
DECLARE @.plus char(1)
DECLARE @.tail varchar(100)
DECLARE @.end varchar(100)
SELECT @.start = http_start, @.plus = plus_sign, @.tail = http_tail, @.end =
http_end
FROM HTTP_TABLE
SELECT@.start + org.address1 + @.tail + org.city + @.plus + org.state + @.plus +
@.plus + org.zip + @.end
FROM ORG_TABLE org
Like I said, not sure what you wer going for with the "plus_sign" column,
but it looks like that might get you going on it
HTH,
John Scragg
"Steve1445" wrote:

> I am trying to concatenate four fields from one table: org.address1,
> org.city, org.state, org.zip with at least four fields from another table:
> url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
> would fire whenever a new record was created. The end stored result would be
> an address used for Yahoo maps and would look like this:
> http://maps.yahoo.com/py/maps.py?Pyt...et+Map=Get+Map
> The data in the url table would have this data in it and really should just
> be one row used over and over again.
> url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=
> url.plus_sign = +
> url.http_tail = &csz=
> url.http_end = &Get+Map=Get+Map
> I've come at this from a few different angles but I'm afraid my skills are
> not up to what I am looking for; I would appreciate anyone's input.
>
|||Thanks for your reply, I was having troubles with the + sign in the final
concatenate being seen as something other than just what it is, a plus sign.
So I figured I would just store the actual plus sign in the plus_sign field
to use in the final concatenation. Thanks for your help. Steve
"John Scragg" wrote:
[vbcol=seagreen]
> I dont't unserstand the "plus_sign" thing, but I think you want something
> like this. Since the tables are not related, you cannot really join on them
> and you most likely want to store the "http" values in local variables:
> DECLARE @.start varchar (100)
> DECLARE @.plus char(1)
> DECLARE @.tail varchar(100)
> DECLARE @.end varchar(100)
> SELECT @.start = http_start, @.plus = plus_sign, @.tail = http_tail, @.end =
> http_end
> FROM HTTP_TABLE
> SELECT@.start + org.address1 + @.tail + org.city + @.plus + org.state + @.plus +
> @.plus + org.zip + @.end
> FROM ORG_TABLE org
> Like I said, not sure what you wer going for with the "plus_sign" column,
> but it looks like that might get you going on it
> HTH,
> John Scragg
> "Steve1445" wrote:

Create table, concatenate data

I am trying to concatenate four fields from one table: org.address1,
org.city, org.state, org.zip with at least four fields from another table:
url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
would fire whenever a new record was created. The end stored result would b
e
an address used for Yahoo maps and would look like this:
http://maps.yahoo.com/py/maps.py?Py...Get+Map=Get+Map
The data in the url table would have this data in it and really should just
be one row used over and over again.
url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=
url.plus_sign = +
url.http_tail = &csz=
url.http_end = &Get+Map=Get+Map
I've come at this from a few different angles but I'm afraid my skills are
not up to what I am looking for; I would appreciate anyone's input.I dont't unserstand the "plus_sign" thing, but I think you want something
like this. Since the tables are not related, you cannot really join on them
and you most likely want to store the "http" values in local variables:
DECLARE @.start varchar (100)
DECLARE @.plus char(1)
DECLARE @.tail varchar(100)
DECLARE @.end varchar(100)
SELECT @.start = http_start, @.plus = plus_sign, @.tail = http_tail, @.end =
http_end
FROM HTTP_TABLE
SELECT@.start + org.address1 + @.tail + org.city + @.plus + org.state + @.plus +
@.plus + org.zip + @.end
FROM ORG_TABLE org
Like I said, not sure what you wer going for with the "plus_sign" column,
but it looks like that might get you going on it
HTH,
John Scragg
"Steve1445" wrote:

> I am trying to concatenate four fields from one table: org.address1,
> org.city, org.state, org.zip with at least four fields from another table:
> url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
> would fire whenever a new record was created. The end stored result would
be
> an address used for Yahoo maps and would look like this:
> http://maps.yahoo.com/py/maps.py?Py...Get+Map=Get+Map
> The data in the url table would have this data in it and really should jus
t
> be one row used over and over again.
> url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=
> url.plus_sign = +
> url.http_tail = &csz=
> url.http_end = &Get+Map=Get+Map
> I've come at this from a few different angles but I'm afraid my skills are
> not up to what I am looking for; I would appreciate anyone's input.
>|||Thanks for your reply, I was having troubles with the + sign in the final
concatenate being seen as something other than just what it is, a plus sign.
So I figured I would just store the actual plus sign in the plus_sign field
to use in the final concatenation. Thanks for your help. Steve
"John Scragg" wrote:
[vbcol=seagreen]
> I dont't unserstand the "plus_sign" thing, but I think you want something
> like this. Since the tables are not related, you cannot really join on the
m
> and you most likely want to store the "http" values in local variables:
> DECLARE @.start varchar (100)
> DECLARE @.plus char(1)
> DECLARE @.tail varchar(100)
> DECLARE @.end varchar(100)
> SELECT @.start = http_start, @.plus = plus_sign, @.tail = http_tail, @.end =
> http_end
> FROM HTTP_TABLE
> SELECT@.start + org.address1 + @.tail + org.city + @.plus + org.state + @.plus
+
> @.plus + org.zip + @.end
> FROM ORG_TABLE org
> Like I said, not sure what you wer going for with the "plus_sign" column,
> but it looks like that might get you going on it
> HTH,
> John Scragg
> "Steve1445" wrote:
>

Create table, concatenate data

I am trying to concatenate four fields from one table: org.address1,
org.city, org.state, org.zip with at least four fields from another table:
url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
would fire whenever a new record was created. The end stored result would be
an address used for Yahoo maps and would look like this:
http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=123+Main+Street&csz=Anywhere+KA++09186&Get+Map=Get+Map
The data in the url table would have this data in it and really should just
be one row used over and over again.
url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr= url.plus_sign = +
url.http_tail = &csz= url.http_end = &Get+Map=Get+Map
I've come at this from a few different angles but I'm afraid my skills are
not up to what I am looking for; I would appreciate anyone's input.I dont't unserstand the "plus_sign" thing, but I think you want something
like this. Since the tables are not related, you cannot really join on them
and you most likely want to store the "http" values in local variables:
DECLARE @.start varchar (100)
DECLARE @.plus char(1)
DECLARE @.tail varchar(100)
DECLARE @.end varchar(100)
SELECT @.start = http_start, @.plus = plus_sign, @.tail = http_tail, @.end =http_end
FROM HTTP_TABLE
SELECT@.start + org.address1 + @.tail + org.city + @.plus + org.state + @.plus +
@.plus + org.zip + @.end
FROM ORG_TABLE org
Like I said, not sure what you wer going for with the "plus_sign" column,
but it looks like that might get you going on it :)
HTH,
John Scragg
"Steve1445" wrote:
> I am trying to concatenate four fields from one table: org.address1,
> org.city, org.state, org.zip with at least four fields from another table:
> url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
> would fire whenever a new record was created. The end stored result would be
> an address used for Yahoo maps and would look like this:
> http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=123+Main+Street&csz=Anywhere+KA++09186&Get+Map=Get+Map
> The data in the url table would have this data in it and really should just
> be one row used over and over again.
> url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=
> url.plus_sign = +
> url.http_tail = &csz=> url.http_end = &Get+Map=Get+Map
> I've come at this from a few different angles but I'm afraid my skills are
> not up to what I am looking for; I would appreciate anyone's input.
>|||Thanks for your reply, I was having troubles with the + sign in the final
concatenate being seen as something other than just what it is, a plus sign.
So I figured I would just store the actual plus sign in the plus_sign field
to use in the final concatenation. Thanks for your help. Steve
"John Scragg" wrote:
> I dont't unserstand the "plus_sign" thing, but I think you want something
> like this. Since the tables are not related, you cannot really join on them
> and you most likely want to store the "http" values in local variables:
> DECLARE @.start varchar (100)
> DECLARE @.plus char(1)
> DECLARE @.tail varchar(100)
> DECLARE @.end varchar(100)
> SELECT @.start = http_start, @.plus = plus_sign, @.tail = http_tail, @.end => http_end
> FROM HTTP_TABLE
> SELECT@.start + org.address1 + @.tail + org.city + @.plus + org.state + @.plus +
> @.plus + org.zip + @.end
> FROM ORG_TABLE org
> Like I said, not sure what you wer going for with the "plus_sign" column,
> but it looks like that might get you going on it :)
> HTH,
> John Scragg
> "Steve1445" wrote:
> > I am trying to concatenate four fields from one table: org.address1,
> > org.city, org.state, org.zip with at least four fields from another table:
> > url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
> > would fire whenever a new record was created. The end stored result would be
> > an address used for Yahoo maps and would look like this:
> >
> > http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=123+Main+Street&csz=Anywhere+KA++09186&Get+Map=Get+Map
> >
> > The data in the url table would have this data in it and really should just
> > be one row used over and over again.
> >
> > url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=
> > url.plus_sign = +
> > url.http_tail = &csz=> > url.http_end = &Get+Map=Get+Map
> >
> > I've come at this from a few different angles but I'm afraid my skills are
> > not up to what I am looking for; I would appreciate anyone's input.
> >

create table and fields

I have maked this little code but dont know the way to insertt new table and fileds in database DATABASE01...

Private Sub Command1_Click()

Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.ConnectionString = "driver={SQL Server};server=DVD377-14D9E48C\SQLEXPRESS;uid=;pwd=;database=master"
objConn.Open
objConn.Execute "CREATE DATABASE DATABASE01"

''''
'"CREATE table Mytable"
''''

''''
'"CREATE filed1" in text format
'"CREATE filed2" in text format
'"CREATE filed3" in text format
'"CREATE filed4" in date format(DD/MM/YYYY)

''''

objConn.Close

End Subu should not be creating a database from a front end code. however, to create tables open another connection object by another connection string using the newly created database name. and then use the "Create Table" SQL. Check BOL for syntax etc.

CREATE TABLE - two fields combine to make the primary key

Perhaps I'm dating myself.
In a table, I want to create a primary key that consists
of two columns (patrolId and incidentId). From the
documentation I have, it looks like you can only have one
field that is a primary key.
Can you help?
hi Ed,
Ed H wrote:
> Perhaps I'm dating myself.
> In a table, I want to create a primary key that consists
> of two columns (patrolId and incidentId). From the
> documentation I have, it looks like you can only have one
> field that is a primary key.
> Can you help?
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE test_table (
patrolId INT NOT NULL ,
incidentId INT NOT NULL ,
Data VARCHAR(10) NOT NULL ,
CONSTRAINT pk_test_table
PRIMARY KEY ( patrolId , incidentId )
)
GO
DROP TABLE test_table
CREATE TABLE synopsis is available at
http://msdn.microsoft.com/library/de...eate2_8g9x.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Create SoreProcedure that search in al Table and fields

i am Create a StoreProcedure That Search in All Tables And Filds by Keyword

and return one result????

I have no idea why you'd want to do this, but sure, you could, I guess. You need to get SQL Server books online, and research enumerating tables and columns using the system tables.

Create row from number range

Greetings:

I would like to create a row for each number range in a table and add its associated fields with it.

Source Table has:

BEG_NUM END_NUM PRINTER USER DATE
150 153 P01 Ed 6-1-07

I would like to convert that to a table which consists of:
Number Printer User Date
150 P01 Ed 6-1-07
151 P01 Ed 6-1-07
152 P01 Ed 6-1-07
153 P01 Ed 6-1-07

How can I create this using SQL? The source table has lots of rows.

Thanks,
Ericselect min(Number) as BEG_NUM
, max(Number) as END_NUM
, Printer as PRINTER
, User as USER
, Date as DATE
from Source
group
by Printer
, User
, Date|||r937,

Thank you for your reply.

Your solution combines the rows. I want to make a row for each number between the beginning and ending number, so if beginning is 150 and ending is 153 i would have 4 rows listed from that one row. 150,151,152,153.

My goal is to have one row for each series of numbers.|||aaargh, i misunderstood, i got your tables backwards, didn't i

you will need an integers table:create table integers (i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)then you can generate your range of numbers like this:select BEG_NUM + i as Number
, PRINTER as Printer
, USER as User
, DATE as Date
from integers
inner
join Source
on BEG_NUM + i <= END_NUM|||When I do the 2nd part, I get invalid column name for all except i because I am selecting fields from integer table that don't exist.

I tried adding the correct table names and selecting from both tables and then joining but it doesn't work. It says tables have the same exposed names.

When I tried another way, it gave me 10 rows for each number, which wasnt what i want either :(|||i tested my query and it works

probably your table is actually different from what you posted, or you made an error in your query

can't help you any further unless you show your query|||I apologize, Its working now.

Thank you for all your help!!|||The "filling holes in a table" problem is a well-known "difficult" one in relational databases.
If your database system supports it, you should use recursive SQL for the "integers table" part of the story:WITH integers(i) AS (VALUES (0)
UNION ALL
SELECT i+1 FROM integers
WHERE i < 9)
SELECT ... <Rudy's query>
Now this query is much more flexible in case there are bigger holes than width 10: just replace the "9" by, say, "99".
Or better yet, replace it by (SELECT MAX(End_Num-Beg_Num) FROM Source) or something like that, so that the query becomes completely unparametrized and will work on any table with any numeric column pair.
(Or with a date pair, for that matter.)|||nice trick, peter, and i mean that as a sincere compliment

it is a true "hack" and that is a good thing

:)

Create query based on a field that wont be the same value in both tables

I have two tables: TestA and TestB. Both tables have 3 fields: ID,
Name, and RunDate. I need to create a query which will join the two
tables first on Name but then I need to match up the RunDates even
though the RunDates won't be the same.

CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)
CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)

INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')
INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')
INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')

Here's a common scenario:
User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Then
the user updates TestB data for Account 1, 15 minutes later. I want
these two records to match. The user must always update TestA data
before they update TestB data. Therefore, there might be more rows in
TestA then in TestB

Here's what the results should look like for the above data.

Name TestA Date TestB Date
-- ---- ----
Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
Account 1 9/3/2004 3:00pm (NULL)
Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Any help would be much appreciated!!!!On 29 Sep 2004 07:41:18 -0700, Jim G wrote:

>Here's what the results should look like for the above data.
>Name TestA Date TestB Date
>-- ---- ----
>Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
>Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
>Account 1 9/3/2004 3:00pm (NULL)
>Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Hi Jim,

Thanks for posting DDL ans INSERTS for sample data!

The following query gives the above results:

SELECT a.Name, a.RunDate, b.RunDate
FROM TestA AS a
LEFT JOIN TestB AS b
ON b.Name = a.Name
AND b.RunDate >= a.RunDate
AND NOT EXISTS (SELECT *
FROM TestA AS a2
WHERE a2.Name = a.Name
AND a2.RunDate > a.RunDate
AND a2.RunDate < b.RunDate)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Awesome! That worked perfectly. Thanks!

Create new table with fields from 4 other tables

I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.

Table Fields

Tbl_Date_Dimension --> [Date_Dimension_Year], Date_Dimension_Period], [Date_Dimension_Fiscal_Week]

Tbl_Report_Level --> Report_Level_Id

Tbl_Customer --> Customer_Code

[Sales Fact] --> [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id

The new table is called Tbl_Sales_Growth

Here's the information on the new table:
[Date_Dimension_Year] [int] NOT NULL,

[Date_Dimension_Period] [int] NOT NULL,

[Date_Dimension_Fiscal_Week] [int] NULL,

[Report_Level_Id] [int] NOT NULL,

[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,

[Quantity] [decimal](18, 0) NOT NULL,

[Gross turnover] [decimal](18, 0) NOT NULL

) ON [PRIMARY]

Quote:

Originally Posted by tenchyz

I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.

Table Fields

Tbl_Date_Dimension --> [Date_Dimension_Year], Date_Dimension_Period], [Date_Dimension_Fiscal_Week]

Tbl_Report_Level --> Report_Level_Id

Tbl_Customer --> Customer_Code

[Sales Fact] --> [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id

The new table is called Tbl_Sales_Growth

Here's the information on the new table:
[Date_Dimension_Year] [int] NOT NULL,

[Date_Dimension_Period] [int] NOT NULL,

[Date_Dimension_Fiscal_Week] [int] NULL,

[Report_Level_Id] [int] NOT NULL,

[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,

[Quantity] [decimal](18, 0) NOT NULL,

[Gross turnover] [decimal](18, 0) NOT NULL

) ON [PRIMARY]

i'm no expert but i guess all you have to do is create a procedure for this|||

Quote:

Originally Posted by tenchyz

I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.

Table Fields

Tbl_Date_Dimension --> [Date_Dimension_Year], Date_Dimension_Period], [Date_Dimension_Fiscal_Week]

Tbl_Report_Level --> Report_Level_Id

Tbl_Customer --> Customer_Code

[Sales Fact] --> [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id

The new table is called Tbl_Sales_Growth

Here's the information on the new table:
[Date_Dimension_Year] [int] NOT NULL,

[Date_Dimension_Period] [int] NOT NULL,

[Date_Dimension_Fiscal_Week] [int] NULL,

[Report_Level_Id] [int] NOT NULL,

[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,

[Quantity] [decimal](18, 0) NOT NULL,

[Gross turnover] [decimal](18, 0) NOT NULL

) ON [PRIMARY]

how are these tables related? do you need a physical table or maybe you just need a view

Create dynamic reports

Hi,

I receive an xml object that represent just about any kind of a table containing unknown amount of text fields, and need to create a Table report out of it, the header fields are not pre-determined.

Is this possible with Crystal reports and in particular in the .Net free addition of Crystal reports ?

if so - HOW ? please explain as articulately as possible

Thanks
. .
|
~

NimCo.You can try using CDO (Crystal Data Objects) to pass info to Crystal Reports. Basically, you populate an array and pass the array to the report.

Look here for an example of CDO in VB 6, maybe you can adapt it to your needs...

http://www.dev-archive.com/forum/showthread.php?s=&threadid=281658&highlight=cdo