In SQL Server, how to generate table script as a single statement? - Database Discussion Boards

DECLARE @TargetDate SMALLDATETIME
DECLARE @InitialDate SMALLDATETIME
SET @InitialDate = '06-19-2012'
SET @TargetDate = '06-25-2012'
WHILE @InitialDate <= @TargetDate
BEGIN
PRINT @InitialDate
SELECT @InitialDate = DATEADD( D, 1, @InitialDate )
END

- Happy Coding -
Vishal Vashishta

i need to make a duplicate copy of an existing database with a new name in sql server and this has to be done using QUERY ONLY,

i tried to take a backup and restore but that restores with the same name...

Look here: MSDN: How to: Restore a Database to a New Location and Name (Transact-SQL)[^]

Sandeep Mewara
[My last tip/trick]: Server side Delimiters in ASP.NET[^]

thank you that worked for an extent, but still finding a bit of problem when trying to restore the database from sql query using c# in asp .net

when i try to restore it says the mdf file location is in correct but when i try to run the same query in sql it works perfectly, any idea on what iam doing wrong....

also how do i know the logical name of the db, since i will be restoring it in the runtime i cant afford it to come randomly, when i tested sometimes it comes as master and mastlog and sometimes it comes as the db name i have backed up...

nvarunkumar wrote:
QUERY ONLY

Why? That is a rather inefficient approach...

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

because i have backup and restore at runtime in asp .net, so if i get the query i can put it in sqlcommand in asp...

I am attempting to put a database into the Data-Tier application model, and the wizards (being MS Wizards) are painful and lack error reporting.

So, I'm trying to export my database as scripts, and import the scripts into the DAC model.

Here's the problem: over the years, our tables have undergone modifications. What I don't get is why SQL Server seems to have to regenerate a table from script in sequence (here's a sample)

SQL

CREATE TABLE [dbo].[SampleUserMaster](
    [SampleUserMasterKey] [dbo].[udt_keyfield] IDENTITY(1,1) NOT NULL,
    [UserKey] [dbo].[udt_keyfield] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NULL,
    [SampleKey] [dbo].[udt_keyfield] NOT NULL,
    [UserMasterKey] [dbo].[udt_keyfield] NULL,
    [AddressRefusedUnknownFlag] [dbo].[udt_logical] NULL,
    [SampleCompletedFlag] [dbo].[udt_logical] NULL,
    [SampleCompletedDate] [datetime] NULL,
    [IncomingSampleActionDate] [datetime] NULL,
    [IncomingSampleActionCodesKey] [dbo].[udt_keyfield] NULL,
    [IncomingSampleActionCode] [dbo].[udt_sharedcode] NULL,
    [IncomingSampleActionDescription] [dbo].[udt_sharedcodedescription] NULL,
    [LockSession] [uniqueidentifier] NULL,
    [LockTime] [datetime] NULL,
    [LockVersion] [timestamp] NOT NULL,
    [WWClearanceCodeKey] [dbo].[udt_keyfield] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceCode] [dbo].[udt_sharedcode] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceDescription] [dbo].[udt_sharedcodedescription] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceCodeDateTime] [datetime] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceCodeAuthorityInvolvedKey] [dbo].[udt_keyfield] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [IsElligibleWorkRelease] [dbo].[udt_logical] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [DepartmentKey] [dbo].[udt_keyfield] NOT NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [CreatedDateTime] [datetime] NOT NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [ModifiedDateTime] [datetime] NOT NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [VisitorListLastChangedDateTime] [datetime] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [IsSealed] [bit] NULL
 CONSTRAINT [PK_SampleUserMasterkey] PRIMARY KEY CLUSTERED
(
    [SampleUserMasterKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Note the alter table statements for each column added over time - why is this???!?!?!

I have 20,000+ tables, so obviously I can't manually edit these to move the added columns into the definition itself - is there a way to make it generate a table as a table without all the alter table commands for added columns? Any ideas?

This fails Data-Tier Application importing; it will not run any of the alter table statements, thus these columns never make the model, thus the stored procs and views fail whereas they reference missing columns.

Is there a way to generate a CREATE TABLE statement with ALL Columns in is? I don't get the way it does it now... Thanks in advance.

I've exported the DB, and restored it and it still keeps the full history of alters to add columns, and generates them as individual statements....

Think of how stupid the average person is, and realize half of them are stupider than that. - George Carlin

Try using the SqlPubWiz to generate the scripts.

P.S. Oh, and there's an article on here that generates scripts.

Thanks for the tip; trying it now. (SQL Pub Wiz)

Think of how stupid the average person is, and realize half of them are stupider than that. - George Carlin

I'm curious, never having used UDTs, what requires you to use so many and what is in the types.

Do you think the UDTs affect performance, normally I would not even ask but you seem to have a hoarde of them!

Never underestimate the power of human stupidity
RAH

hello to all .. i m beginner to mysql and php and i want to do the LEFT JOIN of three tables with data given below
CREATE TABLE IF NOT EXISTS `history` (
`history_id` int(5) NOT NULL AUTO_INCREMENT,
`upload_id` int(5) NOT NULL,
`user_id` int(5) NOT NULL,
`datetime` date NOT NULL,
`location` varchar(200) NOT NULL,
PRIMARY KEY (`history_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `history`
--

INSERT INTO `history` (`history_id`, `upload_id`, `user_id`, `datetime`, `location`) VALUES
(1, 1, 5, '2012-01-03', 'home'),
(2, 2, 5, '2012-06-20', 'Office'),
(3, 3, 5, '2012-03-06', 'office'),
(4, 4, 5, '2012-06-05', 'Home');

-- --------------------------------------------------------

--
-- Table structure for table `uploads`
--

CREATE TABLE IF NOT EXISTS `uploads` (
`upload_id` int(5) NOT NULL AUTO_INCREMENT,
`user_id` int(5) NOT NULL,
`file_path` varchar(200) NOT NULL,
`picture_path` varchar(200) NOT NULL,
PRIMARY KEY (`upload_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `uploads`
--

INSERT INTO `uploads` (`upload_id`, `user_id`, `file_path`, `picture_path`) VALUES
(1, 5, 'hope', 'star'),
(2, 5, 'sky', 'mountain'),
(3, 5, 'smoking', 'habbits'),
(4, 5, 'sleeping', 'walking');

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
`user_id` int(5) NOT NULL AUTO_INCREMENT,
`name` varchar(75) NOT NULL,
`address` varchar(200) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`user_id`, `name`, `address`) VALUES
(5, 'Asif', 'Bannu'),
(6, 'Safeer', 'Bannu'),
(7, 'Farhad', 'Peshawar'),
(8, 'Rashed', 'Peshawar'),
(9, 'Yasir', 'Quetta'),
(10, 'Muhib', 'Islamabad');

i m executing the query

SQL

SELECT DISTINCT US.name, US.address, UP.file_path, UP.picture_path, HS.location
FROM user US
LEFT JOIN uploads UP ON US.user_id = UP.user_id
LEFT JOIN history HS ON US.user_id =  HS.user_id,
uploads UD LEFT JOIN history HSI ON UD.upload_id = HSI.upload_id
WHERE US.user_id = 5
ORDER BY US.user_id

but i m getting multiple rows with same data.
plz help me out in this situation. thanks a lot

U can create view from the tables and then create a join query using that view.

I dont know exactly how ur answer should be, so correct me if i am wrong!!!!

karthi

so what type of joining i can use?

How your output should be...? Based on your output only we can write views which can use left, inner or right join....
If u can post how your answer looks like based on your datas i might try from my side

karthi

Perhaps they shouldn't be all "left" joins[^]?

Bastard Programmer from Hell Suspicious | :suss:

SQL

SELECT DISTINCTROW US.name, US.address, UP.file_path, UP.picture_path, HS.location
FROM user US
LEFT JOIN uploads UP ON US.user_id = UP.user_id
LEFT JOIN history HS ON US.user_id =  HS.user_id,
uploads UD LEFT JOIN history HSI ON UD.upload_id = HSI.upload_id
WHERE US.user_id = 5
ORDER BY US.user_id

Hint: Replace DISTINCT to DISTINCTROW

Not sure why you have the second join to the history table. If each upload has a unique user and a unique history row, then user_id in the history table is useless information for this query. The second history join is giving you the redundant rows.

Try and avoid using DISTINCT and its variations. It requires the server to do more work and therefore slow down your query.

SQL

SELECT 
    US.name, 
    US.address, 
    UP.file_path, 
    UP.picture_path, 
    HS.location
FROM 
    [user] US
LEFT JOIN 
   uploads UP 
   ON (US.user_id = UP.user_id)
LEFT JOIN 
   history HS 
   ON (US.upload_id =  HS.upload_id)
WHERE 
   US.user_id = 5
ORDER BY 
   US.user_id

modified 15-Jun-12 16:51pm.

I have a small programming problem, am requesting Your insight I don't know whether this should be in the C# forum or in the Database Forum, but since it looks more likely a Database thing, I'll ask it Here.
I have an application that is written in C# and has a Backed Database in SQL Server 2005. In Summary the application manages info on a company's distributors and their sales.
In the table, Distributors I have a Primary Key Column DistributorIDNO (char (8)) which stores their IDs in the formart SL000001 (up to a logical maximum of SL999999).
Different branches of the company are allocated number ranges to register new distributors, for example, the range SL002000 to SL002500 may be given to the office in Masaka (a town in My country), and at the Head Office they would continue to register others From SL002501 onwards).
Now, the people in Masaka may not fill up their quarter, which means between SL002000 and SL003000, some numbers are taken up, while others are not.
I would like to write a function that should look through the database and determine which numbers, between a particular range, have not been registered.

Peter

.NET Developer, loved programming from the day I looked at Basic code for the DOS Game "Snake". Learning doesn't stop.

This is NOT a good design for your primary key, a PK should be stupid and not convey any information.

Create a table variable and fill it with all possible numbers between the range you want to test, then select from that table where the PK is not in your Distributor table.

SQL

DECLARE
	@Top INT,
	@bottom INT
	
SET @bottom = 2000
SET @Top = 6000

DECLARE
	@Count INT,
	@Str VARCHAR(20)
DECLARE 	@Tbl TABLE(Code VARCHAR(20))	
	
	
SET @Count = 1
WHILE @Count < @Top
BEGIN
	SET @Str = 'SL' + RIGHT('000000000'+CAST(@Count + @bottom AS VARCHAR(10)),8)
	INSERT @Tbl(Code) VALUES (@Str)
	
	SET @Count = @Count + 1

END

SELECT Code
FROM @Tbl		
WHERE Code NOT IN (SELECT PD FROM TableBame)

Never underestimate the power of human stupidity
RAH

Does the term "SLOO" change? If not, then your key is built from multiple facts, and should be split into multiple columns. A simple identity-field would do nicely as a surrogate key, as suggested by MyCroft.

Bastard Programmer from Hell Suspicious | :suss:

My company's reporting software combines data from multiple sources such as point of sale, inventory, accounting, payroll, etc.. Where possible, we provide our customers with a direct connection interface against the datasource, usually SQL Server. All we require is read only access to the vendor database. A long time customer recently switched to a new inventory system. Our connection to this system uses a view created in another sql server database (usually the master) residing on the same server/instance. The customer asked the IT department for help in getting the view created, and setting up a readonly sql server login to pull the data. This started a lot of questioning, and the next thing I know, the customer forwards me a response from the inventory vendor advising against using this view against their database. Why? Without quoting, they stated that due to the possibility of upgrades or structure changes, there was a possibility of corruption to the source tables. It is my understanding that by design, there is zero chance of a view causing corruption to the source tables. At worst, it would fail or produce incorrect results. I know I don't know what I don't know, which is why I am asking here. (and Google isn't coming up with any usable results) Am I wrong? To me this just seems like a scare tactic from the other vendor to charge for custom work.

"Go forth into the source" - Neal Morse

Having worked for a software vendor in the past, I'm leaning toward scare tactic.

The vendor doesn't want to deal with potential performance problems / data inconsistency, etc, so they are covering their A**.

One way to handle this is ask the vendor what is their "supported" integration method?

You can always build the view against the vendor's recommendation, but look at your support contract and it might put a monkey wrench in the works.

The only time I would even think that a view could cause you trouble is something called a Materialised view, and that's an Oracle thing - not a SQL Server one. Sounds like a scare tactic to me.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

A view in SQL Server can corrupt the underlying table, as changes to the view will change the table. However, your explanation seems to take care of that (read only user, etc.).

I have not worked with views for a while so I do not remember all the what will happens. Good luck.